Oracle 触发器插入更新删除数据同步，两表同步代码,PLSQL代码库,德仔网

代码语言

代码分类

【PLSQL】 Oracle 触发器插入更新删除数据同步，两表同步

作者:Dezai.CN / 发布于2012/9/19/ 1986

Oracle 触发器插入，更新，删除,数据同步，两表同步

<div>建表：</div>
<div>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)
);</div>
<div>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)
);</div>
<div></div>
<div>触发器写法：</div>
<div>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;</div>
<div>
测试数据：
insert into user_info(ID,UserName,PassWord,CreateDate,Status)values(1,'xier','222',to_date('2008-10-11','yyyy-mm-dd'),1)</div>
<div>update user_info u set u.status=3,u.username='xier' where u.id=1</div>
<div>delete from user_info u where u.id=1</div>
<div></div>
<div></div>

试试其它关键字

　表同步　

同语言下

可能有用的

Dezai.CN贡献的其它代码(4037)