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

A bitmask integer value that represents each event that causes firing of this trigger. The description of each bit is:

(Row-level triggers)

  • 0x1 = Before Insert
  • 0x2 = After Insert
  • 0x4 = Before Update
  • 0x8 = After Update
  • 0x10 = Before Delete
  • 0x20 = After Delete
  • 0x40 = Reserved for future use
  • 0x80 = Reserved for future use

(Statement-level triggers)

  • 0x100 = Before Statement Insert
  • 0x200 = After Statement Insert
  • 0x400 = Before Statement Update
  • 0x800 = After Statement Update
  • 0x1000 = Before Statement Delete
  • 0x2000 = After Statement Delete

(Database-level triggers)

  • 0x4000 = Before Create Table
  • 0x8000 = After Create Table
  • 0x10000 = Before Drop Table
  • 0x20000 = After Drop Table
  • 0x40000 = Before Create Index
  • 0x80000 = After Create Index
  • 0x100000 = Before Drop Index
  • 0x200000 = After Drop Index
  • 0x400000 = Before Create Temporary Table
  • 0x800000 = After Create Temporary Table
  • 0x1000000 = Before Drop Temporary Table
  • 0x2000000 = After Drop Temporary Table
  • 0x4000000 = Before Create Temporary Index
  • 0x8000000 = After Create Temporary Index
  • 0x10000000 = Before Drop Temporary Index
  • 0x20000000 = After Drop Temporary Index

For example, using a trigger to illustrate the combined value of Before Delete and Before Update:

SQL> create trigger trg1 for t before delete before update as throw 'aaa'; end_trigger;

SQL> select type_mask from system.triggers;

TYPE_MASK

---------

20

The integer value 20 is 0x14 hexadecimal. If the hexadecimal value such as 0x14 is listed in the description above, it represents the single event that causes this trigger to fire. If it is not displayed in the description above (as in the example offered here), the hexadecimal value represents more than one event that causes this trigger to fire.

The event type mask of 20 that represents Before Delete and Before Update is the combination of [0x10 | 0x4] in hexadecimal or the combination of [16| 4 ] in decimal.

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:

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