A trigger is SQL code which runs just before or just after an INSERT, UPDATE or DELETE event occurs on a particular database table.

Creating a Trigger:

Consider we have two tables:

CREATE TABLE `blog` (
    `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
    `title` text,
    `content` text,
    `deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    KEY `ix_deleted` (`deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='Blog posts';


CREATE TABLE `audit` (
    `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
    `blog_id` mediumint(8) unsigned NOT NULL,
    `changetype` enum('NEW','EDIT','DELETE') NOT NULL,
    CONSTRAINT `FK_audit_blog_id` FOREIGN KEY (`blog_id`) REFERENCES `blog` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

We need to do is:

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 action type i.e a simple text- "Edit".

Each trigger requires:

1) A unique name. I prefer to use a name which describes the table and action, e.g. blog_before_insert or blog_after_update.

2) The table which triggers the event. A single trigger can only monitor a single table.

3) 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.

4) 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.

Syntax:

CREATE
    TRIGGER `event_name` BEFORE/AFTER INSERT/UPDATE/DELETE
    ON `database`.`table`
    FOR EACH ROW BEGIN
        -- trigger body
    END;

Implementation:

DELIMITER $$

CREATE
    TRIGGER `blog_after_update` AFTER UPDATE 
    ON `blog` 
    FOR EACH ROW BEGIN

        INSERT INTO audit (blog_id, changetype) VALUES (NEW.id, 'Edit');

    END$$