TRIGGERS System Table Description

Description

Information about triggers.

Fields

Field Type Description
SCHEMA string Schema containing the table on which this trigger is placed.
TABLENAME string Name of table on which this trigger is placed.
TRIGGERNAME string Name of trigger
TRIGGER_TYPE integer Indicates the language of the trigger, defined as: 0=Java, 1=SQL
TYPE_MASK integer

Defines the trigger event:

  • 1 = Before Insert
  • 2 = After Insert
  • 4 = Before Update
  • 8 = After Update
  • 16 = Before Delete
  • 32 = After Delete
  • 64 = Before Commit - reserved for future use
  • 128 = After Commit - reserved for future use
POSITION smallint A user-provided index that defines the order of invocations of triggers (lower value means early execution). This corresponds to the POSITION number argument - see CREATE TRIGGER.
ACTIVE smallint Defined as: 1=Active, 0=Inactive
CLASSNAME varchar (256) For Java triggers, the classid specified in the CREATE TRIGGER command.
METHODNAME varchar (256) For Java triggers, the fully qualified name of the method to be invoked by the trigger.
TRIGGER_TEXT string For SQL triggers, the actual text of the trigger.
VERSION integer Internal version number reserved for future use.

Indexes

Primary Index: TRIGGERS..PRIMARY_KEY on fields: SCHEMA, TABLENAME, TRIGGERNAME

Example

The first row below represents a SQL trigger and the second row represents a Java trigger. We use SET OUTPUT VERTICAL to make the display more readable.

SET OUTPUT VERTICAL;
SELECT * FROM system.triggers;
==================================== Row #1 ====================================
SCHEMA: HOCKEY
TABLENAME: HOCKEY
TRIGGERNAME: TRG_HOCKEY_INSERT
TRIGGER_TYPE: 1
TYPE_MASK: 1
POSITION: 0
ACTIVE: 1
CLASSNAME: 
METHODNAME: 
TRIGGER_TEXT: IF (new.team <> 'Bruins') THROW 'Team Must Be "Bruins"'; END_IF; END_TRIGGER ;
==================================== Row #2 ====================================
SCHEMA: USER
TABLENAME: MYTABLE
TRIGGERNAME: TR1
TRIGGER_TYPE: 0
TYPE_MASK: 2
POSITION: 0
ACTIVE: 1
CLASSNAME: mytriggerclass
METHODNAME: com.mycompany.MyFirstJavaTrigger.logInsert
TRIGGER_TEXT:

Trigger Execution Table

The following table reflects current trigger behavior as follows:

When using IF EXISTS or IF NOT EXISTS,

Trigger Event SQL Statement SQL Query Target Exists Executed
Before CREATE TABLE CREATE TABLE IF NOT EXISTS No Yes
After CREATE TABLE CREATE TABLE IF NOT EXISTS No Yes
Before CREATE TABLE CREATE TABLE IF NOT EXISTS Yes Yes
After CREATE TABLE CREATE TABLE IF NOT EXISTS Yes No
Before DROP TABLE DROP TABLE IF EXISTS No Yes
After DROP TABLE DROP TABLE IF EXISTS No No
Before DROP TABLE DROP TABLE IF EXISTS Yes Yes
After DROP TABLE DROP TABLE IF EXISTS Yes Yes
Before DROP INDEX DROP INDEX IF EXISTS No Yes
After DROP INDEX DROP INDEX IF EXISTS No No
Before DROP INDEX DROP INDEX IF EXISTS Yes Yes
After DROP INDEX DROP INDEX IF EXISTS Yes Yes