CREATE/ALTER/DROP TRIGGER

Triggers are a powerful feature of the BBj SQL file system that allows developers to execute BBj code when certain operations occur on a file. Triggers can be useful for:

  • validating data before writing it to a file

  • validating permissions before allowing a user to read or write to a file

  • performing data manipulation before writing it to a file or database table

  • and more

Developers can use the BBj Enterprise Manager to create and modify triggers using a handy graphical interface. However, BBj also provides the CREATE TRIGGER statement to manage file triggers programmatically via SQL.

CREATE TRIGGER Syntax

CREATE TRIGGER ONtable_name |'file_name'trigger_type
   ['source_file_name'] [code_block]

Parameter

Description

table_name |
'file name'

Name of the SQL table to create the trigger on, or a string value containing the path to the data file.

trigger_type

Type of trigger to create. Valid options are:

BEFORE_READ
AFTER_READ
INSTEADOF_READ

BEFORE_WRITE
AFTER_WRITE
INSTEADOF_WRITE

BEFORE_REMOVE
AFTER_REMOVE
INSTEADOF_REMOVE

BEFORE_ERASE
AFTER_ERASE
INSTEADOF_ERASE

BEFORE_OPEN
AFTER_OPEN

BEFORE_CLOSE
AFTER_CLOSE

'source_file_name'

Optional string value containing the path to the source code file for the trigger.

code block

Block of BBj code that will be executed when the stored procedure is called. The block must begin with {_BEGIN_} and end with {_END_}.

Example

The following example creates a trigger in the ChileCompany demo database on the ITEM table that logs the user name before writing the record:

CREATE TRIGGER ON item BEFORE_WRITE
{_BEGIN_}
REM Get the trigger data information
tr! = BBJAPI().getFileSystem().getTriggerData()

java.lang.System.out.println(tr!.getUser())
{_END_}

 

ALTER TRIGGER Syntax

ALTER TRIGGER ONtable_name| 'file_name' trigger_type
['source_file_name']ENABLED | DISABLED[code_block]

See the syntax for CREATE TRIGGER above for information about each option.

Example

The following example modifies the trigger in the example from CREATE TRIGGER to disable it, but still leave it available to be re-enabled:

ALTER TRIGGER ON item BEFORE_WRITE DISABLED
 

DROP TRIGGER Syntax

DROP TRIGGER ON table_name | 'file_name' trigger_type [DELETE]

See the syntax for CREATE TRIGGER above for information about the table_name, file_name, and trigger_type options. Specify the DELETE option to remove  the source code files for the trigger. If DELETE is not included, the source files for the trigger will be left intact.

Example

The following example drops the trigger in the example from CREATE TRIGGER and removes the source code files for the trigger:

DROP TRIGGER ON item BEFORE_WRITE DELETE