Once you have the privilege and access to create triggers, next is to know the 3 important terms: trigger_time, trigger_event, and lastly the trigger_body.
trigger_time is the trigger action time. It can be BEFORE or AFTER to indicate that the trigger activates before or after of each trigger event.
trigger_event indicates the kind of statement that activates the trigger. The trigger event can be one of the following:
- INSERT - The trigger is activated whenever a new row is inserted into the table through INSERT, LOAD DATA, and REPLACE statements.
- UPDATE - The trigger is activated whenever a row is modified through UPDATE statements.
- DELETE - The trigger is activated whenever a row is deleted from the table through DELETE and REPLACE statements. Please take note that DROP TABLE and TRUNCATE TABLE statements do NOT activate this trigger, because it doesn't use DELETE.
trigger_body is the statement to execute when the trigger activates. This is where you put your SQL statements. If you want to execute multiple statements, you should use BEGIN ... END on your SQL syntax, this is likely a normal programming syntax.
Columns are associated with aliases OLD and NEW. The availability of aliases depends on the trigger_event.
- OLD - can only be use on UPDATE and DELETE event. OLD.column_name
- NEW - can only be use on INSERT and UPDATE event. NEW.column_name
Example below will create TRIGGER trg_table1_ins that activates BEFORE the insertion happens in table1. We also use DELIMITER to execute multiple lines of SQL statement via MYSQL console.
Let say you have 4 tables: table1, table2, table3, and table4. Each tables has 2 columns: column1 and column2.
delimiter |
CREATE TRIGGER trg_table1_ins BEFORE INSERT ON table1
FOR EACH ROW BEGIN
INSERT INTO table2 (column1, column2) VALUES (NEW.column1, NEW.column2);
DELETE FROM table3 WHERE column1 = NEW.column1;
UPDATE table4 SET column2 = column2 + 1 WHERE column1 = NEW.column1;
END;
|
delimiter ;
Since we are using BEFORE as trigger_time and INSERT as trigger_event, the statements between the clause BEGIN and END will be executed before the insertion happens to table1. As a results, table1 will have same data as table 2, records on table3 will be deleted if column1 matches with newly inserted data, and table4 updated its column2 if column1 matches with newly inserted data.
Using BEFORE as your trigger_time will limits you to use AUTO_INCREMENT column, this is because AUTO_INCREMENT will ONLY be generated after you inserted the data. Hence, you should use AFTER. Please see below example.
Let say you have 2 tables: table1 and table5. Each tables have 3 columns: id as AUTO_INCREMENT, column1, and column2.
delimiter |
CREATE TRIGGER trg_table1_ins AFTER INSERT ON table1
FOR EACH ROW BEGIN
INSERT INTO table5 (id, column1, column2) VALUES (NEW.id, NEW.column1, NEW.column2);
END;
|
delimiter ;
Example below will trigger the statement BEFORE the UPDATE happens in table1. You will noticed that I used both NEW and OLD aliases to perform the statements.
delimiter |
CREATE TRIGGER trg_table1_upd BEFORE UPDATE ON table1
FOR EACH ROW BEGIN
INSERT INTO table2 (column1, column2) VALUES (NEW.column1, NEW.column2);
DELETE FROM table3 WHERE column1 = OLD.column1;
UPDATE table4 SET column2 = NEW.column2 WHERE column1 = OLD.column1;
END;
|
delimiter ;
Lastly, to have a DELETE event, example below will trigger AFTER the DELETE happens in table1.
delimiter |
CREATE TRIGGER trg_table1_del AFTER DELETE ON table1
FOR EACH ROW BEGIN
DELETE FROM table3 WHERE column1 = OLD.column1;
END;
|
delimiter ;
Tables can be associated with database name which defaults to CURRENT database. Ex. database_name.table_name. You can specify the database name on your trigger_body. This is useful if you wanted to execute your trigger statements on another database. Please see example below.
delimiter |
CREATE TRIGGER trg_table1_ins BEFORE INSERT ON table1
FOR EACH ROW BEGIN
INSERT database2.INTO table2 (column1, column2) VALUES (NEW.column1, NEW.column2);
DELETE FROM database2.table3 WHERE column1 = NEW.column1;
UPDATE database2.table4 SET column2 = column2 + 1 WHERE column1 = NEW.column1;
END;
|
delimiter ;
delimiter |
CREATE TRIGGER trg_table1_ins AFTER INSERT ON table1
FOR EACH ROW BEGIN
INSERT INTO database2.table5 (id, column1, column2) VALUES (NEW.id, NEW.column1, NEW.column2);
END;
|
delimiter ;
Hope you like it!!
Resource: dev.mysql.com
