Oracle 10g Training - Triggers And Triggering Events

Triggers are named PL/SQL blocks that get executed implicitly when a triggering event occurs. Rather that being executed when called (as is the case with procedures and functions), triggers get fired automatically when certain events occur in the system. The action of executing a trigger is called ‘firing’ the trigger. A trigger fires when a triggering event occurs.

Triggering Events:

Triggering Events are events that occur due to the users’ actions (or system events) that cause a trigger to be fired. Triggering events can be insertion, deletion, update etc. When any of these events occurs, it executes the triggers written on that event implicitly.

Types of Triggers:

Although, there may be many types and classifications of triggers, basically, there are three types of triggers:

DML Triggers:

DML triggers are fired by the execution of a DML statement. The DML triggers can be defined on insert, update or delete operations. Whenever a DML operation occurs on a table, the trigger will execute. Also, the triggers can be created in such a way that they get executed either before or after the DML operation occurs.

System Triggers:

System triggers fire when a system event such as a database startup or shutdown happens. System triggers can also be fired on DDL operations such as create table.

Instead-of Triggers:

Instead-of triggers can be defined on operations performed on views only. When you define a instead of trigger on an operation on a view, the trigger code will be executed instead of the operation that fired it. This type of triggers can only be row level.

Syntax for trigger creation:

The syntax for trigger creation is as below:

CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE/AFTER/INSTEAD OF}triggering_event

[WHEN trigger_condition]

[FOR EACH ROW]

trigger_body;

Below is an example of creating a trigger:

Figure -4 Trigger Creation

Frequently Asked Questions

1. What are the various PL/SQL objects?

2. What is a package?

3. What is a procedure?

4. What is a function?

5. What is a trigger?

6. What are the differences between procedures and functions?

7. Where are the procedures and functions stored?

8. What are triggering events?

9. What are the various types of triggers?

10. What are instead-of triggers?