PL/SQL Triggers

What is PL/SQL Triggers?

A trigger is a named program unit that is stored in the database and fired (executed) in response to a specified event.

The specified event is associated with either a table, a view, a schema, or the database, and it is one of the following:

• A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)
• A database definition (DDL) statement (CREATE, ALTER, or DROP)
• A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN)

Types of PL/SQL Triggers:

• DML trigger: A DML trigger is fired by a DML statement, a DDL trigger is fired by a DDL statement, a DELETE trigger is fired by a DELETE statement, and so on.
• INSTEAD OF trigger: An INSTEAD OF trigger is a DML trigger that is defined on a view (not a table). The database fires the INSTEAD OF trigger instead of executing the triggering DML statement.
• System trigger: A system trigger is defined on a schema or the database. A trigger defined on a schema fires for each event associated with the owner of the schema (the current user). A trigger defined on a database fires for each event associated with all users.
• Simple trigger: A simple trigger can fire at exactly one of the following timing points:
  • Before the triggering statement executes
  • After the triggering statement executes
  • Before each row that the triggering statement affects
  • After each row that the triggering statement affects
• Compound trigger: A compound trigger can fire at more than one timing point. Compound triggers make it easier to program an approach where you want the actions you implement for the various timing points to share common data.

What is Trigger States?

A trigger can be in either of two states:

• Enabled: An enabled trigger executes its trigger body if a triggering statement is entered and the trigger restriction (if any) evaluates to TRUE.
• Disabled: A disabled trigger does not execute its trigger body, even if a triggering statement is entered and the trigger restriction (if any) evaluates to TRUE.

By default, a trigger is created in enabled state. To create a trigger in disabled state, use the DISABLE clause of the CREATE TRIGGER statement.

Next Article: PL/SQL Exception Handling

Privileges Required to Use Triggers:

To create a trigger in your schema:

• You must have the CREATE TRIGGER system privilege
• One of the following must be true:
  • You own the table specified in the triggering statement
  • You have the ALTER privilege for the table specified in the triggering statement
  • You have the ALTER ANY TABLE system privilege

To create a trigger in another schema, or to reference a table in another schema from a trigger in your schema:

• You must have the CREATE ANY TRIGGER system privilege.
• You must have the EXECUTE privilege on the referenced subprograms or packages.

How to Create Triggers?

To create a trigger, use the CREATE TRIGGER statement. By default, a trigger is created in enabled state. To create a trigger in disabled state, use the DISABLE clause of the CREATE TRIGGER statement.

CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
DECLARE
--Declaration-statements
BEGIN
--Executable-statements
EXCEPTION
--Exception-handling-statements
END;

Code Explained:

• CREATE [OR REPLACE] TRIGGER trigger_name : Creates or replace an existing trigger with the trigger_name.
• {BEFORE | AFTER | INSTEAD OF} : This specifies when the trigger would be executed. The INSTEAD OF clause is used for creating trigger on a view.
• {INSERT [OR] | UPDATE [OR] | DELETE}: This specifies the DML operation.
• [OF col_name]: This specifies the column name that would be updated.
• [ON table_name]: This specifies the name of the table associated with the trigger.
• [REFERENCING OLD AS o NEW AS n]: This allows you to refer new and old values for various DML statements, like INSERT, UPDATE, and DELETE.
• [FOR EACH ROW]: This specifies a row level trigger, i.e., the trigger would be executed for each row being affected. Otherwise the trigger will execute just once when the SQL statement is executed, which is called a table level trigger.
• WHEN (condition): This provides a condition for rows for which the trigger would fire. This clause is valid only for row level triggers.

When Does the Trigger Fire?

A trigger fires based on a triggering statement, which specifies:

• The SQL statement, database event, or DDL event that fires the trigger body. The options include DELETE, INSERT, and UPDATE. One, two, or all three of these options can be included in the triggering statement specification.
• The table, view, DATABASE, or SCHEMA on which the trigger is defined.

Next Article: PL/SQL Exception Handling

For any questions, suggestions or feedback, Please write to us. Thanks for Reading :)