PL/SQL Triggers

A trigger is a PL/SQL block which will run automatically whenever an event occurs. PL/SQL block may be associated with a table, a view or to a database.

A trigger is a procedure that is triggered automatically when on the table that is created is executed a command like insert, update, or delete.
Triggers can contain PL/SQL code and SQL code that are executed as a block.
The trigger can be invoked either before or after the execution of the order insert, update, or delete.

Triggers can be:

– Trigger for the application: this type of trigger is run when an event occurs in an application.
-Trigger for database (DML): this type of trigger is run whenever an event occurs in a database.

CREATE OR REPLACE TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF} 
{INSERT | UPDATE | DELETE} 
 [OF column_name] 
ON table_name 
 [REFERENCING OLD AS old NEW AS new] 
[FOR EACH ROW] 
WHEN (condition)  
BEGIN 
   --- pl/sql statements  
END; 

Triggers at the database level may be of two kinds:
– at the level of instruction (statement level trigger)
– line level (row level trigger)

-- before statement trigger
CREATE OR REPLACE TRIGGER before_ trigger
BEFORE INSERT ON frame
BEGIN
	DBMS_OUTPUT.PUT_LINE('Before Insert Statement Level');
END;

--  before row  trigger
CREATE OR REPLACE TRIGGER before_row_trigger
BEFORE INSERT ON frame
FOR EACH ROW
BEGIN
	DBMS_OUTPUT.PUT_LINE('Before Insert Row Level');
END;