创建触发器

创建触发器 “创建一个新的触发器

句法

CREATE [OR REPLACE] TRIGGER trigger_name
    {[FOR] [schema.]table_name|ON DATABASE }
    [[ BEFORE | AFTER ] event]
    [ POSITION number ]
    [ ACTIVE | INACTIVE ]
    [ LANGUAGE { SQL | JAVA } ]
    [FOR EACH ROW|FOR EACH STATEMENT]
{
    EXTERNAL 'trigger_routine'
|
    AS
        sql_code_block
    END_TRIGGER;
}

描述

创建触发器 creates a new trigger. The trigger is associated with the specified table and executes the statements when certain events occur. Only users that own the table, or users that have been granted ALL privilege on a table, can create a trigger for a table. A single trigger can be specified for multiple events.

When the OR REPLACE clause is used for an existing trigger name, the effect is that of dropping the existing trigger and creating a new one with the given specification. OR REPLACE does not have any effect on the command if a trigger with the specified name does not already exist.

无法在触发器中调用事务提交和回滚语句。
使用创建或替换语句时,与两个不同的事务同时创建相同的数据库对象时,可能会返回以下错误:
duplicate value in unique index ...
如果通过基于表达式的索引调用用户定义的函数,则任何删除或修改函数的尝试都会使用错误警告,但不会重新创建索引。

在活动之前或之后

The trigger can be specified to fire before (BEFORE event) the operation is attempted on a row (for example, before constraints are changed, an , 更新, or 删除 is attempted); or after (AFTER event) the operation has completed (for example, after constraints are checked and the , 更新, or 删除 completes). If the trigger fires before the event, the trigger can skip the operation for the current row, or change the row being inserted or updated. If the trigger fires after the event, all changes, including the effects of other triggers, are "visible" to the trigger.

The trigger is called once for every row the operation modifies. For example, a 删除 operation that affects 10 rows will cause all ON DELETE triggers defined for that table to be called ten (10) separate times, once for each deleted row. If the 删除 operation affects zero (0) rows, no triggers will fire.
Since a 选择 statement does not modify rows, you cannot create triggers on 选择 events.

触发器利用两个特殊表:

  • NEW is an alias for a table that contains the data referenced in an or 更新 trigger before it is actually committed to the database.

  • OLD is an alias for a table that contains the data in the underlying table referenced in an 更新 or 删除 trigger before it is actually updated or removed from the database.

请参见下面的示例。

与整个数据库关联的触发器

当触发器与整个数据库关联时,事件可以是创建表,删除表,创建索引和丢弃索引。在执行这些DDL操作之一之前或之后调用触发器,并且可以利用三个预定义变量中的一个来检测被操纵的对象:

  • ddl_schema_name是表/索引模式的名称

  • ddl_object_name是表/索引的名称

  • ddl_operation_type是create_table,drop_table,create_index或drop_index

触发与整个陈述相关联

当触发器与整个语句关联时,事件可以是提交,删除,插入和更新。每次语句执行后,每个触发器都会被调用,而无论或受影响的行数。在执行DML操作之后执行的触发器的情况下,程序代码可以利用两个预定义的表来检测正在操纵的行:

  • old_table是一个持有行的表,因为它们是在被操纵之前的:它仅用于删除和更新语句

  • new_table是一个持有行的表,因为它们被操纵后它们:它仅填充更新和插入语句

位置

The POSITION clause specifies the order in which multiple triggers for a table are fired for the same operation for the same event. Trigger position by default is 0 and triggers are fired in order of ascending POSITION[.var]number.

主动或无效

A trigger can be created as disabled using the INACTIVE keyword and enabled at a later date using 改变触发器. By default a trigger is created as ACTIVE.

The language for a stored procedure, user defined function or trigger may be SQL or Java. This is specified by declaring the procedure, function or trigger with LANGUAGE SQL or LANGUAGE JAVA. The default language, if none is specified, is SQL. SQL stored procedures, UDFs, and triggers are defined by sql_code_block. See SQL程序语言 想要查询更多的信息。

Stored procedures, user defined functions and triggers written in Java must use the EXTERNAL keyword. See 使用嵌入式Java存储过程.

每行/每个陈述

如果两者都没有指定选项, 对于每一行 是默认选项。

外部定义

Declaring a trigger as EXTERNAL is done to describe a dynamically loadable Java trigger that is to be executed. For LANGUAGE JAVA, the EXTERNAL trigger defined by sp_routine must be in the format classid:fully_qualified_class.method, where classid is the ID used in the 创建javaclass. command that was used to upload the Jar [not JAR] with the defined code.

SQL程序语言 - 创建触发器 body

如果在SQL中定义此存储过程,则SQL_Code_block是一个或多个SQL语句。看 SQL程序语言 更多信息。

参数

trigger_name

名称要给新触发器。这必须与同一表的任何其他触发器的名称不同。模式名称不能包含在这里;触发器始终以与所定义的表相同的模式以与其定义的表相同。触发名称不能大于128个字符。

schema

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

table_name

触发器运行的表的名称。

ON DATABASE

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

event

One of , 更新, or 删除; the event is what actuates the 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.

例子

示例1:创建一个触发到火灾 BEFORE INSERT

Create a trigger to fire BEFORE INSERT. However, the trigger will only perform changes if the boolean condition is true.

 DELIMITER @
CREATE TRIGGER trg_hockey_insert FOR hockey.hockey BEFORE 
AS
    IF (new.team <> 'Bruins')
        THROW 'Team Must Be "Bruins"';
    END_IF;
END_TRIGGER
@
 DELIMITER ;

 INTO hockey.hockey (number, name, position, team)
                   VALUES (99,'BOBBY ORR','Forward','Bruins');
 INTO hockey.hockey (number, name, position, team)
                   VALUES (2, 'TOM JONES', 'Fan', 'Rangers');
Team Must Be "Bruins"

选择 * FROM hockey.hockey WHERE number IN (99,2);
ID  NUMBER    NAME    POSITION   TEAM
 --- ------- --------- --------- ------
 25    99    BOBBY ORR  Forward  Bruins
示例2:创建触发器以导出表中的值
DROP TABLE IF EXISTS hockey_fan CASCADE;
CREATE TABLE hockey.hockey_fan (id          INTEGER GENERATED ALWAYS AS IDENTITY,
                         last_name   STRING,
                         first_name  STRING,
                         full_name   STRING,
                         teamid      CHAR(3),
                         create_date DATE DEFAULT DATE('now'),
                         modify_date DATE );
 DELIMITER @
CREATE TRIGGER trg_hockeyfan_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  CREATE_DATE  MODIFY_DATE
 --- ---------- ----------- ---------- ------- ------------ ------------
  1    JONES        TOM     TOM JONES    BOS    2013-10-28     <null>
示例3:创建触发器以维护对另一个表所做的更新表

创建一个触发器,将维护对另一个表所做的更新审计表。此示例使用示例2中创建的表和触发器。该示例还示出了具有SQL语句的触发器,其引用另一个架构中的表。

DROP TABLE IF EXISTS audit.hockey_fan_audit CASCADE;
CREATE TABLE audit.hockey_fan_audit (id          STRING NOT NULL,
                               old_teamid  CHAR(3),
                               new_teamid  CHAR(3),
                               audit_date  DATE DEFAULT DATE('now') );
 DELIMITER @
CREATE TRIGGER trg_hockeyfan_b_update FOR hockey.hockey_fan BEFORE UPDATE
AS
    new.full_name = new.first_name || ' ' || new.last_name;
    new.modify_date = DATE('now');
END_TRIGGER
@

CREATE TRIGGER trg_hockeyfan_a_update FOR hockey.hockey_fan AFTER UPDATE
AS
    IF (old.teamid != new.teamid)
         INTO audit.hockey_fan_audit (id, old_teamid, new_teamid)
                            VALUES (old.id, old.teamid, new.teamid);
    END_IF;
END_TRIGGER
@
 DELIMITER ;


选择 * FROM hockey.hockey_fan;    /* Created in Example 3 */
 ID  LAST_NAME  FIRST_NAME  FULL_NAME  TEAMID  CREATE_DATE  MODIFY_DATE
 --- ---------- ----------- ---------- ------- ------------ ------------
  1    JONES        TOM     TOM JONES    BOS    2013-10-28     <null>

UPDATE hockey.hockey_fan
    teamid = 'NYR'
  WHERE last_name = 'JONES' and first_name = 'TOM';

选择 * FROM hockey.hockey_fan;
 ID  LAST_NAME  FIRST_NAME  FULL_NAME  TEAMID  CREATE_DATE  MODIFY_DATE
 --- ---------- ----------- ---------- ------- ------------ ------------
  1    JONES        TOM     TOM JONES    NYR    2013-10-28   2013-10-28

选择 * FROM audit.hockey_fan_audit;
 ID  OLD_TEAMID  NEW_TEAMID  AUDIT_DATE
 --- ----------- ----------- -----------
  1      BOS         NYR     2013-10-28
Example 4: Create a trigger that uses a FOR SELECT loop

创建一个触发器,它将基于从另一个表的多个行列值连接到插入的行的列值。

 用  HOCKEY;
DROP TABLE IF EXISTS player_years;
CREATE TABLE player_years (Player_Id    STRING NOT NULL,
                           Years_Played  STRING);
 DELIMITER @
CREATE TRIGGER trg_player_years_before_insert FOR Player_years BEFORE 
  AS
    VAR l_Years_Played string = '';
    FOR 选择 DISTINCT year AS team_year
           FROM scoring
           WHERE playerid = new.Player_Id;
         l_Years_Played = l_Years_Played || team_year || ',';
    END_FOR;
    new.Years_Played = substr(l_Years_Played,1,length(l_Years_Played) -1);
END_TRIGGER;
@

 DELIMITER ;

 INTO Player_Years (Player_Id) 选择 playerId FROM players limit 5;

选择 * FROM Player_Years;
 PLAYER_ID        YEARS_PLAYED
 ---------- ------------------------
 aaltoan01  1997,1998,1999,2000
 abbeybr01  1975
 abbotge01  1943
 abbotre01  1952
 abdelju01  2007,2008,2009,2010,2011
Example 5: Create a trigger that updates a table following a 创建表 event

在数据库触发器上创建一个审核表的创建,通过将关联的行插入新创建的ddl_operations表来审核表。

CREATE TABLE ddl_operations (schema_name STRING, object_name STRING, operation_type STRING);
 DELIMITER @
CREATE TRIGGER ddl_trigger_after_create_table ON DATABASE AFTER CREATE TABLE
AS
 INTO ddl_operations VALUES (DDL_SCHEMA_NAME, DDL_OBJECT_NAME, DDL_OPERATION_TYPE);
END_TRIGGER;
@

 DELIMITER ;
CREATE TABLE a.table1 (value INT);
CREATE TABLE b.table2 (value INT);

选择 * FROM ddl_operations;

SCHEMA_NAME  OBJECT_NAME  OPERATION_TYPE
------------ ------------ ---------------

A          TABLE1     create_table
B          TABLE2     create_table