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

  • 0x40000000 = Before Create Sequence

  • 0x80000000 = After Create Sequence

  • 0x100000000 = Before Drop Sequence

  • 0x200000000 = After Drop Sequence

  • 0x400000000 = Before Create Procedure

  • 0x800000000 = After Create Procedure

  • 0x1000000000 = Before Drop Procedure

  • 0x2000000000 = After Drop Procedure

  • 0x4000000000 = Before Create Function

  • 0x8000000000 = After Create Function

  • 0x10000000000 = Before Drop Function

  • 0x20000000000 = After Drop Function

  • 0x40000000000 = Before Create View

  • 0x80000000000 = After Create View

  • 0x100000000000 = Before Drop View

  • 0x200000000000 = After Drop View

  • 0x400000000000 = Before Create User

  • 0x800000000000 = After Create User

  • 0x1000000000000 = Before Drop User

  • 0x2000000000000 = After Drop User

  • 0x4000000000000 = Before Create Schema

  • 0x8000000000000 = After Create Schema

  • 0x10000000000000 = Before Drop Schema

  • 0x20000000000000 = After Drop Schema

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
Unique Index: UNIQUE_TRIGGER_NAME on fields: SCHEMA, 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,

  • If a CREATE or DROP action occurs on the target, both the related BEFORE and AFTER triggers are fired.

  • If a CREATE or DROP action does not occur on the target, only the related BEFORE triggers are fired.

See the following examples:

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