PL/SQL triggers

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.
PL/SQL sequences
PL/SQL sequences - Define PL/SQL sequences. syntax for a sequence, What does cache and nocache options mean while creating a sequence?, How do we set the LASTVALUE value in an Oracle Sequence?...
Oracle/PLSQL indexes
Oracle/PLSQL indexes - What is an Index? How to create an Index, What is Function-Based Index? Create a Function-Based Index, How do we drop an Index?...
Advantages of PL/SQL
PL/SQL is a transaction processing language that offers the following advantages: support for SQL - SQL is flexible, powerful and easy to learn...
Post your comment
Discussion Board
Good one
Good content, quite helpful
Diya 01-16-2016

You may like