an oracle trigger example

a trigger example of oracle
create or replace TRIGGER SYNC_USER after insert or update or delete on SYS_T_PERSON for each row declare pmaxjouid int;pmaxjcuid int;pld varchar2(50);pdeptid NUMBER(10,0);pldflag NUMBER(1,0);pcount NUMBER; begin select count(*) into pcount from NNCMSLZ.JC_DEPARTMENT c,sys_t_org n where c.DEPART_NAME=n.ORGNAME and n.id=:new.orgid;//use count to avoid if pcount>0 then select c.DEPART_ID,n.ORGTYPE_D into pdeptid, pld from NNCMSLZ.JC_DEPARTMENT c,sys_t_org n where c.DEPART_NAME=n.ORGNAME and n.id=:new.orgid; else pdeptid:=null; pld:=”; end if; if pld=’2D-LD’ then pldflag:=1; else pldflag:=0; end if; if INSERTING then dbms_output.put_line(‘inserting’); select max(user_id)+1 into pmaxjouid from NNCMSLZ.jo_user; select max(user_id)+1 into pmaxjcuid from NNCMSLZ.JC_USER; insert into NNCMSLZ.JC_USER(user_id, GROUP_ID,username,email,password,REGISTER_TIME,REGISTER_IP,LAST_LOGIN_TIME,DEPART_ID,IS_LEAD) values(pmaxjcuid,1, :new.name,’a@a.com’, :new.pwd,sysdate,’127.0.0.1′,sysdate,pdeptid,pldflag); insert into NNCMSLZ.jo_user(user_id, username,email,password,REGISTER_TIME,REGISTER_IP,LAST_LOGIN_TIME) values(pmaxjouid, :new.name,’a@a.com’, :new.pwd,sysdate,’127.0.0.1′,sysdate); end if; if updating then dbms_output.put_line(‘updating’); if :new.pwd is not null then update NNCMSLZ.JC_USER set password=:new.pwd,DEPART_ID=pdeptid,IS_LEAD=pldflag where username=:new.name; update NNCMSLZ.jo_user set password=:new.pwd where username=:new.name; else update NNCMSLZ.JC_USER set DEPART_ID=pdeptid,IS_LEAD=pldflag where username=:new.name; end if; end if; if deleting then dbms_output.put_line(‘deleting’); delete NNCMSLZ.JC_USER where username=:old.name; delete NNCMSLZ.jo_user where username=:old.name; end if; end;

This entry was posted in Computer, Database. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s