it江湖漂,怎能不挨刀;一朝机器当,看你怎么着!
分类: LINUX
2010-06-07 17:18:22
建表:
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);
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
:new 与: old:必须是针对行级触发器的,也就是说要使用这两个变量的触发器一定有for each row
这两个变量是系统自动提供的数组变量,:new用来记录新插入的值,old用来记录被删除的值;
使用insert的时候只有:new里有值;
使用delete的时候只有:old里有值;
使用update的时候:new和:old里都有值