[DB][ORACLE]觸發器建立執行個體

[DB][ORACLE]觸發器建立執行個體

//觸發器名UserToTemp 

create or replace trigger UserToTemp after insert or update or delete 

on user_info for each row//對錶user_info操作時觸發以下事件 

declare//聲明變數 

    integrity_error exception; 

    errno            integer; 

    errmsg           char(200); 

    dummy            integer; 

    found            boolean; 

    sexy              varchar2(20); 

 

begin 

if inserting then 

    select sex into sexy from user_info_test;//取出user_info_test表中的sex欄位的值賦值給變數sexy 

    insert into User_info_temp(ID,UserName,PassWord,Createdate,Status) values(:NEW.ID,:NEW.UserName,sexy,:NEW.createdate,:NEW.status);//:NEW.UserName的值為表user_info新增加的資料 

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;