Engineering Full Stack Apps with Java and JavaScript
A trigger is a set of commands invoked automatically when an SQL statement changes data on the associated table. Other stored SQL types in MySQL are events, stored procedures, and stored functions.
A trigger can be defined to occur BEFORE or AFTER the table data is changed. Data can be changed via the INSERT, UPDATE, and DELETE statements:
SQL statements that do not use INSERT, UPDATE, or DELETE but change data will not invoke triggers. For example, both the TRUNCATE and DROP TABLE statements remove rows from a table, but do not use a DELETE statement to do so. Foreign key cascading actions (ON DELETE CASCADE and ON UPDATE CASCADE) do not invoke triggers in mysqld 6.0 and will be resolved when foreign keys are implemented across all storage keys in future releases.
Creating a trigger
The CREATE TRIGGER statement creates a trigger and has the following syntax:
CREATE TRIGGER triggername
[BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON tablename
FOR EACH ROW {statement}
SQL statements within a trigger are put inside the BEGIN . . . END compound statement syntax. When more than one SQL statements are there, use MySQL’s DELIMITER SQL extension to change the client delimiter while defining the trigger. Else, the first semicolon used to end the first SQL statement in the trigger will be interpreted as the end of the trigger definition and will throw error.
EXAMPLE:
delimiter |
CREATE TRIGGER emptrigger
AFTER INSERT ON employee
FOR EACH ROW
BEGIN
insert into emp values(NEW.empName,NEW.empId);
update emp set `first name`="HJK01" where id=6;
END;
|
delimiter ;
To test this insert some data into the employee table.
Finding all triggers
To view all the triggers in a database, use the SHOW TRIGGERS command. SHOW TRIGGERS LIKE ’customer’\G.
You can also query the INFORMATION_SCHEMA.TRIGGERS table as:
Select * from INFORMATION_SCHEMA.TRIGGERS
Or to get all trigger names:
select TRIGGER_NAME from INFORMATION_SCHEMA.TRIGGERS;
The SHOW CREATE TRIGGER command shows the original SQL command used to create the trigger.
Dropping a trigger
To drop a trigger, use the DROP TRIGGER statement. An error is thrown if the trigger does not exist, but error can be prevented by using the IF EXISTS phrase:
DROP TRIGGER staff_update_date;
DROP TRIGGER IF EXISTS staff_update_date;
Trigger storage and backup
Triggers are stored in the data directory (datadir) in files named tblname.TRG and triggername.TRN. The .TRG files map triggers to tables, and the .TRN files contain the trigger definition. There will be one .TRG file for each table that is affected. If we create six different triggers on four tables, there will be only four .TRG files. When a table is renamed, the trigger files are renamed too, thus no triggers are lost during a table rename. Triggers can be backed up using mysqldump and by copying the .TRG and .TRN files.
More Notes on MySQL Triggers