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;//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; 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.pwd,sysdate,’′,sysdate,pdeptid,pldflag); insert into NNCMSLZ.jo_user(user_id, username,email,password,REGISTER_TIME,REGISTER_IP,LAST_LOGIN_TIME) values(pmaxjouid,,’’, :new.pwd,sysdate,’′,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; update NNCMSLZ.jo_user set password=:new.pwd where; else update NNCMSLZ.JC_USER set DEPART_ID=pdeptid,IS_LEAD=pldflag where; end if; end if; if deleting then dbms_output.put_line(‘deleting’); delete NNCMSLZ.JC_USER where; delete NNCMSLZ.jo_user where; end if; end;


Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s