MySQL INSERT Trigger with IF Condition Statements

Here is a sample MYSQL Trigger that demonstrates how to changes or intercept the values been passed to the database table and change or manipulate using IF statements.

I have create only with very simple IF statement and only for a numeric, date, and text field. There is no limit to what you can do with a trigger. This trigger also demonstrates that you can have multiple conditions in a  single trigger.

This MySQL SQL script was created for a quick reference and to help someone with very little knowledge of SQL trigger to give a basic understanding of MySQL triggers.

Sample MySQL Trigger:

DELIMITER //
CREATE TRIGGER dwdata.t_iCopyDocIDsAndChangeStatus
BEFORE INSERT ON dwdata.sampleen
FOR EACH ROW
BEGIN
/* Move the value from one column to another */
SET NEW.DOCID = NEW.DWDOCID;
/* Check a text field with IF statement */
IF NEW.DOCTYPE = 'Offer'
THEN
SET NEW.STATUS = 'Collect Money';
END IF;
/* Check the numeric field with IF Statement */
IF NEW.AMOUNT >= 1000
THEN
SET NEW.STATUS = 'Big Dollars';
END IF;
/* Check if the date is equal to today's date */
IF DATE_FORMAT(NEW.DATE, '%m/%d/%Y') = DATE_FORMAT(CURDATE(), '%m/%d/%Y')
THEN
SET NEW.STATUS = 'Collect money today';
END IF;
END;
//

To drop or delete a MySQL Trigger you will run the following command (example this trigger):

DROP TRIGGER dwdata.t_iCopyDocIDsAndChangeStatus

About onlinejt

Blogger

2 Responses to MySQL INSERT Trigger with IF Condition Statements

  1. Juan 01/13/2012 at 11:44 am #

    DELIMITER //
    CREATE TRIGGER dwdata2.tr_uRemoveDollarFirstCharacter
    BEFORE UPDATE ON dwdata2.xp_docs
    FOR EACH ROW
    BEGIN
       /** Update the data been inserted **/
      IF SUBSTRING(NEW.SPARE1, 1, 1) = ‘$’ THEN
         SET NEW.SPARE1 = SUBSTRING(NEW.SPARE1, 2, LENGTH(NEW.SPARE1));
      END IF;

    END;
    //

  2. ilyas 12/12/2014 at 8:54 am #

    Oh, it’s nice code.. 🙂 Thanks!

Leave a Reply

*