MySQL Trigger instance

Test Table 1
?

1 2 3 4 5 6 7 8

DROP TABLE IF EXISTS test; CREATE TABLE Test (id bigint (one) unsigned not NULL auto_increment, name varchar (m) NOT null DEF Ault ', type varchar (MB), Create_time datetime, PRIMARY KEY (ID)) Engine=innodb auto_increment=1 ULT Charset=utf8;

Test Table 2

?

1 2 3 4 5 6 7 8 9

DROP TABLE IF EXISTS test_hisy; CREATE TABLE Test_hisy (id bigint (one) unsigned not NULL auto_increment, name varchar (MB) Not NUL L DEFAULT ', type varchar (MB), Create_time datetime, Operation varchar (m) COMMENT ' operation type ', PRIMARY KEY (ID)) Engine=innodb auto_increment=1 DEFAULT Charset=utf8;

Insert Trigger

Table test new record, insert a record of type value of "1" into the Test_hisy table (after insert: Trigger after entry, before insert: Trigger before entry)

?

1 2 3 4 5 6 7 8 9 10 11

DELIMITER//DROP TRIGGER IF EXISTS t_after_insert_test//CREATE TRIGGER t_after_insert_test after insert in test for each ROW BEGIN IF new.type= ' 1 ' THEN insert into Test_hisy (name, type, create_time, operation) VALUES (New.name, new     . Type, new.create_time, ' Insert '); End IF; end;//

Update triggers

When table test modifies, when the type value is "2", the modified record is inserted into the Test_hisy table (after update: triggered after modification, before update: triggered before modification)

?

1 2 3 4 5 6 7 8 9 10 11

DELIMITER//DROP TRIGGER IF EXISTS t_before_update_test//CREATE TRIGGER t_before_update_test before update on test for E ACH ROW BEGIN IF new.type= ' 2 ' THEN insert into Test_hisy (name, type, create_time, operation) VALUES (Old.name,     Old.type, Old.create_time, ' Update '); End IF; end;//

Delete Trigger

Table test deletes records into table test_hisy before deleting records (after Delete: Triggers after deletion, before delete: triggers before deleting)

?

1 2 3 4 5 6 7 8 9

DELIMITER//DROP TRIGGER IF EXISTS t_before_delete_test//CREATE TRIGGER t_before_delete_test before delete on test for E ACH ROW BEGIN INSERT INTO Test_hisy (name, type, create_time, operation) VALUES (Old.name, Old.type, Old.create_time , ' delete '); end;//

Note: The above trigger example appears in the new for the modified data, the old is the modified data