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 deleteon user_info for each rowdeclare    integrity_error exception;    errno            integer;    errmsg           char(200);    dummy            integer;    found            boolean;    beginif 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,'hello','111',to_date('2015-08-11','yyyy-mm-dd'),1);

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

delete from user_info u where u.id=1;

+++++++++++++++++不同数据库建的两个表同步++++++++++++++++++++++++++++++++

在parking库建User_Info表在sfgl库建User_Info_Temp表

----------------------------------------------------------两个数据库的连接dblink:  在parking库新建的dblink_usertest  可以任意改，是个dblink连接名称sfgl/sfgl  数据库的用户名和密码orcl 数据库连接标识符

create public database link dblink_usertest  connect to sfgl identified by sfgl   using 'orcl'; 

----------------------------------------------------------触发器： 新建在parking库上的

create or replace trigger UserToTemp after insert or update or deleteon User_Info for each rowdeclare    integrity_error exception;    errno            integer;    errmsg           char(200);    dummy            integer;    found            boolean;    beginif inserting then    insert into User_Info_Temp@dblink_usertest(ID,UserName,PassWord,CreateDate,Status) values(:NEW.ID,:NEW.UserName,:NEW.PassWord,:new.CreateDate,:NEW.Status);elsif updating then     update User_Info_Temp@dblink_usertest 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@dblink_usertest where id=:OLD.id;end if;exception    when integrity_error then       raise_application_error(errno, errmsg);end; ----------------------------------------------------------select * from User_Info@dblink_usertest2;select * from User_Info_Temp@dblink_usertest;