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

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