MySQL: Triggers

Simple Example

-- begin

DROP DATABASE IF EXISTS `test`;

CREATE DATABASE `test`;

USE `test`;

-- DROP TABLE IF EXISTS `test`;

CREATE TABLE `test` (
	`val` int
);

CREATE TABLE `test_log` (
	`val` int
);

DROP TRIGGER IF EXISTS `t1`;

DELIMITER //

CREATE TRIGGER `t1`
AFTER INSERT ON `test` FOR EACH ROW
BEGIN
	INSERT INTO `test_log` VALUES (NEW.val);
END //

DELIMITER ;

SET @x = 99;

SELECT * FROM `test`;
SELECT * FROM `test_log`;

INSERT INTO `test` VALUES (@x);

SELECT * FROM `test`;
SELECT * FROM `test_log`;

-- end

Another example

CREATE TABLE `test_updated` (
	`val` int,
	`val1` int
);

DELIMITER //

CREATE TRIGGER `t2`
BEFORE INSERT ON `test` FOR EACH ROW
BEGIN
	IF NEW.val <= 0 THEN
	INSERT INTO `test_updated` VALUES (NEW.val, -1);
	ELSE
	INSERT INTO `test_updated` VALUES (NEW.val, 1);
	END IF;
END //

DELIMITER ;

SELECT * from `test_updated`;

INSERT INTO `test` values(-9);
INSERT INTO `test` values(9);

SELECT * from `test_updated`;

References
Introduction to MySQL Triggers

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: