FirebirdSQL logo
 VIEWPROCEDURE 

Who Can Alter a Trigger?

DML triggers can be altered 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 altered by:

Examples using ALTER TRIGGER

  1. Deactivating the set_cust_no trigger (switching it to the inactive status).

    ALTER TRIGGER set_cust_no INACTIVE;

  2. Changing the firing order position of the set_cust_no trigger.

    ALTER TRIGGER set_cust_no POSITION 14;

  3. Switching the TR_CUST_LOG trigger to the inactive status and modifying the list of events.

    ALTER TRIGGER TR_CUST_LOG
INACTIVE AFTER INSERT OR UPDATE;

  4. Switching the tr_log_connect trigger to the active status, changing its position and body.

    ALTER TRIGGER tr_log_connect
ACTIVE POSITION 1
AS
BEGIN
  INSERT INTO LOG_CONNECT (ID,
                           USERNAME,
                           ROLENAME,
                           ATIME)
  VALUES (NEXT VALUE FOR SEQ_LOG_CONNECT,
          CURRENT_USER,
          CURRENT_ROLE,
          CURRENT_TIMESTAMP);
END
See also

[fblangref50-ddl-trgr-create], [fblangref50-ddl-trgr-crtalter], [fblangref50-ddl-trgr-recreate], [fblangref50-ddl-trgr-drop]

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
See also

[fblangref50-ddl-trgr-create], [fblangref50-ddl-trgr-alter], [fblangref50-ddl-trgr-recreate]

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:

Example of DROP TRIGGER

Deleting the set_cust_no trigger
DROP TRIGGER set_cust_no;
See also

[fblangref50-ddl-trgr-create], [fblangref50-ddl-trgr-recreate]

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
See also

[fblangref50-ddl-trgr-create], [fblangref50-ddl-trgr-drop], [fblangref50-ddl-trgr-crtalter]