ALTER TRIGGER

ALTER TRIGGER —change the properties of an already defined trigger

Syntax

ALTER TRIGGER trigger_name 
    FOR [schema.]table_name 
    [ POSITION pos_number ] 
    [ ACTIVE | INACTIVE ]

Description

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

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