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