Online Documentation for SQL Manager for Oracle

Creating/editing trigger


Use the Trigger tab of Trigger Editor to create/edit a trigger and specify its properties.

 

Note: Trigger can be launched/stopped within the appropriate object context menu item in the DB Explorer.

 

Name

Specify the name of the trigger to be created, or view the name of the trigger being edited. Note that the trigger name must be unique within its schema.

 

Schema

Use the drop-down list to specify the schema in which the trigger will be created.

 

Trigger Editor - Editing trigger definition

 

Object schema

Use the drop-down list to specify the schema containing the table/view for which the trigger is defined.

 

Object name

Use the drop-down list to identify the table/view for which the trigger is created.

 

The FOLLOWS group is available for Oracle 11.

This group allows you to specify the relative firing order of triggers of the same type.

 

Follow schema

Use the drop-down list to specify the schema containing the trigger to be fired before the trigger being created/edited.

 

Follow trigger

Use the drop-down list to select the trigger after which the trigger being created/edited should fire.

 

CheckBox Disabled

Disables the trigger immediately after it is created. A disabled trigger still exists as an object in the database, but does not fire.

 

Trigger on

This group allows you to specify the database object on which the trigger is defined:

RadioButton Table

RadioButton View

 

For each

Specify whether a row trigger or statement trigger is to be defined:

RadioButton Row

Designates the trigger as 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 condition.

RadioButton Statement

Designates the trigger as a statement trigger. Oracle fires a statement trigger only once when the triggering statement is issued if the optional trigger constraint is met.

 

Trigger type

This group allows you to specify when the trigger should fire:

RadioButton Before (indicates that the trigger will fire before executing the triggering event; for row triggers, the trigger is fired before each affected row is changed)

RadioButton After (indicates that the trigger will fire after executing the triggering event, for row triggers, the trigger is fired after each affected row is changed)

RadioButton Instead of (only available for views; indicates that the trigger will fire instead of executing the triggering event)

RadioButton Compound (defines a compound trigger on a DML event; the body of a COMPOUND trigger can have up to four sections, so that you can specify a before statement, before row, after row, or after statement operation in one trigger)

 

On Event

Use this group to specify DML statements that can cause the trigger to fire. Oracle fires the trigger in the existing user transaction.

 

CheckBox Insert

The trigger is fired whenever an INSERT statement adds a row to a table or adds an element to a nested table.

 

CheckBox Delete

The trigger is fired whenever a DELETE statement removes a row from the table or removes an element from a nested table.

 

CheckBox Update

The trigger is fired whenever an UPDATE statement changes a value in one of the columns of the table or nested table.

 

References

Use this group to specify the correlation names. You can use correlation names in the PL/SQL block and WHEN condition of a row trigger to refer specifically to old and new values of the current row.

If your row trigger is associated with a table named OLD or NEW, use the Old as and New as boxes to specify different correlation names to avoid confusion between the table name and the correlation name. If the trigger is defined on a nested table, then OLD and NEW refer to the row of the nested table, and PARENT refers to the current row of the parent table.

 

Fields

This section allows you to select fields for which trigger will fire. Use the navigation buttons to fill Selected area with required fields. Note that this feature is available only with CheckBox Update option checked in the On event section.

 

Body

Use this tab to specify the trigger body which consists of an optional list of local variables and their data types and a block of statements in Oracle procedure and trigger language, bracketed by BEGIN and END. These statements are executed when the trigger fires.

 

Select the trigger code source: it can be a PL/SQL block or a stored procedure call.

 

RadioButton PL/SQL Block

Specify the PL/SQL block or PL/SQL compound trigger block that Oracle executes to fire the trigger.

 

RadioButton Use existing procedure

This option allows you to call a stored procedure rather than specifying the trigger code inline as a PL/SQL block.

 

When

Use this tab to specify the trigger condition, which is a SQL condition that must be satisfied for the database to fire the trigger. This condition must contain correlation names and cannot contain a query.

qiery

For your convenience the code folding, syntax highlight, code completion and a number of other features for efficient SQL editing are implemented. For details see Working with Query data editor and Using the context menu.

 

To compile the object, use the corresponding iconCompile Compile item of the Navigation bar or toolbar.