ALTER TRIGGER

ALTER TRIGGER

Multi-action triggers
Restriction on altering used triggers
PLAN allowed in trigger code
ALTER TRIGGER no longer increments
table change count

`ALTER TRIGGER`

Available in: DSQL, ESQL

Description

Alters an existing trigger. The table or view that the trigger belongs to cannot be changed.

Syntax

 ALTER TRIGGER name
    [ACTIVE | INACTIVE]
    [{BEFORE | AFTER} <action_list>]
    [POSITION number]
    [AS <trigger_body>]

 <action_list> ::= <action> [OR <action> [OR <action>]]
 <action>      ::= INSERT | UPDATE | DELETE

Multi-action triggers

Added in: 1.5

Description

The ALTER TRIGGER syntax (see above) has been extended to support multi-action triggers. For a full discussion of this feature, see CREATE TRIGGER :: Multi-action triggers.

Restriction on altering used triggers

Changed in: 2.0, 2.0.1

Description

In Firebird 2.0 only, a restriction is in place which prevents anyone from dropping, altering or recreating a trigger or stored procedure if it has been used since the database was opened. This restriction has been removed again in version 2.0.1.

Still, performing these operations on a live database is potentially risky and should only be done with the utmost care.

`PLAN` allowed in trigger code

Changed in: 1.5

Description

Before Firebird 1.5, a trigger containing a PLAN statement would be rejected by the compiler. Now a valid plan can be included and will be used.

`ALTER TRIGGER` no longer increments table change count

Changed in: 1.0

Description

Each time you use CREATE, ALTER or DROP TRIGGER, InterBase increments the metadata change counter of the associated table.

Once that counter reaches 255, no more metadata changes are possible on the table (you can still work with the data though). A backup-restore cycle is needed to reset the counter and perform metadata operations again.

While this obligatory cleanup after many metadata changes is in itself a useful feature, it also means that users who regularly use ALTER TRIGGER to deactivate triggers during e.g. bulk import operations are forced to backup and restore much more often then needed.

Since changes to triggers don't imply structural changes to the table itself, Firebird no longer increments the table change counter when CREATE, ALTER or DROP TRIGGER is used. One thing has remained though: once the counter is at 255, you can no longer create, alter or drop triggers for that table.