改变触发器

改变触发器 - 更改已定义触发器的属性

句法

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

描述

Use 改变触发器 to modify a trigger’s properties. Only the POSITIONACTIVE/INACTIVE properties can be changed using 改变触发器. 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.

参数

trigger_name

要改变现有触发器的名称。

schema

拥有触发器正在为此创建表的模式的名称。如果没有提供,则表必须存在于当前模式中。

table_name

触发行为的表的名称。

ON DATABASE

表示修改的触发器是数据库触发。

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.

例子

在插入行时,在表上创建一个触发器,该表将根据其他列的值导出列值。禁用触发器。

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

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

选择 * 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;

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

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