The Firebird 5.0 Language Reference | CREATE OR ALTER TRIGGER

`CREATE OR ALTER TRIGGER`

Creates a trigger if it doesn’t exist, or alters a trigger

Available in

DSQL

Syntax

CREATE OR ALTER TRIGGER trigname
  { <relation_trigger_legacy>
  | <relation_trigger_sql>
  | <database_trigger>
  | <ddl_trigger> }
  {<psql_trigger> | <external-module-body>}

!! See syntax of CREATE TRIGGER for further rules !!

The CREATE OR ALTER TRIGGER statement creates a new trigger if it does not exist;otherwise it alters and recompiles it with the privileges intact and dependencies unaffected.

Example of `CREATE OR ALTER TRIGGER`

Creating a new trigger if it does not exist or altering it if it does exist

CREATE OR ALTER TRIGGER set_cust_no
ACTIVE BEFORE INSERT ON customer POSITION 0
AS
BEGIN
  IF (NEW.cust_no IS NULL) THEN
    NEW.cust_no = GEN_ID(cust_no_gen, 1);
END

`DROP TRIGGER`

Drops a trigger

Available in

DSQL, ESQL

Syntax

DROP TRIGGER trigname

Table 1. `DROP TRIGGER` Statement Parameter
Parameter	Description
trigname	Trigger name

The DROP TRIGGER statement drops (deletes) an existing trigger.

Who Can Drop a Trigger?

DML triggers can be dropped by:

Administrators
The owner of the table (or view)
Users with — for a table — the ALTER ANY TABLE, or — for a view — ALTER ANY VIEW privilege

Database and DDL triggers can be dropped by:

Administrators
Users with the ALTER DATABASE privilege

Example of `DROP TRIGGER`

Deleting the set_cust_no trigger

DROP TRIGGER set_cust_no;

`RECREATE TRIGGER`

Drops a trigger if it exists, and creates a trigger

Available in

DSQL

Syntax

RECREATE TRIGGER trigname
  { <relation_trigger_legacy>
  | <relation_trigger_sql>
  | <database_trigger>
  | <ddl_trigger> }
  {<psql_trigger> | <external-module-body>}

!! See syntax of CREATE TRIGGER for further rules !!

The RECREATE TRIGGER statement creates a new trigger if no trigger with the specified name exists;otherwise the RECREATE TRIGGER statement tries to drop the existing trigger and create a new one.The operation will fail on COMMIT if the trigger is in use.

Warning

Be aware that dependency errors are not detected until the COMMIT phase of this operation.

Example of `RECREATE TRIGGER`

Creating or recreating the set_cust_no trigger.

RECREATE TRIGGER set_cust_no
ACTIVE BEFORE INSERT ON customer POSITION 0
AS
BEGIN
  IF (NEW.cust_no IS NULL) THEN
    NEW.cust_no = GEN_ID(cust_no_gen, 1);
END

CREATE OR ALTER TRIGGER

CREATE OR ALTER TRIGGER

Example of CREATE OR ALTER TRIGGER

docnext count = 5

DROP TRIGGER

Who Can Drop a Trigger?

Example of DROP TRIGGER

RECREATE TRIGGER

Example of RECREATE TRIGGER

`CREATE OR ALTER TRIGGER`

Example of `CREATE OR ALTER TRIGGER`

`DROP TRIGGER`

Example of `DROP TRIGGER`

`RECREATE TRIGGER`

Example of `RECREATE TRIGGER`