Cloudscape System Tables
Page 16 of 17

SYSTRIGGERS

Describes the database's triggers.

Column Name

Type

Length

Nullability

Contents

TRIGGERID

CHAR

36

false

unique identifier for the trigger

TRIGGERNAME

VARCHAR

128

false

name of the trigger

SCHEMAID

CHAR

36

false

id of the trigger's schema (join with SYSSCHEMAS.SCHEMAID)

CREATIONTIMESTAMP

TIMESTAMP


false

time the trigger was created

EVENT

CHAR

1

false

`U' for update, `D' for delete, `I' for insert

FIRINGTIME

CHAR

1

false

`B' for before `A' for after

TYPE

CHAR

1

false

`R' for row, `S' for statement

STATE

CHAR

1

false

`E' for enabled, `D' for disabled

TABLEID

CHAR

36

false

id of the table on which the trigger is defined

WHENSTMTID

CHAR

36

true

used only if there is a WHEN clause (not yet supported)

ACTIONSTMTID

CHAR

36

false

id of the stored prepared statement for the trigger action (join with SYSSTATEMENTS.STMTID)

REFERENCEDCOLUMNS

SERIALIZE(
COM.cloudscape.
types.
ReferencedColumns
Descriptor
)


true

descriptor of the columns referenced by UPDATE triggers

TRIGGERDEFINITION

LONG VARCHAR


true

body of CREATE TRIGGER statement

REFERENCINGOLD

BOOLEAN


true

whether or not the REFERENCINGNAME, if non-null, refers to the OLD row or table

REFERENCINGNEW

BOOLEAN


true

whether or not the REFERENCINGNAME, if non-null, refers to the NEW row or table

REFERENCINGNAME

VARCHAR

128

true

the correlation name or identifier from the REFERENCING clause

Any SQL-J text that is part of a trigger action is compiled and stored in SYSSTATEMENTS. ACTIONSTMTID and WHENSTMTID are foreign keys that reference SYSSTATEMENTS.STMTID. The statements for a trigger are always in the same schema as the trigger.

Indexes

  • SYSTRIGGERS_INDEX1 unique BTREE index on (TRIGGERID)
  • SYSTRIGGERS_INDEX2 unique BTREE index on (TRIGGERNAME, SCHEMAID)
  • SYSTRIGGERS_INDEX3 BTREE index on (TABLEID, CREATIONTIMESTAMP)

-- get the name, event, and type of each trigger
SELECT t.TRIGGERNAME, t.EVENT,
    (t.TYPE='R'?'ROW': 'STATEMENT') AS TYPE
FROM SYS.SYSTRIGGERS t, SYS.SYSTABLES tbl
WHERE t.tableID = tbl.tableId

-- get the text action for each trigger
-- (the text action is also stored in SYS.SYSSTATEMENTS)

SELECT t.TRIGGERNAME, t.TRIGGERDEFINITION
FROM SYS.SYSTRIGGERS t