SQL命令 CREATE TRIGGER（一）

2022-04-18 11:53:38 浏览数 (1)

创建触发器

大纲

代码语言：javascript复制
CREATE TRIGGER trigname {BEFORE | AFTER} event [,event]
          [ORDER integer]
          ON table
          [REFERENCING {OLD | NEW} [ROW] [AS] alias]
         action

参数

  • trigname - 要创建的触发器的名称，它是一个标识符。触发器名称可以是限定的，也可以是非限定的；如果限定，则其架构名称必须与表的架构名称匹配。
  • BEFORE event，AFTER event - 事件执行触发器的时间(之前或之后)。 触发器事件或以逗号分隔的触发器事件列表。可用的事件列表选项包括INSERT、DELETE和UPDATE。 可以指定事件的单个更新。UPDATE OF子句后跟列名或逗号分隔的列名列表。仅当language为SQL时才能指定UPDATE OF子句。不能在逗号分隔的事件列表中指定UPDATE OF子句。
  • ORDER integer - 可选-当具有相同时间和事件的表有多个触发器时，触发器的执行顺序。如果省略顺序，则为触发器分配的顺序为0。
  • ON table - 为其创建触发器的表。表名可以是限定的，也可以是非限定的；如果限定，则触发器必须驻留在与表相同的架构中。
  • REFERENCING OLD ROW AS alias，REFERENCING NEW ROW AS alias - 可选-仅当Language为SQL时才能使用REFERENCING子句。REFERENCING子句允许指定可用于引用列的别名。引用旧行允许在UPDATE或DELETE触发器期间引用列的旧值。引用新行允许在INSERT或UPDATE触发器期间引用列的新值。作为关键字的行是可选的。对于更新，可以在同一引用子句中指定old和new，如下所示：REFERENCING OLD oldalias NEW newalias。
  • action - 触发器的程序代码。Action参数可以包含各种可选关键字子句，包括(按顺序)：For Each子句；带有控制触发操作执行的谓词条件的WHEN子句；以及指定Language SQL或Language OBJECTSCRIPT的LANGUAGE子句。如果省略LANGUAGE子句，则默认为SQL。在这些子句之后，指定一行或多行SQL触发器代码或ObjectScript触发器代码，指定在执行触发器时要执行的操作。

描述

CREATE TRIGGER命令定义触发器，即修改特定表中的数据时要执行的代码块。当特定的触发事件发生时(例如将新行插入到指定表中)，就会执行(“触发”或“拉出”)触发器。触发器执行用户指定的触发器代码。可以指定触发器应该在执行触发事件之前或之后执行此代码。触发器特定于指定表。

  • 触发器由指定的事件触发：INSERT、DELETE或UPDATE操作。可以指定逗号分隔的事件列表，以便在指定表上发生任何指定事件时执行触发器。
  • 一个触发器由一个事件触发(可能)多次或只触发一次。每修改一行，就触发一次行级触发器。语句级触发器对一个事件触发一次。此触发器类型是使用FOR EACH子句指定的。行级触发器是默认的触发器类型。
  • 通常，触发触发器代码会对另一个表或文件执行操作，例如执行日志记录操作或显示消息。触发触发器不能修改触发记录中的数据。例如，如果更新记录7会触发触发器，则该触发器的代码块不能更新或删除记录7。触发器可以修改调用该触发器的同一个表，但触发事件和触发器代码操作必须不同，以防止递归触发器无限循环。

如果要修改现有触发器，则必须先调用DROP TRIGGER删除旧版本的触发器，然后再调用CREATE TRIGGER来替换它。DROP TABLE删除与该表关联的所有触发器。

权限和锁

CREATE TRIGGER命令是特权操作。用户必须具有%CREATE_TRIGGER管理权限才能执行CREATE TRIGGER。否则将导致%msg User 'name' does not have %CREATE_TRIGGER privileges的SQLCODE-99错误。

用户必须对指定表拥有%ALTER特权。如果用户是表的所有者(创建者)，则会自动授予该用户对该表的%ALTER权限。否则，必须授予用户对该表的%ALTER特权。否则将导致SQLCODE-99错误，并显示%msg User 'name' does not have required %ALTER privilege needed to create a trigger on table: 'Schema.TableName'

如果拥有适当的授予权限，则可以使用GRANT命令分配%CREATE_TRIGGER和%ALTER权限。

在嵌入式SQL中，可以使用$SYSTEM.Security.Login()方法以具有适当权限的用户身份登录：

代码语言：javascript复制
   DO $SYSTEM.Security.Login("_SYSTEM","SYS")
   &sql(      )

必须具有%Service_Login：Use权限才能调用$SYSTEM.Security.Login方法。

  • CREATE TRIGGER不能用于从持久类投影的表，除非表类定义包括[DdlAllowed]。否则，操作将失败，并显示SQLCODE-300错误%msg DDL not enabled for class 'Schema.tablename'.
  • CREATE TRIGGER不能用于从部署的持久类投射的表。此操作失败，并显示%msg的SQLCODE-400错误 Unable to execute DDL that modifies a deployed class: 'classname'.

CREATE TRIGGER语句获取TABLE的表级锁。这可以防止其他进程修改表的数据。此锁在创建触发器操作结束时自动释放。

若要创建触发器，表不能在独占模式或共享模式下被另一个进程锁定。尝试在锁定表上执行CREATE TRIGGER操作将导致SQLCODE-110错误，并显示如下%msg： Unable to acquire exclusive table lock for table 'Sample.MyTest'。

定义触发器的其他方式

可以将SQL触发器定义为类对象，如触发器定义中所述。以下是对象触发器的示例：

代码语言：javascript复制
Trigger SQLJournal [ CodeMode = objectgenerator, Event = INSERT/UPDATE, ForEach = ROW/OBJECT, Time = AFTER ]
{  /* ObjectScript trigger code
      that updates a journal file
      after a row is inserted or updated. */
}

参数

trigname

触发器名称遵循与表名称相同的标识符要求，但不具有相同的惟一性要求。 触发器名称对于模式中的所有表应该是唯一的。 因此，在一个模式中引用不同表的触发器不应该具有相同的名称。 违反这种唯一性要求可能会导致DROP TRIGGER错误。

触发器及其关联表必须驻留在相同的模式中。 在相同的模式中，不能对触发器和表使用相同的名称。 违反触发器命名约定将导致在CREATE trigger执行时出现SQLCODE -400错误。

触发器名称可以是限定的，也可以是限定的。 限定触发器名称的形式如下:

代码语言：javascript复制
schema.trigger

如果触发器名称未限定，则触发器架构名称默认为与指定表架构相同的架构。如果表名不合格，则表架构名默认为与指定触发器架构相同的架构。如果两者都未限定，则使用默认架构名称；不使用架构搜索路径。如果两者都是限定的，则触发器架构名称必须与表架构名称相同。模式名称不匹配会导致SQLCODE-366错误；只有当触发器名称和表名都是限定的，并且它们指定了不同的模式名称时才会出现这种情况。

触发器名称遵循标识符约定，受以下限制。默认情况下，触发器名称是简单标识符。触发器名称不应超过128个字符。触发器名称不区分大小写。

IRIS使用TRIGNAME IRIS类中生成相应的触发器名称。相应的类触发器名称仅包含字母数字字符(字母和数字)，最大长度为96个字符。要生成此标识符名， IRIS首先从触发器名称中删除标点符号，然后生成96个(或更少)字符的唯一标识符，在需要创建唯一名称时用数字代替第96个字符。此名称生成对触发器的命名施加了以下限制：

  • 触发器名称必须至少包含一个字母。触发器名称的第一个字符或首个标点符号字符后的第一个字符必须是字母。
  • IRIS支持使用16位(宽)字符作为触发器名称。如果字符通过$ZNAME测试，则该字符是有效字母。
  • 由于为IRIS类生成的名称不包括标点符号，因此不建议(尽管可能)创建仅在标点符号方面不同的触发器名称。
  • 触发器名称可能比96个字符长得多，但前96个字母数字字符不同的触发器名称更易于使用。

使用现有触发器的名称发出CREATE TRIGGER会发出SQLCODE-365“触发器名称不唯一”错误。要更改现有触发器，必须发出DROP TRIGGER，然后使用新的触发器定义执行CREATE TRIGGER。如果模式中引用不同表的两个触发器具有相同的名称，则DROP TRIGGER可能会发出SQLCODE-365“Trigger Name Not Unique”错误，并显示消息“Trigger‘MyTrigName’Found in 2 CLASS”。

event

触发触发器的时间由BEFORE或AFTER关键字指定；这些关键字指定触发器操作应在 IRIS执行触发事件之前或之后发生。在执行指定事件之前但在验证事件之后执行BEFORE触发器。例如， IRIS仅在DELETE语句对指定行有效并且进程具有执行删除所需的权限(包括任何外键引用完整性检查)的情况下才执行BEFORE DELETE触发器。如果进程无法执行指定的事件， IRIS将为该事件发出错误代码；它不会执行BEFORE触发器。

关键字BEFORE或AFTER后跟触发事件的名称，或以逗号分隔的触发事件列表。在指定表中插入行时，将执行指定为INSERT的触发器。从指定表中删除行时，将执行指定为DELETE的触发器。在指定表中更新行时，将执行指定为UPDATE的触发器。可以按任意顺序指定单个触发器事件或以逗号分隔的INSERT、UPDATE或DELETE触发器事件列表。

指定为UPDATE OF的触发器仅在指定表的一行中更新了一个或多个指定列时才执行。列名指定为逗号分隔的列表。列名可以按任何顺序指定。触发器的更新有以下限制：

  • UPDATE OF仅在触发器代码语言为SQL(默认)时有效；如果触发器代码语言为OBJECTSCRIPT，则会发出SQLCODE-50错误。
  • UPDATE OF不能与其他触发事件组合；如果在逗号分隔的触发事件列表中指定UPDATE OF，则会发出SQLCODE-1错误。
  • UPDATE OF不能指定不存在的字段; 发出SQLCODE -400错误。 UPDATE OF不能指定重复的字段名; 发出SQLCODE -58错误。

以下是事件类型的示例:

代码语言：javascript复制
CREATE TRIGGER TrigBI BEFORE INSERT ON Sample.Person
       INSERT INTO TLog (Text) VALUES ('before insert')
代码语言：javascript复制
CREATE TRIGGER TrigAU AFTER UPDATE ON Sample.Person
       INSERT INTO TLog (Text) VALUES ('after update')
代码语言：javascript复制
CREATE TRIGGER TrigBUOF BEFORE UPDATE OF Home_Street,Home_City,Home_State ON Sample.Person
       INSERT INTO TLog (Text) VALUES ('before address update')
代码语言：javascript复制
CREATE TRIGGER TrigAD AFTER UPDATE,DELETE ON Sample.Person
       INSERT INTO TLog (Text) VALUES ('after update or delete')

ORDER

ORDER子句确定同一表具有相同时间和事件的多个触发器时触发器的执行顺序。例如，两个AFTER DELETE触发器。首先执行具有最低阶整数的触发器，然后执行下一个更高的整数，依此类推。如果未指定ORDER子句，则使用分配的order 0(零)创建触发器。因此，不带ORDER子句的触发器总是在带ORDER子句的触发器之前执行。

可以将相同的订单值分配给多个触发器。还可以创建多个顺序为0(隐式或显式)的触发器。具有相同时间、事件和顺序的多个触发器以随机顺序一起执行。

触发器按以下顺序执行：time > order > event。因此，如果有BEFORE INSERT触发器和BEFORE INSERT，UPDATE触发器，则将首先执行顺序值最低的触发器。如果具有相同顺序值的BEFORE INSERT触发器和BEFORE INSERT，UPDATE触发器，则INSERT将在INSERT，UPDATE。这是因为-时间和顺序相同-单事件触发器总是在多事件触发器之前执行。如果两个(或多个)触发器具有相同的时间、顺序和事件值，则执行顺序是随机的。

下面的示例展示了ORDER号的工作方式。 所有这些CREATE TRIGGER语句都创建由同一个事件执行的触发器:

代码语言：javascript复制
CREATE TRIGGER TrigA BEFORE DELETE ON doctable
       INSERT INTO TLog (Text) VALUES ('doc deleted')
  /* Assigned ORDER=0 */
代码语言：javascript复制
CREATE TRIGGER TrigB BEFORE DELETE ORDER 4 ON doctable
       INSERT INTO TReport (Text) VALUES ('doc deleted')
  /* Specified as ORDER=4 */
代码语言：javascript复制
CREATE TRIGGER TrigC BEFORE DELETE ORDER 2 ON doctable
       INSERT INTO Ttemps (Text) VALUES ('doc deleted')
  /* Specified as ORDER=2 */
代码语言：javascript复制
CREATE TRIGGER TrigD BEFORE DELETE ON doctable
       INSERT INTO Tflags (Text) VALUES ('doc deleted')
  /* Also assigned ORDER=0 */

这些触发器将按照以下顺序执行:(TrigA, TrigD)， TrigC, TrigB。 注意，TrigA和TrigD有相同的序号，因此以随机顺序执行。

REFERENCING

REFERENCING子句可以为行的旧值和/或新值指定别名。旧值是UPDATE或DELETE触发器触发操作之前的行值。新值是UPDATE或INSERT触发器的触发操作之后的行值。对于UPDATE触发器，可以为BEFORE和AFTER行值指定别名，如下所示：

代码语言：javascript复制
REFERENCING OLD ROW AS oldalias NEW ROW AS newalias

关键字ROW和AS是可选的。因此，同样的条款也可以指定为：

代码语言：javascript复制
REFERENCING OLD oldalias NEW newalias

在INSERT之前引用旧值或在DELETE之后引用新值是没有意义的。尝试这样做会导致编译时出现SQLCODE-48错误。

只有当操作程序代码为SQL时，才能使用REFERENCING子句。 使用LANGUAGE OBJECTSCRIPT子句指定references子句将导致SQLCODE -49错误。

下面是一个使用REFERENCING 和INSERT的例子:

代码语言：javascript复制
CREATE TRIGGER TrigA AFTER INSERT ON doctable
      REFERENCING NEW ROW AS new_row
BEGIN
      INSERT INTO Log_Table VALUES ('INSERT into doctable');
      INSERT INTO New_Log_Table VALUES ('INSERT into doctable',new_row.ID);
END

action

触发动作由以下元素组成:

  • 每个子句都是可选的。 可用的值为FOR EACH ROW、FOR EACH ROW_AND_OBJECT和FOR EACH STATEMENT。 默认值是FOR EACH ROW:
    • FOR EACH ROW—该触发器由触发语句影响的每一行触发。 注意，TSQL不支持行级触发器。
    • FOR EACH ROW_AND_OBJECT—该触发器由触发语句影响的每一行或通过对象访问进行的更改触发。 注意，TSQL不支持行级触发器。这个选项定义了一个统一触发器，之所以这么叫，是因为它是由通过SQL或对象访问发生的数据更改触发的。 (相比之下，与其他触发器相比，如果您希望在通过对象访问发生更改时使用相同的逻辑，则需要实现回调，如%OnDelete()。)
    • FOR EACH STATEMENT—该触发器对整个语句触发一次。 ObjectScript和TSQL触发器都支持语句级触发器。

可以使用INFORMATION.SCHEMA.TRIGGERS的ACTIONORIENTATION属性列出每个触发器的FOR EACH值。

一个可选的WHEN子句，由WHEN关键字和括在括号中的谓词条件(简单或复杂)组成。 如果谓词条件的计算结果为TRUE，则执行触发器。 当语言为SQL时，才能使用WHEN子句。 WHEN子句可以引用oldalias或newalias值。

一个可选的LANGUAGE子句，可以是LANGUAGE SQL或LANGUAGE OBJECTSCRIPT。 默认为LANGUAGE SQL。

在触发器执行时执行的用户编写的代码。

编程算法 云推荐引擎 sql

0 人点赞