若得山花插满头,莫问奴归处!

  博客园 :: 首页 :: 博问 :: 闪存 :: 新随笔 :: 联系 :: 订阅 订阅 :: 管理 ::
  1375 随笔 :: 0 文章 :: 753 评论 :: 23 引用

公告

建表:

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

posted on 2012-03-13 11:25 莫问奴归处 阅读(...) 评论(...) 编辑 收藏
轩轩娃