This is the second article in a series about database automation with triggers and events. A trigger is SQL code which is run just before or just after an INSERT, UPDATE or DELETE event occurs on a particular database table. Triggers have been supported in MySQL since version 5.0.2. ...
...Creating a TriggerWe now require two triggers: When a record is INSERTed into the blog table, we want to add a new entry into the audit table containing the blog ID and a type of ‘NEW’ (or ‘DELETE’ if it was deleted immediately).
When a record is UPDATEd in the blog table, we want to add a new entry into the audit table containing the blog ID and a type of ‘EDIT’ or ‘DELETE’ if the deleted flag is set.Note that the changetime field will automatically be set to the current time. Each trigger requires: A unique name. I prefer to use a name which describes the table and action, e.g. blog_before_insert or blog_after_update.
The table which triggers the event. A single trigger can only monitor a single table.
When the trigger occurs. This can either be BEFORE or AFTER an INSERT, UPDATE or DELETE. A BEFORE trigger must be used if you need to modify incoming data. An AFTER trigger must be used if you want to reference the new/changed record as a foreign key for a record in another table.
The trigger body; a set of SQL commands to run. Note that you can refer to columns in the subject table using OLD.col_name (the previous value) or NEW.col_name (the new value). The value for NEW.col_name can be changed in BEFORE INSERT and UPDATE triggers. The basic trigger syntax is:
CREATE
TRIGGER `event_name` BEFORE/AFTER INSERT/UPDATE/DELETE
ON `database`.`table`
FOR EACH ROW BEGIN
-- trigger body
-- this code is applied to every
-- inserted/updated/deleted row
END;We require two triggers — AFTER INSERT and AFTER UPDATE on the blog table. It’s not necessary to define a DELETE trigger since a post is marked as deleted by setting it’s deleted field to true. The first MySQL command we’ll issue is a little unusual:
DELIMITER $$
Read more: Sitepoint
QR:
...Creating a TriggerWe now require two triggers: When a record is INSERTed into the blog table, we want to add a new entry into the audit table containing the blog ID and a type of ‘NEW’ (or ‘DELETE’ if it was deleted immediately).
When a record is UPDATEd in the blog table, we want to add a new entry into the audit table containing the blog ID and a type of ‘EDIT’ or ‘DELETE’ if the deleted flag is set.Note that the changetime field will automatically be set to the current time. Each trigger requires: A unique name. I prefer to use a name which describes the table and action, e.g. blog_before_insert or blog_after_update.
The table which triggers the event. A single trigger can only monitor a single table.
When the trigger occurs. This can either be BEFORE or AFTER an INSERT, UPDATE or DELETE. A BEFORE trigger must be used if you need to modify incoming data. An AFTER trigger must be used if you want to reference the new/changed record as a foreign key for a record in another table.
The trigger body; a set of SQL commands to run. Note that you can refer to columns in the subject table using OLD.col_name (the previous value) or NEW.col_name (the new value). The value for NEW.col_name can be changed in BEFORE INSERT and UPDATE triggers. The basic trigger syntax is:
CREATE
TRIGGER `event_name` BEFORE/AFTER INSERT/UPDATE/DELETE
ON `database`.`table`
FOR EACH ROW BEGIN
-- trigger body
-- this code is applied to every
-- inserted/updated/deleted row
END;We require two triggers — AFTER INSERT and AFTER UPDATE on the blog table. It’s not necessary to define a DELETE trigger since a post is marked as deleted by setting it’s deleted field to true. The first MySQL command we’ll issue is a little unusual:
DELIMITER $$
Read more: Sitepoint
QR: