mysql 第13章触发器

2015-10-24

DDL

CREATE TABLE employee (
name char(64) not null,
email char(64),
password char(64),
PRIMARY key (name)
);

CREATE TABLE log (
id int auto_increment,
email char(64),
status char(10),
message text,
ts timestamp,
primary key (id)
);

CREATE TRIGGER tr_employee_insert_after AFTER INSERT ON employee
FOR EACH ROW INSERT INTO log(email,status,message)
VALUES(NEW.email,'OK',CONCAT('Adding employee ',NEW.name));

CREATE TRIGGER tr_employee_delete_after AFTER DELETE ON employee
FOR EACH ROW INSERT INTO log(email,status,message)
VALUES(OLD.email,'OK',CONCAT('Removing employee '));

delimiter //
CREATE TRIGGER tr_employee_update_after AFTER UPDATE ON employee
FOR EACH ROW 
BEGIN
IF OLD.name != NEW.name THEN
INSERT INTO log(email,status,message)
VALUES(OLD.email,'OK',CONCAT('Name change from ',OLD.name,'to',NEW.name));
END IF;
IF OLD.password != NEW.password THEN
INSERT INTO log(email,status,message)
VALUES(OLD.email,'OK','Password change ');
END IF;
IF OLD.email != NEW.email THEN
INSERT INTO log(email,status,message)
VALUES(OLD.email,'OK',CONCAT('E-mail change to ',NEW.email));
END IF;
END 
//
delimiter ;

DML

SET @pass = PASSWORD('xyzzy');

SELECT SLEEP(2);

INSERT INTO employee VALUES('mats','mats@example.com',@pass);

SELECT SLEEP(2);

UPDATE employee SET name = 'matz' WHERE email = 'mats@example.com';

SELECT SLEEP(2);

SET @pass = PASSWORD('foobar');

SELECT SLEEP(2);

UPDATE employee SET `password` = @pass WHERE email = 'mats@example.com';

SELECT SLEEP(2);

DELETE FROM employee WHERE email = 'mats@example.com';

SELECT SLEEP(2);

SELECT * FROM log;