Triggers

Last modified: October 23, 2019

Triggers Overview

A trigger is a named PL/SQL unit that is stored in the database and fired in response to a specified event that occurs in the database.

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)

The trigger is said to be defined on the table, view, schema, or database.

Trigger Types

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.

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.

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

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.

Creating a Trigger

1. In the Database Explorer window, right-click the Triggers folder, and then click New Trigger.
2. Select a database object to assign a trigger on it. You can select either Table, View, Schema, or Database.
3. Enter a trigger body.
4. Go to the Event tab, and set Timing and Event.
5. Save the document.

Editing a Trigger

Edit a trigger by selecting Edit Trigger on the Database Explorer node shortcut menu. In the opened document, you can alter the query text. Save the document to apply changes you’ve made to the query text.

Dropping a Trigger

Drop a Trigger by selecting Delete on the Database Explorer node shortcut menu.

Enabling and Disabling Triggers

Enable or disable a trigger by selecting Enable or Disable on the Database Explorer node shortcut menu.

To enable or disable all triggers at once, select Enable All or Disable ALL on the Database Explorer node shortcut menu.