Oracle使用触发器实例

分手后的思念是犯贱 2022-08-10 06:10 195阅读 0赞

eg1:

create or replace trigger SYM\_U\_BS\_STUDENT after update on BASE\_STUDENT for each row  
begin  
      if (:new.EVENT\_SOURCE=0) or (:new.EVENT\_SOURCE is null)  then  
           insert into sym\_STUDENT (event\_type,sym\_id,id,school\_id,class\_id,student\_name,old\_name,student\_code,unitive\_code,identity\_card,sex,birthday,mobile\_phone,is\_leave\_school,enroll\_year,class\_inner\_code,economy\_state,card\_number,is\_singleton,stayin,boorish,is\_local\_source,student\_recruitment,study\_mode,native\_place,homepage,email,link\_phone,link\_address,postalcode,region\_code,creation\_time,modify\_time,is\_deleted,event\_source,is\_freshman,password,now\_state,school\_district,meetexam\_code,source\_place,is\_specialid,dir\_id,file\_path,health,nation,background,polity\_join\_date,home\_address,to\_school\_date,dorm,dorm\_tel,spec\_id,specpoint\_id,enter\_score)  
                   values(decode(:new.is\_deleted,1,'D',0,'U'),sys\_guid(),:new.id,:new.school\_id,:new.class\_id,:new.student\_name,  
       :new.old\_name,:new.student\_code,:new.unitive\_code,:new.identity\_card,:new.sex,:new.birthday,:new.mobile\_phone,  
       :new.is\_leave\_school,:new.enroll\_year,:new.class\_inner\_code,:new.economy\_state,:new.card\_number,:new.is\_singleton,:new.stayin,:new.boorish,:new.is\_local\_source,:new.student\_recruitment,:new.study\_mode,:new.native\_place,  
       :new.homepage,:new.email,:new.link\_phone,:new.link\_address,:new.postalcode,:new.region\_code,:new.creation\_time,  
       :new.modify\_time,:new.is\_deleted,:new.event\_source,:new.is\_freshman,:new.password,:new.now\_state,  
       :new.school\_district,:new.meetexam\_code,:new.source\_place,:new.is\_specialid,:new.dir\_id,:new.file\_path,  
       :new.health,:new.nation,:new.background,:new.polity\_join\_date,:new.home\_address,:new.to\_school\_date,:new.dorm,:new.dorm\_tel,:new.spec\_id,:new.specpoint\_id,:new.enter\_score);  
       end if;  
end;

eg2:

create or replace trigger tr\_u\_student after update on base\_student  for each row  
begin  
 if(:new.student\_name <> :old.student\_name  or :new.sex <> :old.sex or :old.sex is null  
 or :new.class\_id <> :old.class\_id or :old.class\_id is null or :new.mobile\_phone <> :old.mobile\_phone  
  or :old.mobile\_phone is null  
 or :new.birthday <> :old.birthday or :old.birthday is null or :new.school\_id <> :old.school\_id ) then  
   update base\_user set real\_name=:new.student\_name,sex=:new.sex,class\_id=:new.class\_id,  
    mobile\_phone=:new.mobile\_phone,birthday=:new.birthday,unit\_id=:new.school\_id,event\_source=0,  
    modify\_time=sysdate  where owner\_id=:new.id;  
 end if;  
end;

eg3: