How to Create Triggers in MySQL

Our Database Plan

• `blog`: stores a unique post ID, the title, content, and a deleted flag.
• `audit`: stores a basic set of historical changes with a record ID, the blog post ID, the change type (NEW, EDIT or DELETE) and the date/time of that change.

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,
	`changetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`),
	KEY `ix_blog_id` (`blog_id`),
	KEY `ix_changetype` (`changetype`),
	KEY `ix_changetime` (`changetime`),
	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;

Creating a Trigger

• 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.

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.

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;

DELIMITER $$

CREATE
	TRIGGER `blog_after_insert` AFTER INSERT 
	ON `blog` 
	FOR EACH ROW BEGIN
	
		IF NEW.deleted THEN
			SET @changetype = 'DELETE';
		ELSE
			SET @changetype = 'NEW';
		END IF;
    
		INSERT INTO audit (blog_id, changetype) VALUES (NEW.id, @changetype);
		
    END$$

DELIMITER ;

DELIMITER $$

CREATE
	TRIGGER `blog_after_update` AFTER UPDATE 
	ON `blog` 
	FOR EACH ROW BEGIN
	
		IF NEW.deleted THEN
			SET @changetype = 'DELETE';
		ELSE
			SET @changetype = 'EDIT';
		END IF;
    
		INSERT INTO audit (blog_id, changetype) VALUES (NEW.id, @changetype);
		
    END$$

DELIMITER ;

Trigger Happy?

INSERT INTO blog (title, content) VALUES ('Article One', 'Initial text.');

UPDATE blog SET content = 'Edited text' WHERE id = 1;

UPDATE blog SET deleted = 1 WHERE id = 1;