Oracle触发器之表新增/修改的触发操作

Oracle创建触发器，若是本地触发，加数据库名即可；如果是远程服务器，做一个db_link操作即可。

教学代码

建表：
 
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

示例代码

--新增用户或修改用户时触发授予默认权限操作
create or replace trigger tr_gspuser_insert after insert or update or delete
on GSPUSER for each row
declare
    integrity_error exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;
    
begin
if inserting then
    insert into GSPUSERASS(ID,USERID,POSITIONID) VALUES (:NEW.ID,:NEW.ID,'094e4de8-3d46-4b30-b2ea-6cd25ab589f7');
elsif updating then 
    update GSPUSERASS set ID=:NEW.ID  where id=:OLD.ID;
elsif deleting then
    delete from GSPUSERASS where ID=:OLD.id;
end if;
exception
    when integrity_error then
       raise_application_error(errno, errmsg);
end;