Online Documentation for SQL Manager for InterBase/Firebird

Creating/editing DB Trigger


Use the Database Trigger tab of Database Trigger Editor to view and edit the database trigger definition.

 

Database Trigger Editor provides two modes: Text editor and Visual editor. To switch between these editor modes, use the corresponding items available in the General group of the Navigation bar and toolbar.

 

Database Trigger Editor - Editing DB Trigger definition

 

 

When in the iconVisualModeText editor mode, you can use the editor area to specify the database trigger definition as SQL statement using a template, hence it is enough to simply edit the template using the editor area to make appropriate changes, and compile the database trigger.

 

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

 

 

When in the Visual editor mode, you are provided with a set of widgets allowing you to benefit from graphical user interface while editing the object properties.

 

Name

Enter a name for the new database trigger, or modify the name of the trigger being edited. Note that database trigger name must be unique in the database.

 

Trigger type

RadioButton Database

The trigger is created for the database event. Use the On event group to specify the triggering event

 

RadioButton DDL

The trigger is executed only when committing the transaction in which the affected DDL command runs. Check the required DDL statement(s) from the On event group.

 

On event

If the RadioButton Database trigger has been selected:

CheckBox Connect

  • Database connection established
  • A transaction is started
  • Triggers are fired - uncaught exceptions rollback the transaction, disconnect the attachment and are returned to the client
  • The transaction is committed

 

CheckBox Disconnect

  • A transaction is started
  • Triggers are fired - uncaught exceptions rollback the transaction, disconnect the attachment and are swallowed
  • The transaction is committed
  • The attachment is disconnected

 

CheckBox Transaction Start

  • Triggers are fired in the newly user created transaction - uncaught exceptions are returned to the client, and the transaction is rolled back

 

CheckBox Transaction Commit

  • Triggers are fired in the committing transaction - uncaught exceptions rollback the triggers savepoint, the commit command is aborted, and the exception is returned to the client

 

CheckBox Transaction Rollback

Triggers are fired in the rollback of the transaction - changes done will be rolled back together with the transaction; exceptions are swallowed.

 

If the RadioButton DDL trigger type has been selected the list of available DDL statements is displayed.

 

 

Position

Defines the order of firing database triggers on the same event. Lower-number triggers fire first, thus trigger with the default position (0) will be the first trigger to fire.

 

CheckBox Is active

Activates/deactivates the database trigger immediately after it is created. A disabled (inactive) trigger still exists as an object in the database, but does not fire.

 

 

Variables

This group is provided for managing the list of local variables declared and used only in the database trigger.

 

Parameter based on

This group allows you to select the source type for the variable being created/edited. It can be one of existing domains, a standard InterBase/Firebird data type or a column.

 

RadioButton Data type

Specifies that the parameter is based on a standard data type.

RadioButton Domain

Specifies that the parameter is based on a domain previously created in the database. Note that creating parameters based on a domain is only available for Firebird 2.1 and later.

RadioButton Column

Specifies that the parameter is based on a field.

 

Data type

 

Database Trigger Editor - Editing DB Trigger definition - Data Type

 

Variable

For NUMERIC and DECIMAL data types set digit precision (digits of precision to store, 1 to 18) and scale (number of decimal places that can be stored, 1 to 18) in the appropriate boxes; for CHAR and VARCHAR set length (in characters), for BLOB data type set segment size and subtype. You can also set the variable character set and collation order within this group.

 

Domain

 

Database Trigger Editor - Editing DB Trigger definition - Domain

 

Use the Domain drop-down list of existing domains to select the domain to be used for the variable. The collation  drop-down list is only available if the selected domain is based on a string data type. Use this drop-down list to select the collation order to be used for string data stored in the variable.

 

To edit the selected domain using Domain Editor, click the Edit Domain button.

To create a new domain for the field using Domain Editor, click the New Domain button.

 

Column

 

Database Trigger Editor - Editing DB Trigger definition - Column

 

Column group contains Table name and Field name drop-down lists that can be used to define the the column variable should be based on. Within the Collation drop-down list you can set the collation order.

 

The Not NULL and Use default value options are common for all types.

 

CheckBox Not NULL

This option indicates if a variable can contain NULL values.

 

CheckBox Use default value

With this option you can set the default variable value.

 

Cursors

This group is provided for managing the list of cursors declared to define sets of rows that can be retrieved using the cursor.

 

Procedure Editor - Managing parameters - Cursors

 

For details refer to Creating/editing trigger.

 

The lower area allows you to specify the trigger body, i.e. an optional list of local variables and their data types and a block of statements in InterBase procedure and trigger language, bracketed by BEGIN and END. These statements are performed when the trigger fires. When editing the trigger, you can edit its body using the editor area to make appropriate changes, and recompile the trigger.

 

 

To compile a database trigger, you can use the iconCompile Compile item available within the Navigation bar or toolbar.