測試oracle觸發器,Oracle 觸發器 插入，更新，刪除,數據同步，兩表同步

建表：

create table User_Info (

ID                   INTEGER                         not null,

UserName            VARCHAR(30)                     not null,

PassWord            VARCHAR(20)                     not null,

CreateDate          Date                            not null,

Status              INTEGER                         not null,

constraint PK_User_Info primary key (ID)

);

create table User_Info_temp (

ID                   INTEGER                         not null,

UserName            VARCHAR(30)                     not null,

PassWord            VARCHAR(20)                     not null,

CreateDate          Date                            not null,

Status              INTEGER                         not null,

constraint PK_User_Info_temp primary key (ID)

);

觸發器寫法：

create or replace trigger UserToTemp after insert or update or delete

on user_info for each row

declare

integrity_error exception;

errno            integer;

errmsg           char(200);

dummy            integer;

found            boolean;

begin

if inserting then

insert into User_info_temp(ID,UserName,PassWord,CreateDate,Status) values(:NEW.ID,:NEW.UserName,:NEW.PassWord,:new.CreateDate,:NEW.Status);

elsif updating then

update User_info_temp set ID=:NEW.ID,UserName=:NEW.UserName,PassWord=:NEW.PassWord,Status=:NEW.Status where id=:OLD.id;

elsif deleting then

delete from User_info_temp where id=:OLD.id;

end if;

exception

when integrity_error then

raise_application_error(errno, errmsg);

end;

測試數據：

insert into user_info(ID,UserName,PassWord,CreateDate,Status)values(1,'xier','222',to_date('2008-10-11','yyyy-mm-dd'),1)

update user_info u set u.status=3,u.username='xier' where u.id=1

delete from user_info u where u.id=1