Oracle: Triggers

Last updated on February 21st, 2020 at 04:45 pm

Triggers are stored programs that are fired automatically when some events occur. The code to be fired can be defined as per the requirement.

Oracle has also provided the facility to mention the event upon which the trigger needs to be fire and the timing of the execution.

Benefits of Triggers

• Generating some derived column values automatically
• Enforcing referential integrity
• Event logging and storing information on table access
• Auditing
• Synchronous replication of tables
• Imposing security authorizations
• Preventing invalid transactions

Types of Triggers in Oracle

Triggers can be classified based on the following parameters.

• Classification based on the timing
  • BEFORE Trigger: It fires before the specified event has occurred.
  • AFTER Trigger: It fires after the specified event has occurred.
  • INSTEAD OF Trigger: A special type. You will learn more about the further topics. (only for DML )
• Classification based on the level
  • STATEMENT level Trigger: It fires one time for the specified event statement.
  • ROW level Trigger: It fires for each record that got affected in the specified event. (only for DML)
• Classification based on the Event
  • DML Trigger: It fires when the DML event is specified (INSERT/UPDATE/DELETE)
  • DDL Trigger: It fires when the DDL event is specified (CREATE/ALTER)
  • DATABASE Trigger: It fires when the database event is specified (LOGON/LOGOFF/STARTUP/SHUTDOWN)

So each trigger is the combination of above parameters.

How to Create Trigger

Below is the syntax for creating a trigger.

CREATE [ OR REPLACE ] TRIGGER <trigger_name>

[BEFORE | AFTER | INSTEAD OF ]

[INSERT | UPDATE | DELETE……]

ON<name of underlying object>

[FOR EACH ROW]

[WHEN<condition for trigger to get execute> ]

DECLARE

<Declaration part>

BEGIN

<Execution part>

EXCEPTION

<Exception handling part>

END;

Syntax Explanation:

• The above syntax shows the different optional statements that are present in trigger creation.
• BEFORE/ AFTER will specify the event timings.
• INSERT/UPDATE/LOGON/CREATE/etc. will specify the event for which the trigger needs to be fired.
• ON clause will specify on which object the above-mentioned event is valid. For example, this will be the table name on which the DML event may occur in the case of DML Trigger.
• Command “FOR EACH ROW” will specify the ROW level trigger.
• WHEN clause will specify the additional condition in which the trigger needs to fire.
• The declaration part, execution part, exception handling part is same as that of the other PL/SQL blocks. Declaration part and exception handling part are optional.