Triggers are an important part of Oracle. And I know only them to be the objects which are event-driven.
That means they cannot be executed by the plsql code and rather they depend upon some event to occur. That event can be any DML operation a table or a DDL level operation on the database.
Definition - Triggers are procedures which are event-driven.
Timing points(Events which trigger triggers) - BEFORE | AFTER
ROW LEVEL | STATEMENT LEVEL
So if there are multiple triggers defined on a table, from the above, we can define the order of the trigger execution.
Before statement >>> Before each row >>> DML >>> After each row >>> After statement.
FOLLOWS - If there are multiple triggers defined at the same timing point for a table, then the order in which they will be executed, is not defined; and is decided by Oracle server.
But, in Oracle 11g, there a new keyword FOOLOWS created. through this keyword, we can define which trigger is to follow which trigger.
COMPOUND TRIGGER - We define a trigger for a particular event, like before the row of after the statement, but Oracle 11g, brought this new way to define a single trigger which can define code blocks to run at different events. This is compound trigger.
Compound trigger have many benefits over normal triggers. And they have many rules as well.
|||||||||||||||||||||||||||||||||||||||||||||||
BEFORE and AFTER triggers are available for tables; INSTEAD OF triggers is available for views only.
Up to 3 triggering events can be connected and specified using the keyword OR. INSERT OR UPDATE OR DELETE
UPDATE can be optionally followed by OF; specifying the columns whose update will run the trigger.
the special columns NEW and OLD are only available if the trigger is row-level.
CREATE TRIGGER trig_tt
AFTER INSERT
REFERENCING NEW AS newrec
OR UPDATE
ON tab_tt
FOR EACH ROW
WHEN(newrec.a1 <= 100)
BEGIN
INSERT INTO tem VALUES (:newrec.b1, :newrec.c1);
END trig_tt;
As you can see from the above code, the special variable NEW is used without colon(:) in the WHEN clause.
|||||||||||||||||||||||||||||||||||||||||||||||
To check the errors in a trigger, use:
SHOW ERRORS TRIGGER trigger_name
|||||||||||||||||||||||||||||||||||||||||||||||
list all user created triggers -
SELECT trigger_name FROM USER_TRIGGERS;
|||||||||||||||||||||||||||||||||||||||||||||||
MUTATING TABLE ERRORS
when the trigger queries or tries to modify a table which is mutating already; Oracle will report a mutating trigger error.
To Aviod mutating table errors:
> A row-level trigger must not query or modify a mutating table. NEW and OLD values are still accessible.
> A statement-level trigger must not query or modify a mutating table of the trigger is fired as the result of a CASCADE DELETE.
|||||||||||||||||||||||||||||||||||||||||||||||
FUN FACTS
Oracle puts a size limit of 32 kb to triggers. This size is big enough to write over 4000 characters. But if you still need more code, then the best idea is to create named procedures and call them in the code of triggers.
Oracle fires all triggers of a type together and then triggers of another type.
We can't control which type will be fired when.
|||||||||||||||||||||||||||||||||||||||||||||||
BEFORE and AFTER triggers are available for tables; INSTEAD OF triggers is available for views only.
Up to 3 triggering events can be connected and specified using the keyword OR. INSERT OR UPDATE OR DELETE
UPDATE can be optionally followed by OF; specifying the columns whose update will run the trigger.
the special columns NEW and OLD are only available if the trigger is row-level.
CREATE TRIGGER trig_tt
AFTER INSERT
REFERENCING NEW AS newrec
OR UPDATE
ON tab_tt
FOR EACH ROW
WHEN(newrec.a1 <= 100)
BEGIN
INSERT INTO tem VALUES (:newrec.b1, :newrec.c1);
END trig_tt;
As you can see from the above code, the special variable NEW is used without colon(:) in the WHEN clause.
|||||||||||||||||||||||||||||||||||||||||||||||
To check the errors in a trigger, use:
SHOW ERRORS TRIGGER trigger_name
|||||||||||||||||||||||||||||||||||||||||||||||
list all user created triggers -
SELECT trigger_name FROM USER_TRIGGERS;
|||||||||||||||||||||||||||||||||||||||||||||||
MUTATING TABLE ERRORS
when the trigger queries or tries to modify a table which is mutating already; Oracle will report a mutating trigger error.
To Aviod mutating table errors:
> A row-level trigger must not query or modify a mutating table. NEW and OLD values are still accessible.
> A statement-level trigger must not query or modify a mutating table of the trigger is fired as the result of a CASCADE DELETE.
|||||||||||||||||||||||||||||||||||||||||||||||
FUN FACTS
Oracle puts a size limit of 32 kb to triggers. This size is big enough to write over 4000 characters. But if you still need more code, then the best idea is to create named procedures and call them in the code of triggers.
Oracle fires all triggers of a type together and then triggers of another type.
We can't control which type will be fired when.
