What is a Trigger? Syntax of Triggers
A database trigger is a PL/SQL object which executed defines the action the database
To be performed on table or view. Like an insert, update or delete to be performed on the table or view.
Syntax:
CREATE OR REPLACE TRIGGER [Trigger Name] [Before / After / Instead Of]
ON [schema].[table]
<pl/sql subprogram>
Types of PL/SQL Triggers
The following are the different types of triggers
- Row trigger - The trigger fires for each ROW affected.
- Statement trigger – The trigger is fired once when the condition is matched
- Before and After trigger – The BEFORE trigger run the trigger action before the insert, update or delete statement. The AFTER trigger runs the trigger action after the insert, update or delete statement is executes.
PL/SQL triggers execution hierarchy.- The statement level triggers are executed and then row level triggers executed.
- First all BEFORE statements that apply. During which Integrity constraint is performed.
- Run all AFTER statements that apply. During which Integrity constraint is performed.
- SQL statement is executed.
How to obtain information about any trigger?
A select statement against the USER_TRIGGERS table will provide the information of the triggers.
What is CYCLIC CASCADING in a TRIGGER?
When one or more trigger enters into an infinite loop the triggers can enter into cyclic cascading.
Let’s say
Trigger A is inserting a row into table employee and update department
Trigger B is inserting a row into table department and insert into table employee
This is a cyclic situation which may lead to the crashing of the database.
