Triggers can be associated with Table, View, Schema or Database. The trigger can be PLSQL block or PL/SQL, C or Java procedure.
They fire when a specific event (sql statement) occurs in the database. You specify the event when you create the trigger.
The body of a trigger can contain DML, SELECT statements. DDL and transaction control statements are not allowed in a trigger. ROLLBACK, COMMIT, and SAVEPOINT cannot be used.For system triggers, {CREATE/ALTER/DROP} TABLE statements and ALTER...COMPILE are allowed.
Triggers can be Enabled or Disabled. They can become invalid if the table or column used doesnt exist or renamed.
ALTER TRIGGER trigger_name ENABLE;
ALTER TABLE emp ENABLE ALL TRIGGERS;
Views to check triggers - USER_TRIGGERS, DBA_TRIGGERS, ALL_TRIGGERS
Different Types of Triggers
3 types of triggers are DML, System an Instead of Triggers.
DML Triggers -
Statement Trigger - Fires for each DML statements( INSERT, UPDATE or DELETE)
Row Trigger - Fires for each row affected by DML INSERT, UPDATE or DELETE
For UPDATE trigger you can specify the column list. only if the any of the column in the list changes, the trigger is fired.
you cannot specify the column list of INSERT or DELETE trigger.
If you have 1 trigger which fires for both INSERT and UPDATE, you can detect the DML by using
IF INSERTING THEN ... END IF; --you can also use UPDATING or DELETING
IF UPDATING(ename) THEN ... END IF; --checks if the column ename is updated or not
System Triggers
Database Trigger -fires for each event for all users
Schema Trigger -fires for each event for a specific user
System events - STARTUP, SHUTDOWN, SERVERERROR(specify errno)
Client events - ALTER, DROP, AUDIT, CREATE, DDL, GRANT/REVOKE, RENAME, LOGON/LOGOFF, TRUNCATE
Instead of Triggers -
Some views cannot be updated becasue they might be accessing different tables. YOu can create INSTEAD OF triggers to update such type of views.
Instead of running the DML statement oracle fires the trigger. They can be only FOR EACH Row. You cannot specify BEFORE or AFTER
Views which have SET operator, DISTINCT, aggregate, analytic functions, subquery, etc.
When accessing nested tables, you can use parent qualifier to reference parent row corresponding to the nested table element.
BEFORE and AFTER -
When you create a trigger you have to specify either BEFORE or AFTER.
BEFORE trigger fires before the row data is writteen to disk.
AFTER triggers fire after the row is written to disk, so you can use ROWID. AFTER row trigger needs to read the data for the trigger and then again for the triggering event, but BEFORE trigger reads only once.
Oracle executes all trigggers of same type before executing triggre of a differnt type.
But within the same type, if you have multiple triggers oracle executes the triggers in arbitrary order.
:old and :new
INSERT - will have :new column values, :old values are null;
DELETE - will have :old column values, :new values are null
UPDATE - will have both :old column values and :new column values
:new column value can be assigned in BEFORE trigger, but not in AFTER row trigger.
The column value changed by the BEFORE trigger is visible in the AFTER row trigger.
YOu can specify your own qualifier instead of :old and :new by using REFERENCING option.
Autonomous Triggers :-
You have to specify PRAGMA AUTONOMOUS_TRANSACTION to make the trigger Autonomous.
Autonous Triggers can commit or rollback irrespective of the firing event(weather or not you commit changes to the main table)
Autonomous Triggers can execute DDL statements using native dynamci SQL
CREATE OR REPLACE TRIGGER emp_audit_trg AFTER INSERT ON emp_audit
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE temp_audit';
Insert into emp_audit_tbl values('1', sysdate);
COMMIT;
END;
/
Triggers and SQLLOADER -
Conventional Load fires INSERT triggers.
For Direct Load - the triggers are disabled before the load.
If the table does not exist, then teh data is loaded before triggers are defined.
If IGNORE=Y and the table is present, then any existing trigger fires.
Calling a Java Procedure from a trigger :-
CREATE OR REPLACE PROCEDURE tst_java_proc(nam varchar2) IS LANGUAGE JAVA
name 'tsttrigger.afterupdate(oracle.sql.CHAR)';
CREATE OR REPLACE PROCEDURE tst_update AFTER UPDATE ON tst
FOR EACH ROW
CALLA tst_java_proc(:new.name);
Errors and Exceptions :-
If there are errors in the trigger then the actions of trigger body and trigger even are rolled back, unless it is handled in exception handler.
But for STARTUP, SHUTDOWN or LOGGON triggers only the trigger action is rolled back.
DDL operations may not be allowed on DDL events
Mutating Table -
ORA-04091: table SCOTT.Emp is mutating, trigger/function may not see it
A mutating table is a table that is being modified by an UPDATE/DELETE/INSERT statement, or by the effects of a DELETE CASCADE constraint.
The session that issued the triggering statement cannot query or modify a mutating table.
This restriction is only FOR EACH ROW clause. Views being modified in INSTEAD OF triggers are not considered mutating.
Statement Triggers will work fine.
you could bypass these restrictions by using a temporary table, a PL/SQL table, or a package variable. For example, in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you might use two triggers—an AFTER row trigger that updates a temporary table, and an AFTER statement trigger that updates the original table with the values from the temporary table.
Restrictions on Mutating Tables Relaxed
This allows most foreign key constraint actions to be implemented through their obvious after-row trigger, providing the constraint is not self-referential. Update cascade, update set null, update set default, delete set default, inserting a missing parent, and maintaining a count of children can all be implemented easily. For example, this is an implementation of update cascade:
create table p (p1 number constraint ppk primary key);
create table f (f1 number constraint ffk references p);
create trigger pt after update on p for each row begin
update f set f1 = :new.p1 where f1 = :old.p1;
end;
/
This implementation requires care for multirow updates. For example, if a table p has three rows with the values (1), (2), (3), and table f also has three rows with the values (1), (2), (3), then the following statement updates p correctly but causes problems when the trigger updates f:
update p set p1 = p1+1;
The statement first updates (1) to (2) in p, and the trigger updates (1) to (2) in f, leaving two rows of value (2) in f. Then the statement updates (2) to (3) in p, and the trigger updates both rows of value (2) to (3) in f. Finally, the statement updates (3) to (4) in p, and the trigger updates all three rows in f from (3) to (4). The relationship of the data in p and f is lost.
To avoid this problem, you must forbid multirow updates to p that change the primary key and reuse existing primary key values. It could also be solved by tracking which foreign key values have already been updated, then modifying the trigger so that no row is updated twice.
That is the only problem with this technique for foreign key updates. The trigger cannot miss rows that have been changed but not committed by another transaction, because the foreign key constraint guarantees that no matching foreign key rows are locked before the after-row trigger is called.
They fire when a specific event (sql statement) occurs in the database. You specify the event when you create the trigger.
The body of a trigger can contain DML, SELECT statements. DDL and transaction control statements are not allowed in a trigger. ROLLBACK, COMMIT, and SAVEPOINT cannot be used.For system triggers, {CREATE/ALTER/DROP} TABLE statements and ALTER...COMPILE are allowed.
Triggers can be Enabled or Disabled. They can become invalid if the table or column used doesnt exist or renamed.
ALTER TRIGGER trigger_name ENABLE;
ALTER TABLE emp ENABLE ALL TRIGGERS;
Views to check triggers - USER_TRIGGERS, DBA_TRIGGERS, ALL_TRIGGERS
Different Types of Triggers
3 types of triggers are DML, System an Instead of Triggers.
DML Triggers -
Statement Trigger - Fires for each DML statements( INSERT, UPDATE or DELETE)
Row Trigger - Fires for each row affected by DML INSERT, UPDATE or DELETE
For UPDATE trigger you can specify the column list. only if the any of the column in the list changes, the trigger is fired.
you cannot specify the column list of INSERT or DELETE trigger.
If you have 1 trigger which fires for both INSERT and UPDATE, you can detect the DML by using
IF INSERTING THEN ... END IF; --you can also use UPDATING or DELETING
IF UPDATING(ename) THEN ... END IF; --checks if the column ename is updated or not
System Triggers
Database Trigger -fires for each event for all users
Schema Trigger -fires for each event for a specific user
System events - STARTUP, SHUTDOWN, SERVERERROR(specify errno)
Client events - ALTER, DROP, AUDIT, CREATE, DDL, GRANT/REVOKE, RENAME, LOGON/LOGOFF, TRUNCATE
Instead of Triggers -
Some views cannot be updated becasue they might be accessing different tables. YOu can create INSTEAD OF triggers to update such type of views.
Instead of running the DML statement oracle fires the trigger. They can be only FOR EACH Row. You cannot specify BEFORE or AFTER
Views which have SET operator, DISTINCT, aggregate, analytic functions, subquery, etc.
When accessing nested tables, you can use parent qualifier to reference parent row corresponding to the nested table element.
BEFORE and AFTER -
When you create a trigger you have to specify either BEFORE or AFTER.
BEFORE trigger fires before the row data is writteen to disk.
AFTER triggers fire after the row is written to disk, so you can use ROWID. AFTER row trigger needs to read the data for the trigger and then again for the triggering event, but BEFORE trigger reads only once.
Oracle executes all trigggers of same type before executing triggre of a differnt type.
But within the same type, if you have multiple triggers oracle executes the triggers in arbitrary order.
:old and :new
INSERT - will have :new column values, :old values are null;
DELETE - will have :old column values, :new values are null
UPDATE - will have both :old column values and :new column values
:new column value can be assigned in BEFORE trigger, but not in AFTER row trigger.
The column value changed by the BEFORE trigger is visible in the AFTER row trigger.
YOu can specify your own qualifier instead of :old and :new by using REFERENCING option.
Autonomous Triggers :-
You have to specify PRAGMA AUTONOMOUS_TRANSACTION to make the trigger Autonomous.
Autonous Triggers can commit or rollback irrespective of the firing event(weather or not you commit changes to the main table)
Autonomous Triggers can execute DDL statements using native dynamci SQL
CREATE OR REPLACE TRIGGER emp_audit_trg AFTER INSERT ON emp_audit
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE temp_audit';
Insert into emp_audit_tbl values('1', sysdate);
COMMIT;
END;
/
Triggers and SQLLOADER -
Conventional Load fires INSERT triggers.
For Direct Load - the triggers are disabled before the load.
If the table does not exist, then teh data is loaded before triggers are defined.
If IGNORE=Y and the table is present, then any existing trigger fires.
Calling a Java Procedure from a trigger :-
CREATE OR REPLACE PROCEDURE tst_java_proc(nam varchar2) IS LANGUAGE JAVA
name 'tsttrigger.afterupdate(oracle.sql.CHAR)';
CREATE OR REPLACE PROCEDURE tst_update AFTER UPDATE ON tst
FOR EACH ROW
CALLA tst_java_proc(:new.name);
Errors and Exceptions :-
If there are errors in the trigger then the actions of trigger body and trigger even are rolled back, unless it is handled in exception handler.
But for STARTUP, SHUTDOWN or LOGGON triggers only the trigger action is rolled back.
DDL operations may not be allowed on DDL events
Mutating Table -
ORA-04091: table SCOTT.Emp is mutating, trigger/function may not see it
A mutating table is a table that is being modified by an UPDATE/DELETE/INSERT statement, or by the effects of a DELETE CASCADE constraint.
The session that issued the triggering statement cannot query or modify a mutating table.
This restriction is only FOR EACH ROW clause. Views being modified in INSTEAD OF triggers are not considered mutating.
Statement Triggers will work fine.
you could bypass these restrictions by using a temporary table, a PL/SQL table, or a package variable. For example, in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you might use two triggers—an AFTER row trigger that updates a temporary table, and an AFTER statement trigger that updates the original table with the values from the temporary table.
Restrictions on Mutating Tables Relaxed
This allows most foreign key constraint actions to be implemented through their obvious after-row trigger, providing the constraint is not self-referential. Update cascade, update set null, update set default, delete set default, inserting a missing parent, and maintaining a count of children can all be implemented easily. For example, this is an implementation of update cascade:
create table p (p1 number constraint ppk primary key);
create table f (f1 number constraint ffk references p);
create trigger pt after update on p for each row begin
update f set f1 = :new.p1 where f1 = :old.p1;
end;
/
This implementation requires care for multirow updates. For example, if a table p has three rows with the values (1), (2), (3), and table f also has three rows with the values (1), (2), (3), then the following statement updates p correctly but causes problems when the trigger updates f:
update p set p1 = p1+1;
The statement first updates (1) to (2) in p, and the trigger updates (1) to (2) in f, leaving two rows of value (2) in f. Then the statement updates (2) to (3) in p, and the trigger updates both rows of value (2) to (3) in f. Finally, the statement updates (3) to (4) in p, and the trigger updates all three rows in f from (3) to (4). The relationship of the data in p and f is lost.
To avoid this problem, you must forbid multirow updates to p that change the primary key and reuse existing primary key values. It could also be solved by tracking which foreign key values have already been updated, then modifying the trigger so that no row is updated twice.
That is the only problem with this technique for foreign key updates. The trigger cannot miss rows that have been changed but not committed by another transaction, because the foreign key constraint guarantees that no matching foreign key rows are locked before the after-row trigger is called.
Comments