Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 (中文) » SQL Anywhere 服务器 - SQL 参考 » 使用 SQL » SQL 语句 » SQL 语句 (A-D)

 

CREATE TRIGGER 语句

此语句用于在表中创建触发器。

语法
CREATE [ OR REPLACE ] TRIGGER trigger-name trigger-type
{ trigger-event-list | UPDATE OF column-list }
[ ORDER integer ] ON table-name
[ REFERENCING [ OLD AS old-name ]
   [ NEW AS new-name ] 
   [ REMOTE AS remote-name ] ]
[ FOR EACH { ROW | STATEMENT } ]
[ WHEN ( search-condition ) ]
trigger-body
column-list :  column-name[, ...]
trigger-type : 
BEFORE 
| AFTER 
| INSTEAD OF 
| RESOLVE
trigger-event-list : trigger-event[, ...  ]
trigger-event : 
DELETE 
| INSERT 
| UPDATE

trigger-body :BEGIN 语句。请参见BEGIN 语句

参数
  • OR REPLACE 子句   指定 OR REPLACE (CREATE OR REPLACE TRIGGER) 将创建一个新触发器或替换同名的现有触发器。

  • 触发器事件   触发器可以由以下事件触发。您可以为 DELETE、INSERT 或 UPDATE 事件定义多个触发器,或为 UPDATE OF column-list 事件定义一个触发器:

    • DELETE   每当删除关联表中的行时激活。

    • INSERT   每当有新行插入到与触发器关联的表中时激活。

    • UPDATE   每当更新关联表中的行时激活。

    • UPDATE OF column-list   每当更新关联表中的行和修改 column-list 中的列时就被调用。这种类型的触发器事件无法在 trigger-event-list 中使用;它必须是为触发器定义的唯一的触发器事件。此子句不能在 INSTEAD OF 触发器中使用。

      您可以为需要处理的每个事件分别编写触发器,如果您有一些共享操作和一些取决于事件的操作,也可以为所有事件创建一个触发器并使用 IF 语句辨别所发生的操作。有关触发操作的详细信息,请参见触发器操作条件

  • trigger-type   可以将行级触发器定义为在插入、更新或删除操作之前 (BEFORE)、之后 (AFTER) 执行,或替代 (INSTEAD OF) 其中一个操作执行。可以将语句级触发器定义为替代 (INSTEAD OF) 语句执行或在语句之后 (AFTER) 执行。

    只要行上发生更新 (UPDATE),BEFORE UPDATE 触发器就会触发,而无论新值是否与旧值不同。也就是说,如果已为 BEFORE UPDATE 触发器指定了 column-list,则 column-list 中的任何列出现在 UPDATE 语句的 SET 子句中都会导致该触发器触发。如果已为 AFTER UPDATE 触发器指定了 column-list,则只有在 column-list 中任意列的值被 UPDATE 语句更改 时触发器才触发。

    INSTEAD OF 触发器是可在常规视图上定义的唯一触发器形式。INSTEAD OF 触发器会用其它操作替代触发操作。当 INSTEAD OF 触发器触发时,会跳过触发操作而执行指定的操作。INSTEAD OF 触发器可在行级或语句级定义。语句级 INSTEAD OF 触发器会替换整个语句,包括全部行级操作。如果语句级 INSTEAD OF 触发器触发,该语句不会导致任何行级触发器的触发。但是,语句级触发器的主体可能会执行其它操作,从而导致其它行级触发器的触发。

    如果正在定义 INSTEAD OF 触发器,就无法使用 UPDATE OF column-list 子句、ORDER 子句或 WHEN 子句。

    有关 INSTEAD OF 触发器的功能和限制的详细信息,请参见INSTEAD OF 触发器

    RESOLVE 触发器类型适于和 SQL Remote 一同使用:它仅在行级 UPDATE 或 UPDATE OF column-list 前触发。

  • FOR EACH 子句   要将触发器声明为行级触发器,请使用 FOR EACH ROW 子句。要将触发器声明为语句级触发器,可以使用 FOR EACH STATEMENT 子句或忽略 FOR EACH 子句。为保证清晰,建议在声明语句级触发器时指定 FOR EACH STATEMENT 子句。

  • ORDER 子句   定义同时触发(之前、之后或解析)的相同类型的附加触发器(插入、更新或删除)时,必须指定一个 ORDER 子句来告知数据库服务器触发器触发的顺序。配置为在同时触发的相同类型触发器之间,顺序编号必须是唯一的。如果指定的顺序编号不是唯一的,则会返回错误。顺序编号不需要连续(例如可以指定 1、12、30)。数据库服务器从最低编号开始触发触发器。

    如果忽略 ORDER 子句或指定 0,数据库服务器会指派顺序 1。但是,如果另一相同类型触发器已设置为 1,则会返回错误。

    添加附加触发器时,可能需要修改事件的现有相同类型触发器,这取决于触发器的操作是否进行交互。如果它们不进行交互,新触发器的 ORDER 值必须高于现有触发器。如果它们进行交互,则需要考虑其它触发器,可能需要更改触发器触发的顺序。

    ORDER 子句不能在 INSTEAD OF 触发器中使用,因为在表或视图上只能定义每种类型(插入、更新或删除)的一个 INSTEAD OF 触发器。

  • REFERENCING 子句   REFERENCING OLD 和 REFERENCING NEW 子句允许您引用已插入、已删除或已更新的行。对于此子句而言,UPDATE 被视为删除后插入。

    INSERT 会使用 REFERENCING NEW 子句,该子句代表已插入的行。但不使用 REFERENCING OLD 子句。

    DELETE 会使用 REFERENCING OLD 子句,该子句代表已删除的行。但不使用 REFERENCING NEW 子句。

    UPDATE 用到 REFERENCING OLD 子句,代表更新前的行;并用到 REFERENCING NEW 子句,代表更新后的行。

    REFERENCING OLD 和 REFERENCING NEW 的含义因时而异,具体取决于触发器是行级还是语句级触发器。对于行级触发器,REFERENCING OLD 子句允许引用更新或删除之前行中的值,REFERENCING NEW 子句允许引用已插入或更新的值。在 BEFORE 和 AFTER 触发器中可以引用 OLD 和 NEW 行。REFERENCING NEW 子句允许在插入或更新操作发生之前在 BEFORE 触发器中修改新行。

    对于语句级触发器,REFERENCING OLD 和 REFERENCING NEW 子句引用保存行的新旧值的已声明临时表。这些表的缺省名是 deleted 和 inserted。

    REFERENCING REMOTE 子句适于和 SQL Remote 一同使用。它允许引用 UPDATE 语句的 VERIFY 子句中的值。它只能和 RESOLVE UPDATE 或 RESOLVE UPDATE OF 列列表触发器一同使用。

  • WHEN 子句   仅对搜索条件求值结果为 true 的行触发触发器。WHEN 子句仅能用于行级触发器。此子句不能在 INSTEAD OF 触发器中使用。

  • 触发器主体   触发器主体包含触发操作发生时要执行的操作,由一个 BEGIN 子句组成。请参见BEGIN 语句

    可以在 BEGIN 语句中包含触发器操作条件。触发器操作条件将根据导致触发器触发的触发器事件来完成操作。例如,如果触发器定义为针对更新和删除都触发,则可以为这两种情况指定不同的操作。有关触发器操作条件(包括示例)的详细信息,请参见触发器操作条件

注释

CREATE TRIGGER 语句创建与数据库中的表关联的触发器,并在数据库中存储触发器。

您不能在实例化视图上定义触发器。如果这样做,将返回 SQLE_INVALID_TRIGGER_MATVIEW 错误。

触发器声明为行级触发器(此情况下,它在每行修改之前或之后执行)或语句级触发器(此情况下,它在整个触发器语句完成后执行)。

权限

必须具有 RESOURCE 权限以及对表的 ALTER 权限,或者必须是表的所有者或具有 DBA 权限。CREATE TRIGGER 将对表进行表锁定,这样就需要表的独占使用权。

副作用

自动提交。

另请参见
标准和兼容性
  • SQL/2003   持久存储模块特性。一些子句是服务商扩充。

示例

此示例将创建一个语句级触发器。您必须先创建一个表:

CREATE TABLE t0
( id integer NOT NULL,
 times timestamp NULL DEFAULT current timestamp,
 remarks text NULL,
 PRIMARY KEY ( id )
);

接下来,为此表创建一个语句级触发器:

CREATE TRIGGER myTrig AFTER INSERT ORDER 4 ON t0
REFERENCING NEW AS new_name
FOR EACH STATEMENT
BEGIN
  DECLARE @id1 INTEGER;
  DECLARE @times1 TIMESTAMP;
  DECLARE @remarks1 LONG VARCHAR;
  DECLARE @err_notfound EXCEPTION FOR SQLSTATE VALUE '02000';
//declare a cursor for table new_name
  DECLARE new1 CURSOR FOR
   SELECT id, times, remarks FROM new_name;
  OPEN new1;
 //Open the cursor, and get the value
  LoopGetRow:
  LOOP
      FETCH NEXT new1 INTO @id1, @times1,@remarks1;
      IF SQLSTATE = @err_notfound THEN
   LEAVE LoopGetRow
      END IF;
      //print the value or for other use
      PRINT (@remarks1);
  END LOOP LoopGetRow;
  CLOSE new1
END;

以下示例将替换在上一个示例中创建的 myTrig 触发器。

CREATE OR REPLACE TRIGGER myTrig AFTER INSERT ORDER 4 ON t0
REFERENCING NEW AS new_name
FOR EACH STATEMENT
BEGIN
FOR L1 AS new1 CURSOR FOR
   SELECT id, times, remarks FROM new_name
DO
     //print the value or for other use
     PRINT (@remarks1);
END FOR;
END;

下一个示例说明如何在 BEFORE UPDATE 触发器中使用 REFERENCING NEW。此示例确保新 Employees 表中的邮政编码均为大写形式:

CREATE TRIGGER emp_upper_postal_code
BEFORE UPDATE OF PostalCode
ON Employees
REFERENCING NEW AS new_emp
FOR EACH ROW
WHEN ( ISNUMERIC( new_emp.PostalCode ) = 0 )
BEGIN
   -- Ensure postal code is uppercase (employee might be 
   -- in Canada where postal codes contain letters)
   SET new_emp.PostalCode = UPPER(new_emp.PostalCode)
END;

UPDATE Employees SET state='ON', PostalCode='n2x 4y7' WHERE EmployeeID=191;
SELECT PostalCode FROM Employees WHERE EmployeeID = 191;

下一个示例说明如何在 BEFORE DELETE 触发器中使用 REFERENCING OLD。此示例可防止从 Employees 表中删除尚未离职的雇员。

CREATE TRIGGER TR_check_delete_employee 
BEFORE DELETE
ON Employees
REFERENCING OLD AS current_employees
FOR EACH ROW /* WHEN( search_condition ) */
BEGIN
   IF current_employees.TerminationDate IS NULL THEN
    RAISERROR 30001 'You cannot delete an employee who has not been fired';
    END IF;
END;

下一个示例说明如何在 BEFORE UPDATE 触发器中使用 REFERENCING NEW 和 REFERENCING OLD。此示例可防止降低雇员薪水。

CREATE TRIGGER TR_check_salary_decrease 
    BEFORE UPDATE
      ON Employees
    REFERENCING OLD AS before_update 
    NEW AS after_update 
FOR EACH ROW 
BEGIN
   IF after_update.salary < before_update.salary THEN
    RAISERROR 30002 'You cannot decrease a salary';
    END IF;
END;

下一个示例说明如何在 BEFORE UPDATE 触发器中使用 REFERENCING NEW 和 REFERENCING OLD。此示例也可以禁止降低雇员薪水,但是此触发器更有效,因为它只在更新薪水列时才触发。

CREATE TRIGGER TR_check_salary_decrease_column 
    BEFORE UPDATE OF Salary
      ON Employees
    REFERENCING OLD AS before_update 
    NEW AS after_update
FOR EACH ROW /* WHEN( search_condition ) */
BEGIN
   IF after_update.salary < before_update.salary THEN
    RAISERROR 30002 'You cannot decrease a salary';
End IF;
END;

下一个示例说明如何在 BEFORE INSERT 和 UPDATE 触发器中使用 REFERENCING NEW。以下示例会创建一个触发器,此触发器将在 SalesOrderItems 表中的行插入或更新前触发。

CREATE TRIGGER TR_update_date 
   BEFORE INSERT, UPDATE
     ON SalesOrderItems
   REFERENCING NEW AS new_row
FOR EACH ROW 
BEGIN
   SET new_row.ShipDate = CURRENT TIMESTAMP;
END;