The Database Trigger editor enables you to create and edit BEFORE/AFTER triggers for tables, and INSTEAD OF triggers for views and object views (if connected to an Oracle database).
Table Owner |
Displays the user ID of the owner of the current database table. Click the arrow to the right of this field to display a list of users. |
Table |
Displays the name of the current database table. click the arrow to the right of this field to display a list of table names of the tables owned by the user shown in the Table Owner field. Click a table in the list to select it. The Table list displays only the names of the database tables you have access to. |
Name |
Displays the name of the current database trigger. Click the arrow to the right of this field to display a list of trigger names for the table shown in the Table field. The Name list displays only the names of the database triggers you have access to. |
Triggering (BEFORE and AFTER) |
(For tables only.) Corresponds to the BEFORE or AFTER option of the SQL command CREATE TRIGGER. Indicates when the trigger body is fired in relation to the triggering statement (for example, UPDATE, INSERT, or DELETE) that is being executed. BEFORE triggers are fired before the triggering statement. AFTER triggers are fired after the trigger statement is executed. BEFORE and AFTER triggers apply to both row and statement triggers. |
Instead of |
(For views only.) Corresponds to the INSTEAD OF option of the SQL command CREATE TRIGGER. Oracle fires the trigger instead of executing the triggering statement. The trigger performs the insert, update, or delete actions directly on the base tables of the view. |
Statement |
Specifies the type of SQL statement (triggering statement) that fires the trigger body. The possible options are UPDATE, INSERT, and DELETE. Check one or more of the check boxes to include the option in the triggering statement. If the triggering statement specifies UPDATE, an optional list of columns can be included in the triggering statement. See the Of Columns option next. If more than one option is checked, you can use the conditional predicates UPDATING, INSERTING, and DELETING to execute specific blocks of code in the trigger body. See Trigger Body later. |
Of Columns |
(For tables only.) Corresponds to the OF clause in the UPDATE triggering statement of the SQL command CREATE TRIGGER. Displays a list of columns for the table selected in the Table field. This list is active only if the triggering statement specifies UPDATE. Click one or more columns in this list box to specify that the trigger fires only when one of the selected columns is updated. If you want the trigger to fire when any column in the table is updated, do not click any column names in the list. Not available with INSTEAD OF triggers. Oracle fires INSTEAD OF triggers whenever an UPDATE changes a value in any column of the view. Oracle database objects: You cannot specify nested table columns or LOB columns here. |
For Each Statement |
Corresponds to the FOR EACH STATEMENT option of the SQL command CREATE TRIGGER. Designates the trigger to be a statement trigger. Oracle fires a statement trigger only once when the triggering statement is issued (regardless of the number of rows affected), if the optional trigger constraint defined in the WHEN clause is satisfied. Not available with INSTEAD OF triggers. |
For Each Row |
Corresponds to the FOR EACH ROW option of the SQL command CREATE TRIGGER. Designates the trigger to be a row trigger. Oracle fires a row trigger once for each row that is affected by the triggering statement and meets the optional trigger constraint defined in the WHEN clause. |
Referencing |
Specifies correlation names. You can use correlation names in the trigger body and WHEN clause of a row trigger to refer specifically to old and new values of the current row. The default correlation names are OLD and NEW. If your row trigger is associated with a table named OLD or NEW, use this option to specify different correlation names to avoid confusion between the table name and the correlation name. For row triggers only. |
OLD As |
Specifies a correlation name to avoid a name conflict between the correlation name and a table that is named OLD. |
NEW As |
Specifies a correlation name to avoid a name conflict between the correlation name and a table that is named NEW. |
When |
(For tables only.) Corresponds to the Boolean SQL expression in the WHEN clause of the SQL command CREATE TRIGGER. For row triggers only. Specifies an optional trigger restriction. If included, the SQL expression is evaluated for each row that the trigger affects. If the expression evaluates to TRUE for a row, the trigger body is fired. If the expression evaluates to FALSE or NOT TRUE for a row (that is, unknown, as with nulls), the trigger body is not fired. Oracle database objects: You can reference object columns or their attributes, VARRAY, nested table, or LOB columns. You cannot invoke PL/SQL functions or methods in the trigger restriction. |
Trigger Body |
The PL/SQL block Oracle executes to fire the trigger. A scrollable, multiline text box where you can type and edit a PL/SQL block. Trigger bodies can include SQL and PL/SQL statements. BEGIN and END keywords are required. The trigger body for row triggers can include correlation names and the REFERENCING option, and the conditional predicates INSERTING, DELETING, and UPDATING. If more than one type of triggering statement is specified for the row trigger, for example, INSERT and UPDATE, you can include the following conditions to execute specific blocks of code in the trigger body:
|
New |
Creates a new, unnamed database trigger. |
Save |
Saves the trigger options and compiles the source appearing in the Trigger Body field. Any error messages generated as a result of the compilation will appear in a separate dialog. |
Revert |
Restores the database trigger to its state at the last save or revert operation. If no save or revert operations have occurred, the database trigger is returned to its original state. |
Drop |
Drops the current database trigger from the database, with confirmation. |
Close |
Attempts to close the Database Trigger editor. If any changes have been made but not applied, an alert appears, prompting you to compile or revert the changes or cancel the operation. Once all changes have been compiled or reverted, the Database Trigger editor is closed. |
About database triggers
Creating a database trigger
Copyright © 1984, 2005, Oracle. All rights reserved.