Triggers in PL/SQL
March 26, 2022, 2:03 p.m.
Triggers are stored programs, which are automatically executed or fired when some event occur. Events like:
1) A database manipulation (DELETE,INSERT,UPDATE)
2) A database definition (DROP, CREATE, ALTER)
3) A database operation (LOGON, LOGOFF, STARTUP, SHUTDOWN)
Triggers can be defined on the table, view schema or database with which the event is associated.
Triggers are stored programs, which are automatically executed or fired when some event occur. Events like:
1) A database manipulation (DELETE,INSERT,UPDATE)
2) A database definition (DROP, CREATE, ALTER)
3) A database operation (LOGON, LOGOFF, STARTUP, SHUTDOWN)
Triggers can be defined on the table, view schema or database with which the event is associated.
Benefits:
a) Generating some derived column values automatically.
b) Enforcing referential integrity.
c) Preventing invalid transactions.
Types:
1) Row level Triggers
2) Statement level Triggers
3) Before Trigger
4) After Trigger
5) Combination Triggers:
| a) Before Insert row |
| b) Before Insert statement |
| c) After Insert row |
| d) After Insert statement |
| e) Before Update row |
| f) Before Update statement |
| g) After Update row |
| h) After Update statement |
| i) Before Delete row |
| j) Before Delete statement |
| k) After Delete row |
| l) After Delete statement |
When a row-level trigger fires, the PL/SQL runtime system creates and populates the two pseudorecords OLD and NEW. They are called pseudorecords because they have some, but not all, of the properties of records.
For the row that the trigger is processing:
OLD and NEW Pseudo record
| For Insert | OLD contains no values |
| NEW contains new values | |
| For Update | OLD contains old values |
| NEW contains new values | |
| For Delete | OLD contains old values |
| NEW contains no values |
For example:
INSERT INTO AUDIT_LOG (user_id, date, b_cust,a_cust) values (v_user, sysdate, :OLD.cust_id, :NEW.cust_id);
Disabling and Enabling Triggers
Disable a trigger
Alter trigger <trigger_name> DISABLE;
Enable a trigger
Alter trigger <trigger_name> ENABLE;
Disable all triggers present on the table
Alter table <table_name> DISABLE ALL TRIGGERS;
Enable all triggers present on the table
Alter table <table_name> ENABLE ALL TRIGGERS;
Drop
Drop trigger <trigger_name>;
Rename
Alter trigger <old_trigger_name> Rename to <new_trigger_name>;
Tags
Comments
Comments
Explained Well