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;