Systems Engineering and RDBMS

Trigger Basics (Oracle)

Posted by decipherinfosys on March 18, 2008

Triggers are one of the widely used database objects. In Oracle, we can define triggers on tables, views, schema or a database. On tables, we can define DML (insert, update ,delete) triggers, on schema we can define DDL triggers and logon/logoff event triggers, and system event triggers(startup/ shutdown) on the database. In general based on where a trigger is defined it can be for any DML events, DDL events or login events. Common use of triggers are to generate running numbers before inserting new rows, for security restrictions, for populating some other columns based on the new or old column values and for auditing purpose to name few.

Following are the different trigger types for DML related triggers:

1) BEFORE and AFTER triggers: Indicates whether trigger should be fired before or after the INSERT, UPDATE or DELETE operation occurs on the table. It can be used for DDL statements as well but in that case trigger should be defined either on the schema or the database.

2) ROW and STATEMENT triggers: Defines whether trigger should be fired for each row affected or just once for triggering statement:
a) BEFORE ROW: For each row before triggering statement is executed.
b) AFTER ROW: For each row after triggering statement is executed.
c) BEFORE STATEMENT: Once, before triggering statement is executed.
d) AFTER STATEMENT: Once, after triggering statement is executed.

3) INSTEAD OF triggers: These triggers are normally defined on complex view. It allows to update the views which are not directly updatable through DML statements. It updates the underlying table used to create view.

In 11g, Oracle introduced COMPOUND triggers in which, more than one type of trigger can be defined in a single trigger. In compound trigger, we can define specific action for each type mentioned above but all these actions are composed in a single trigger. We will cover more about compound triggers in future blog post.

We can define more than one trigger of different type on the same table. Stretching it further we can even have multiple triggers of the same type. e.g. two before insert trigger on the same table.

When two or more triggers are defined on the table, they are fired in following order.

• All before statement triggers
• All before row triggers
• All after row triggers
• All after statement triggers.

If multiple triggers are of the same type, then they follow above order but there is no guarantee which one will be executed first unless it is created with ‘FOLLOWS’ clause. This clause is introduced in 11g. To know more about this clause, please visit our previous blog post.

By default when triggers are created, they are enabled. One can disable it using ‘ALTER TRIGGER’ command. But in Oracle 11g, we can create trigger in ‘DISABLE’ state. We have covered it in one of our blog post.

It is advisable to avoid creating triggers for defining referential integrity constraints. If triggers are not defined correctly, it may result into mutating table errors. We will cover more about it in our future blog post.

2 Responses to “Trigger Basics (Oracle)”

  1. […] by decipherinfosys on March 24, 2008 In one of our previous blog post, we had covered the basic concepts of triggers in Oracle. We had briefly touched upon the topic of […]

  2. […] our previous blog posts, we have covered trigger basics here. . We also gave the script to disable and enable the triggers and constraints in Oracle.   There […]

Sorry, the comment form is closed at this time.

%d bloggers like this: