Trigger-demo--新增 更新 和删除的一个 例子

--create table persons
drop table if EXISTS `persons`;


CREATE TABLE `persons`(
P_id int(5) primary key,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);


--create table person_record to record the persons's changed
drop table if exists `person_record`;


CREATE table person_record(
R_id int(5) AUTO_INCREMENT PRIMARY KEY,
P_id int(5),
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255),
Operate_type varchar(10)
);


--create trigger insert, update, delete for persons
DROP TRIGGER IF EXISTS `persons_insert_trigger`;


DROP TRIGGER IF EXISTS `persons_update_trigger`;


DROP TRIGGER IF EXISTS `persons_delete_trigger`;


CREATE TRIGGER persons_insert_trigger AFTER INSERT ON persons for each row 
       begin       
                   insert into person_record(P_id,LastName,FirstName,Address,City,Operate_type)
                   values (new.P_id,new.LastName,new.FirstName,new.Address,new.City,'CREATE');        
       end;       
create trigger person_update_trigger after update on persons for each row 
       begin        
                    insert into person_record(P_id,LastName,FirstName,Address,City,Operate_type)
                    values (old.P_id,new.LastName,new.FirstName,new.Address,new.City,'UPDATE');                       
       end;       


create trigger person_delete_trigger after delete on persons for each row 
       begin        
                    insert into person_record(P_id,LastName,FirstName,Address,City,Operate_type)
                    values (old.P_id,old.LastName,old.FirstName,old.Address,old.City,'DELETE');                       
       end;