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
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;
//
Oh, it’s nice code.. 🙂 Thanks!