Triggers are stored program , which are automatically executed or fired when some events occur.
Triggers are infact executed in response of the following events:
- DML operation - delete, insert and update
- DDL operation - create, alter or drop
- Other db operations - servererror, logon, logoff, shutdown
Triggers could be defined on table, view, schema or db with which the event is associated.
Benefits of Triggers:
- Generating some derived column values automatically
- Enforcing referencial integrity
- Event logging and storing info on table access
- Auditing
- Imposing security authorization
- Preventing invalid transactions
Trigger types:
- Row level Triggers
- Statement level triggers
- Before triggers
- After triggers
Row level Triggers:
Row level triggers execute once for each row in a transaction. For example, if an update statement updates multiple rows of a table, a row level trigger is fired once for each row affected by the update statement.
Example, an update statement updates 3 rows in a table, then 3 triggers will be fired.
Statement level Triggers:
Statement level triggers are triggered only once for each transaction. For example, when an update commands update 2 rows, the commands contained in the trigger are executed only once.
Before Triggers:
Before trigger execute the trigger action before the triggering event(insert, update or delete).
- Before triggers are used to drive specific column values before completing a triggering insert, update operation.
- Before triggers are used when the trigger action should determine whether or not the triggering statement should be allowed to complete.
After Triggers:
After trigger executes the trigger action after the triggering event(insert, update or delete).
Example,
To perform cascade delete operation, it means that user delete the record from one table but the corresponding records in other tables are deleted automatically by a trigger which fired after the execution of delete statement issued by the user.
Example 1 - statment level trigger:
When we will update the customet table, before the update, the following trigger will be fired and put an audit entry into the audit table.
CREATE TRIGGER customer_before_update
BEFORE UPDATE
ON CUSTOMER
DECLARE
V_USERNAME VARCHAR2(100);
BEGIN
SELECT USER INTO V_USERNAME FROM DUAL;
INSERT INTO AUDIT_TABLE(TABLE_NAME,USER_ID,OPERATION_DATE,OPERATION)
VALUES('CUSTOMER',V_USERNAME,SYSDATE,'BEFORE UPDATE OPERATION');
END;
Example 2 - staement level with more events
CREATE TRIGGER customer_after_action
AFTER INSERT OR DELETE OR UPDATE
ON CUSTOMER
DECLARE
V_USERNAME VARCHAR2(100);
BEGIN
SELECT USER INTO V_USERNAME FROM DUAL;
IF INSERTING THEN
INSERT INTO AUDIT_TABLE(TABLE_NAME,USER_ID,OPERATION_DATE,OPERATION)
VALUES('CUSTOMER',V_USERNAME,SYSDATE,'INSERT OPERATION');
ELSIF DELETING THEN
INSERT INTO AUDIT_TABLE(TABLE_NAME,USER_ID,OPERATION_DATE,OPERATION)
VALUES('CUSTOMER',V_USERNAME,SYSDATE,'DELETE OPERATION');
ELSIF UPDATING THEN
INSERT INTO AUDIT_TABLE(TABLE_NAME,USER_ID,OPERATION_DATE,OPERATION)
VALUES('CUSTOMER',V_USERNAME,SYSDATE,'UPDATE OPERATION');
END IF;
END;
Example row level trigger:
CREATE TRIGGER customer_after_update
AFTER UPDATE
ON CUSTOMER
FOR EACH ROW
DECLARE
V_USERNAME VARCHAR2(100);
BEGIN
SELECT USER INTO V_USERNAME FROM DUAL;
INSERT INTO AUDIT_TABLE(TABLE_NAME,USER_ID,OPERATION_DATE,OPERATION)
VALUES('CUSTOMER',V_USERNAME,SYSDATE,'INSERT OPERATION ROW LEVEL');
END;
OLD and NEW pseudo records with an example:
When a row level trigger fires, the PL/SQL runtime system creates and populates the two pseudo records OLD and NEW.
- For an Insert trigger, OLD : none, NEW: nee value
- For an Update trigger, OLD: old value, NEW: New value
- For a Delete trigger, OLD: old value, NEW: none
CREATE TRIGGER customer_after_update
AFTER UPDATE
ON CUSTOMER
FOR EACH ROW
DECLARE
V_USERNAME VARCHAR2(100);
BEGIN
SELECT USER INTO V_USERNAME FROM DUAL;
INSERT INTO AUDIT_LOG(USERID,OPERATION_DATE,B_CUSTOMER_ID,A_CUSTOMER_ID,B_FIRST_NAME,A_FIRST_NAME)
VALUES(V_USERNAME,SYSDATE,:OLD.CUSTOMER_ID,:NEW.CUSTOMER_ID,:OLD.FIRST_NAME,:NEW.FIRST_NAME);
END;
Row level Triggers Example with WHEN clause:
CREATE TRIGGER customer_after_update
AFTER UPDATE
ON CUSTOMER
FOR EACH ROW
WHEN (OLD.REGION = 'SOUTH')
DECLARE
V_USERNAME VARCHAR2(100);
BEGIN
SELECT USER INTO V_USERNAME FROM DUAL;
INSERT INTO AUDIT_LOG(USERID,OPERATION_DATE,B_CUSTOMER_ID,A_CUSTOMER_ID,B_FIRST_NAME,A_FIRST_NAME)
VALUES(V_USERNAME,SYSDATE,:OLD.CUSTOMER_ID,:NEW.CUSTOMER_ID,:OLD.FIRST_NAME,:NEW.FIRST_NAME);
END;
Row level triggers with OF clause
CREATE TRIGGER customer_after_update
AFTER UPDATE
OF CUSTOMER_ID
ON CUSTOMER
FOR EACH ROW
WHEN (OLD.REGION = 'SOUTH')
DECLARE
V_USERNAME VARCHAR2(100);
BEGIN
SELECT USER INTO V_USERNAME FROM DUAL;
INSERT INTO AUDIT_LOG(USERID,OPERATION_DATE,B_CUSTOMER_ID,A_CUSTOMER_ID,B_FIRST_NAME,A_FIRST_NAME)
VALUES(V_USERNAME,SYSDATE,:OLD.CUSTOMER_ID,:NEW.CUSTOMER_ID,:OLD.FIRST_NAME,:NEW.FIRST_NAME);
END;
Disable and enable triggers:
Disable/Enable:
ALTER TRIGGER <TRIGGERNAME> DISABLE;
ALTER TRIGGER <TRIGGERNAME> ENABLE;
Disable / enable all the triggers present in the table:
ALTER TABLE <TABLE NAME> DISABLE ALL TRIGGERS;
ALTER TABLE <TABLE NAME> ENABLE ALL TRIGGERS;
Drop a trigger:
DROP TRIGGER <TRIGGER NAME>;
Rename a trigger:
ALTER TRIGGER <OLD TRIGGER NAME> RENAME TO <NEW TRIGGER NAME>
