create trigger sql
How to CREATE TRIGGER statement allows you to create a new trigger that is fired automatically whenever an event such as INSERT, DELETE, or UPDATE occurs against a table.
Example trigger for oracle peoplesoft
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
create or replace TRIGGER TEST_TTL_TR AFTER INSERT OR UPDATE OR DELETE ON PS_TEST_TTL FOR EACH ROW DECLARE V_AUDIT_OPRID VARCHAR2(64); BEGIN DBMS_APPLICATION_INFO.READ_CLIENT_INFO(V_AUDIT_OPRID); IF INSERTING THEN INSERT INTO PS_TEST_AUDIT_TBL VALUES (GET_PS_OPRID_NEW(V_AUDIT_OPRID),SYSDATE,'A',:NEW.TTL_DISCIP_NO,:NEW.EMPLID,:NEW.DESCR100,:NEW.DESCR100_1,:NEW.TTL_FLAG,:NEW.FINAL_RESOLUTION,:NEW.KAMSANG_NO,:NEW.TTL_DISCIP_FINAL,:NEW.KAMSANG_NO2,:NEW.TTL_DISCIP_FINAL2,:NEW.NO3,:NEW.TTL_DEEP_FINAL3,:NEW.NO4,:NEW.TTL_DEEP_RE_TYPE); ELSE IF DELETING THEN INSERT INTO PS_TEST_AUDIT_TBL VALUES (GET_PS_OPRID_NEW(V_AUDIT_OPRID),SYSDATE,'D',:OLD.TTL_DEEP_NO,:OLD.EMPLID,:OLD.DESCR100,:OLD.DESCR100_1,:OLD.TTL_FLAG,:OLD.FINAL_RESOLUTION,:OLD.NO,:OLD.TTL_DEEP_FINAL,:OLD.NO2,:OLD.TTL_DEEP_FINAL2,:OLD.NO3,:OLD.TTL_DEEP_FINAL3,:OLD.NO4,:OLD.TTL_DEEP_RE_TYPE); ELSE INSERT INTO PS_TEST_AUDIT_TBL VALUES (GET_PS_OPRID_NEW(V_AUDIT_OPRID),SYSDATE,'K',:OLD.TTL_DEEP_NO,:OLD.EMPLID,:OLD.DESCR100,:OLD.DESCR100_1,:OLD.TTL_FLAG,:OLD.FINAL_RESOLUTION,:OLD.NO,:OLD.TTL_DEEP_FINAL,:OLD.NO2,:OLD.TTL_DEEP_FINAL2,:OLD.NO3,:OLD.TTL_DEEP_FINAL3,:OLD.NO4,:OLD.TTL_DEEP_RE_TYPE); INSERT INTO PS_TEST_AUDIT_TBL VALUES (GET_PS_OPRID_NEW(V_AUDIT_OPRID),SYSDATE,'N',:NEW.TTL_DEEP_NO,:NEW.EMPLID,:NEW.DESCR100,:NEW.DESCR100_1,:NEW.TTL_FLAG,:NEW.FINAL_RESOLUTION,:NEW.NO,:NEW.TTL_DEEP_FINAL,:NEW.NO2,:NEW.TTL_DEEP_FINAL2,:NEW.NO3,:NEW.TTL_DEEP_FINAL3,:NEW.NO4,:NEW.TTL_DEEP_RE_TYPE); END IF; END IF; END TEST_TTL_TR; |