Trigger

Definition: – A set of PL/SQL statements stored permanently in database and automatically activated when ever an event raising statement (DML) is executed.

• They are stored in USER_TRIGGERS system table
• They are used to impose business rules or user defined restrictions on table columns.
• They are also activated when tables are manipulated by other application software tools.
• They provide high security.
• It will execute implicitly whenever the triggering event happens and trigger does not accept arguments.
• The act of executing a trigger is known as firing
• The trigger event can be a DML (INSERT, UPDATE, or DELETE) operation on database table or certain kinds of views; or system event, such as database startup or shutdown, and certain kinds of DDL operations.
• INSTEAD OF is used to create a trigger on a view. before and after cannot be used to create a trigger on a view

Trigger Parts: – There are four parts in trigger.

1. Triggering event
2. Trigger types
3. Trigger restriction
4. Trigger body

1. Triggering event:- Indicates when to activate the trigger

BEFORE –INSERT/UPDATE/DELETE

AFTER –INSERT/UPDATE/DELETE

2. Trigger types:- Two types of Triggers are there
   1. Row level trigger- Activates the trigger for every row manipulated by DML statement
   2. Statement level trigger- Activates the trigger only one’s for one DML statement(default

type).

3. Trigger Restriction: – Used to stop the activation of trigger based on condition. If condition is TRUE trigger is active.

4.     Trigger body: – A set of PL/SQL statements.

Syntax:

create [or replace] TRIGGER <trigger name>

BEFORE / AFTER

INSERT / UPDATE / DELETE

[ OF < column>] On < table name>

[ REFERENCING : OLD AS o : New As n]

[ FOR EACH ROW WHEN < condition>]

DECLARE

< declaration Section>

BEGIN

<execution Section>

Exception

  <exception Section>

END;