--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;
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;