ALTER TRIGGER

ALTER TRIGGER —change the properties of an already defined trigger

Syntax

ALTER TRIGGER trigger_name
    {FOR [schema.]table_name | ON DATABASE}
    [ POSITION pos_number ]
    [ ACTIVE | INACTIVE ]

Description

Use ALTER TRIGGER to modify a trigger’s properties. Only the POSITION and the ACTIVE/INACTIVE properties can be changed using ALTER TRIGGER. You must own the table on which the trigger is defined to be allowed to change its properties. For database triggers, the DBA role must be allowed to change trigger properties.

Parameters

trigger_name: Name of an existing trigger to alter.
schema: Name of the schema that owns the table which the trigger is being created for. If not provided, the table must exist in the current schema.
table_name: Name of the table on which the trigger acts.
ON DATABASE: Indicates that the trigger to modify is a database trigger.
POSITION pos_number: The position clause specifies the order in which multiple triggers for a table are fired for the same event. Trigger position by default is 0 and triggers are fired in order of ascending POSITION number.
ACTIVE | INACTIVE: Enables or disables a trigger. The default value is ACTIVE.

Example

Create a trigger on a table that will derive a column value based on the values of other columns when rows are inserted. Disable the trigger.

CREATE TABLE hockey_fan (id          INTEGER GENERATED ALWAYS AS IDENTITY,
                         last_name   STRING,
                         first_name  STRING,
                         full_name   STRING,
                         teamid      CHAR(3));
SET DELIMITER @
CREATE TRIGGER hockey_fan_b_insert FOR hockey_fan BEFORE INSERT
AS
    new.full_name = new.first_name || ' ' || new.last_name;
END_TRIGGER
@
SET DELIMITER ;

INSERT INTO hockey_fan (last_name, first_name, teamid)
                VALUES ('JONES','TOM','BOS');

SELECT * FROM hockey_fan;
ID  LAST_NAME  FIRST_NAME  FULL_NAME  TEAMID
 --- ---------- ----------- ---------- -------
  1    JONES        TOM     TOM JONES    BOS

ALTER TRIGGER hockey_fan_b_insert FOR hockey_fan INACTIVE;

INSERT INTO hockey_fan (last_name, first_name, teamid)
                VALUES ('SMITH','BOB','BOS');

SELECT * FROM hockey_fan;
 ID  LAST_NAME  FIRST_NAME  FULL_NAME  TEAMID
 --- ---------- ----------- ---------- -------
  1    JONES        TOM     TOM JONES    BOS
  2    SMITH        BOB       <null>     BOS