Online Documentation for SQL Manager for Oracle

Creating/editing DB/schema trigger


Use the Trigger tab of DB/Schema Trigger Editor to create/edit a database or schema trigger and specify its properties.

 

Name

Specify the name of the database or schema 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.

 

DB and Schema Trigger Editor - Editing trigger definition

 

CheckBox Disabled

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

 

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)

RadioButton After (indicates that the trigger will fire after executing the triggering event)

 

Trigger on

This group allows you to specify whether a database or schema trigger is defined:

RadioButton Database (defines the trigger on the entire database; the trigger fires whenever any database user initiates the triggering event)

RadioButton Schema (defines the trigger on the current schema; the trigger fires whenever any user connected as the specified schema initiates the triggering event; use the drop-down list to select the schema)

 

On Event

Specify whether the type of event for the trigger:

RadioButton DDL

Indicates that the trigger will fire whenever any of specified DDL statements is issued.

Specify one or more types of DDL statements that can cause the trigger to fire. Oracle fires the trigger in the existing user transaction.

RadioButton Database

Indicates that the trigger will fire when a specified database event occurs.

Specify one or more particular states of the database that can cause the trigger to fire. For each of the triggering events, Oracle opens an autonomous transaction scope, fires the trigger, and commits any separate transaction (regardless of any existing user transaction).

 

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 Use PL/SQL block

Specify the PL/SQL 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.

 

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.