db2数据库insert触发器

转载

deanyuancn 2024-12-24 16:56:27

文章标签 db2数据库insert触发器触发器数据库 python mysql 文章分类 数据仓库大数据

总览

对于使用SQL触发器来帮助管理业务的客户，有一个新的IBM DB2 for i增强功能，可以更轻松地管理和维护这些触发器。 IBM i 7.1 Technology Refresh 6提供了对多个事件触发器的支持，这允许单个SQL触发器处理触发器事件（INSERT，UPDATE和DELETE）的组合。有了新的支持，现在就可以只需要更新一个源SQL触发器，以适应数据库和业务需求的将来更改。

该支持引入了触发器谓词和新关键字，这些关键字可用于基于导致触发触发器的事件来控制触发器中的逻辑流。新谓词“插入”，“更新”和“删除”是不言自明的，并且涉及导致触发触发器的事件。 CREATE TRIGGER语句中还添加了新SQL语法，以允许为触发器定义多个事件。该语法允许指定触发器可以处理的事件。

要使用SQL多事件触发器支持，需要在系统上应用DB2 PTF组SF99701级别23（请参阅SF99701级别22增强功能）。

多个事件触发示例

以下是一些示例，这些示例显示了触发谓词的用法以及CREATE TRIGGER语句的新语法。

在第一个示例中，假设您有一个名为employee的表来跟踪员工信息。该表包含两列，等等，名为EDLEVEL和薪水。另外，假设该表当前有两个为其定义的BEFORE触发器程序，一个用于INSERT，另一个用于UPDATE，可以根据edlevel调整或设置薪水列。

这是INSERT之前的触发器：

清单1. INSERT之前的触发器触发器insert_set_salary

CREATE OR REPLACE TRIGGER insert_set_salary
  NO CASCADE
  BEFORE INSERT ON employee
  REFERENCING NEW AS N FOR EACH ROW
  -- Give starting salary based on education level.
  BEGIN
    SET n.salary = CASE n.edlevel WHEN 18 THEN 50000
                                  WHEN 16 THEN 40000
                                  ELSE 25000
    END;
  END

这是BEFORE UPDATE触发器：

清单2. UPDATE之前的触发器update_set_salary

CREATE OR REPLACE TRIGGER update_set_salary
  NO CASCADE
  BEFORE UPDATE ON employee
REFERENCING NEW AS n OLD AS o  
  FOR EACH ROW
  WHEN( n.edlevel > o.edlevel )
  -- Give 10% raise to existing employees with new education level.
  BEGIN
    SET N.SALARY = N.SALARY * 1.1;
  END

要将这些触发器替换为针对雇员表的UPDATE或INSERT语句而触发的单个SQL触发器，请使用触发器主体中的触发器谓词以及CREATE TRIGGER语句上的新语法来指示要进行多个事件在触发器中处理。

为了指示触发器中要处理的事件，CREATE TRIGGER语句语法允许指定触发器处理的事件。这些事件由OR关键字分隔。在多事件触发器（如清单3所示）中，将SQL触发器定义为在更新和插入事件之前都触发。

多事件触发器如下所示。请注意，在多事件触发器的第一个示例中，新语法以粗体显示。

清单3.多个事件触发器set_salary

CREATE OR REPLACE TRIGGER SET_SALARY
  NO CASCADE
  BEFORE UPDATE OR INSERT ON employee
  REFERENCING NEW AS n OLD AS o
  FOR EACH ROW
  WHEN( o.edlevel IS NULL OR n.edlevel > o.edlevel )
  BEGIN
    -- Give 10% raise to existing employees with new education level.
    IF UPDATING THEN SET n.salary = n.salary * 1.1;
    -- Give starting salary based on education level.
    ELSEIF INSERTING THEN
      SET n.salary = CASE n.edlevel WHEN 18 THEN 50000
                                    WHEN 16 THEN 40000
                                    ELSE 25000
      END;
    END IF;
  END

在此示例中，请注意，存在传递给触发器的转换变量不相关的情况（INSERT语句的旧行图像，因为没有“旧”行）。对于在过渡变量中传递的行图像不相关的情况（另一种情况是DELETE的“新”行），不相关行中的每个过渡变量都包含NULL值。

这是另一个示例，其中所有三个事件（INSERT，UPDATE和DELETE）都在单个SQL触发器中处理。

表company_stats中的prod_count列包含该公司生产的不同产品的当前数量。在以下示例中，多事件触发器：

每次开发新产品时，增加产品数量。
每次公司停止销售产品时，都会减少产品数量。
当产品价格的变化（增加或减少）超过10％时，将返回错误。

清单4.多事件触发器prod_maintain

CREATE TRIGGER prod_maintain
AFTER INSERT OR DELETE OR UPDATE OF prod_price ON PRODUCTS
 REFERENCING NEW AS n OLD AS o FOR EACH ROW
 BEGIN
    IF INSERTING
THEN UPDATE products SET prod_count = prod_count + 1;
 END IF;
 IF DELETING
 THEN UPDATE products SET prod_count = prod_count - 1;
 END IF;
    IF UPDATING AND
       (n.prod_price > 1.1 * o.prod_price OR
        n.prod_price < 0.9 * o. prod_price)
THEN SIGNAL SQLSTATE '75000'
 SET MESSAGE_TEXT = 'Price change > 10%, attention!';
 END IF;
  END

对于此示例，将为触发器程序处理的每个事件运行不同的逻辑。对于INSERT和DELETE，表产品中的prod_count会进行相应的更新。对于UPDATE，如果价格变化超过阈值，触发器将发出错误条件信号。

关于触发器谓词（INSERTING，DELETING，UPDATING）的一件事要注意的是，它们可以在控制语句（类似于前面的示例）中使用，也可以在可以指定谓词（例如SELECT或UPDATE）的任何SQL语句中使用，提供包含谓词的语句在SQL触发器内。在下一个示例中，在INSERT语句中使用触发事件谓词为product_history表生成一行。

清单5.表product_history

CREATE TABLE product_history(prod_id INT,
                             posttime TIMESTAMP,
                             change_type CHAR(1))

清单6.多个事件触发器set_prod_hist

CREATE TRIGGER set_prod_hist
  AFTER INSERT OR UPDATE OR DELETE ON products
  REFERENCING NEW AS n OLD AS o
  FOR EACH ROW MODE DB2SQL
  BEGIN
    INSERT INTO product_history VALUES (
      CASE
         WHEN INSERTING OR UPDATING
           THEN n.prod_id
         WHEN DELETING
           THEN o.prod_id
      END,
      CURRENT TIMESTAMP,
      CASE
        WHEN INSERTING
          THEN 'I'
        WHEN UPDATING
          THEN 'U'
        WHEN DELETING
          THEN 'D'
      END
    );
  END

对于此触发，所有三个触发事件都使用相同的常规逻辑。触发事件谓词主要用于确定是否需要从上一行或下一行的值中读取产品ID，以及第二次设置更新类型。

下一个示例显示了杂货店如何使用SQL触发器来跟踪当商店出售的产品发生变化时，价格查询（PLU）代码跟踪表的添加，删除和更改（PLU是（位于杂货店单独出售的水果和蔬菜的小标签上）。在此简单示例中，将根据触发触发器的事件调用不同的过程。处理这些事件的业务逻辑在各自的过程中。

清单7.多事件触发器plu_track

CREATE TRIGGER plu_track
BEFORE INSERT OR UPDATE OR DELETE ON prod_plu
  REFERENCING NEW AS n OLD AS o FOR EACH ROW
  BEGIN
    IF INSERTING THEN
      CALL prod_plu_ins(n.plu);
    ELSEIF DELETING THEN
      CALL prod_plu_del(o.plu);
    ELSE
      CALL prod_plu_upd(n.plu);
    END IF;
  END

确定触发类型

qsys2.systriggers目录视图已更改，以反映新的多事件触发器支持。如果触发器是多事件触发器，则现有列event_manipulation包含新值'MULTI'。另外，如果使用相应的触发事件谓词创建了触发程序，则三个新的CHAR（1）列eventinsert ， eventupdate和eventdelete包含一个“ Y”。

当安装了DB2 for i PTF组SF99701级别23时， qsys2.systriggers目录视图将具有下表中所述的内容：

表1.触发程序的systriggers值

触发器的事件子句中指定的操作	EVENT_MANIPULATION列的值	EVENTINSERT列的值	EVENTUPDATE列的值	EVENTDELETE列的值
插	插	ÿ	ñ	ñ
更新	更新	ñ	ÿ	ñ
删除	删除	ñ	ñ	ÿ
插入，更新	多	ÿ	ÿ	ñ
插入，删除	多	ÿ	ñ	ÿ
更新，删除	多	ñ	ÿ	ÿ
插入，更新，删除	多	ÿ	ÿ	ÿ

在上面的set_salary触发器示例中，当创建两个单个事件触发器时，每个触发器的单独记录将写入qsys2.systriggers目录视图。

图1. qsys2.systriggers目录视图中的单个事件触发器

当将两个单个事件触发器组合为一个多个事件触发器时，仅一个记录被写入qsys2.systriggers目录视图。

图2. qsys2.systriggers目录视图中的多个事件触发器

与本机I / O和现有CL命令共存

正如您对IBM i上的集成数据库所期望的那样，现有的控制语言（CL）命令可以与为SQL多事件触发器创建的程序对象一起使用。使用这些程序时，这些命令具有常识性行为。例如，删除物理文件触发器（RMVPFTRG）命令可用于从文件中删除多事件触发器程序，但必须删除该触发器程序处理的所有事件。因此，删除此类触发程序时必须使用TRGEVENT（* ALL）参数。

多个事件触发器的注意事项

尽管触发器程序可以处理多个事件，但必须将所有事件定义为同时触发。对于所有事件，必须将SQL触发器定义为“之前”或“之后”。

作为此支持的一部分，删除了与松散相关的触发器程序限制。在获得此支持之前，SQL BEFORE触发器不能包含执行任何可落实工作SQL语句，例如INSERT，CREATE TABLE等。有了这个新的支持，就消除了此限制，并且before触发器程序可以包含这些语句。

如果没有新的支持，尝试创建包含这些语句之一SQL BEFORE触发器将导致在CREATE TRIGGER语句上返回42987SQLSTATE。

另外，请注意，在此新支持之前，还有一个QAQQINI选项SQL_MODIFIES_SQL_DATA，该选项允许触发器包含SQL语句来执行可提交的工作。由于新支持取消了此选项提供解决方法的限制，因此将QAQQINI选项设置为'* NO'时将被忽略但可以接受，因为它不再相关。

新旧转换变量总是传递给多事件触发器，但是对于某些操作，它们不相关，因此包含空值。例如，在set_salary触发器中，表示旧行“ O”的转换变量在INSERTING操作中将为NULL。

新的触发器谓词只能在SQL触发器中使用，并且不能在SQL函数或过程或触发器之外的任何其他SQL语句中引用。但是，该触发程序不必是多个事件触发程序即可使用谓词。它可以是仅处理单个事件的触发器。

摘要

多个事件触发器提供了一种方法，使单个SQL源触发器可以处理表或视图的多个事件。该支持旨在使使用触发器来管理和维护这些触发器的IT部门更加容易。另外，对触发器的增强SQL支持的语法和语义与其他DB2平台上的支持相匹配，以提供跨数据库的兼容性和可移植性。从IBM i 7.1 TR6发行版开始提供多事件SQL触发器支持。因此，如果它非常适合您的DB2 for i数据库环境，请尝试一下。