db2关于trigger的常用操作!

myfriend2010

这几天pub上有人问到db2的关于trigger的操作的问题，抽时间整理了一下！


db2支持表级DML的tirgger！也就是增删改的trigger！这边分别对insert和update做了2个例子



首先看2个表的DDL
describe table  DB2ADMIN.TEST;

列                             类型      类型
名称                           模式      名称               长度     小数位  NULL
------------------------------ --------- ------------------ -------- ----- ------
ID                             SYSIBM    INTEGER                   4     0 是   
NAME                           SYSIBM    VARCHAR                 100     0 是   
AGE                            SYSIBM    INTEGER                   4     0 是   

  3 条记录已选择。



describe table  db2admin.aaaa;

Statement processed successfully in 1.43 secs.

describe table  db2admin.aaaa;
completed successfully.

列                             类型      类型
名称                           模式      名称               长度     小数位  NULL
------------------------------ --------- ------------------ -------- ----- ------
ID                             SYSIBM    INTEGER                   4     0 是   
TNAME                          SYSIBM    VARCHAR                 100     0 是   
EVENT                          SYSIBM    INTEGER                   4     0 是   

  3 条记录已选择。


一.对于trigger的insert
drop TRIGGER DB2ADMIN.ZXT_TEST_INSERT;


SET SCHEMA DB2ADMIN     ;

SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","CCP";

CREATE TRIGGER DB2ADMIN.ZXT_TEST_INSERT
  AFTER  INSERT
  ON DB2ADMIN.TEST
  FOR EACH ROW
  MODE DB2SQL
  BEGIN ATOMIC
  declare v_count integer;
  if exists(select t.tname from  db2admin.aaaa  t where t.tname = 'TEST')
  then
  update  db2admin.aaaa set event=2 where tname = 'TEST';
  else
  select count(1)+1  from db2admin.aaaa t;
  insert into db2admin.aaaa values(value(v_count,1),'TEST',1);
  end if;
  end;
commit;


db2 => alter table  DB2ADMIN.TEST activate not logged initially with empty table
DB20000I  SQL 命令成功完成。
db2 => alter table  db2admin.aaaa activate not logged initially with empty table
DB20000I  SQL 命令成功完成。
db2 => select * from DB2ADMIN.TEST

ID          NAME                  AGE

----------- --------------------- -------

  0 条记录已选择。

db2 => select * from db2admin.aaaa

ID          TNAME                   EVENT

----------- ----------------------- -------

  0 条记录已选择。

db2 => insert into DB2ADMIN.TEST values (01,'ZXT',25)
DB20000I  SQL 命令成功完成。
db2 => commit
DB20000I  SQL 命令成功完成。
db2 => select * from DB2ADMIN.TEST

ID          NAME                  AGE

----------- --------------------- -------
          1 ZXT                   25

  1 条记录已选择。

db2 => select * from db2admin.aaaa

ID          TNAME                   EVENT

----------- ----------------------- -------
          1 TEST                    1

  1 条记录已选择。

db2 => insert into DB2ADMIN.TEST values (02,'XDL',23)
DB20000I  SQL 命令成功完成。
db2 => select * from DB2ADMIN.TEST

ID          NAME                  AGE

----------- --------------------- -------
          1 ZXT                   25
          2 XDL                   23

  2 条记录已选择。

db2 => select * from db2admin.aaaa

ID          TNAME                   EVENT

----------- ----------------------- -------
          1 TEST                    2

  1 条记录已选择。

二.关于trigger的update操作
CREATE TRIGGER DB2ADMIN.ZXT_TEST_UPDATE
  AFTER update
  ON DB2ADMIN.TEST
  REFERENCING OLD AS o NEW AS n
  FOR EACH ROW
  MODE DB2SQL
  BEGIN ATOMIC
    declare v_count integer;
    if(o.name =n.name) then
           insert into db2admin.aaaa values(1000,n.name,1);
    elseif(o.age=n.age) then
      insert into db2admin.aaaa
        values(1001,char(integer(n.age)),1);
    end if;
  end;


db2 => connect to dw

   数据库连接信息

数据库服务器         = DB2/NT 8.2.0
SQL 授权标识         = DB2ADMIN
本地数据库别名       = DW

db2 => select * from DB2ADMIN.TEST

ID          NAME                  AGE

----------- --------------------- -------
1           ZXT                   25
2           XDL                   23

  2 条记录已选择。

db2 =>   select * from db2admin.aaaa

ID          TNAME                   EVENT

----------- ----------------------- -------
1           TEST                    2

  1 条记录已选择。

db2 =>   update DB2ADMIN.TEST set name='ZXT1' where id =1
DB20000I  SQL 命令成功完成。
db2 =>   select * from DB2ADMIN.TEST

ID          NAME                     AGE

----------- ------------------------ -------
1           ZXT1                     25
2           XDL                      23

  2 条记录已选择。

db2 =>   select * from db2admin.aaaa

ID          TNAME                    EVENT

----------- ------------------------ -------
1001        25                       1
1           TEST                     2

  2 条记录已选择。

db2 =>   update DB2ADMIN.TEST set AGE=23 where id =2
DB20000I  SQL 命令成功完成。
db2 =>   select * from DB2ADMIN.TEST

ID          NAME                     AGE

----------- ------------------------ -------
1           ZXT1                     25
2           XDL                      23

  2 条记录已选择。

db2 =>   select * from db2admin.aaaa

ID          TNAME                     EVENT

----------- ------------------------- -------
1001        25                        1
1000        XDL                       1
1           TEST                      2

  3 条记录已选择。

db2 =>
db2 =>

myfriend2010

http://space.itpub.net/694276/viewspace-202028

myfriend2010

关于db2触发器的一些概念：

  DB2定义了一组操作，当被定义的事件出现时，将执行该组操作，触发事件可能是下边的sql：insert、update、delete。
   可以在一个指定的表上定义触发器，并且一旦定义触发器将自动激活。

  触发器介绍：
  
   当定义一个指定的SQL操作（如insert、update、delete）作用于某张表时，一个定义了一组操作的触发器就可以被激活。触发器并不像参照完整性约束和检查约束那样，我们甚至可以使用它来对其他表来对其他表进行更新，更简单的说，触发器是基于表创建的一种特殊的 存储过程，针对标的某些操作而完成某种特定的功能。

   触发器的作用：

   我们可以通过触发器完成许多功能
    确保新变化的数据在我们制定的要求范围内；
    将写入的数据变成我们预期的形式或者指定的格式；
    保证数据的完整性，通过触发器调整父表与子表的数据关系；

    触发器的激活：

    触发器能够定义成按照以下两种方式之一的激活

    前触发器：（before trigger） 在引发触发器的sql语句执行之前，对受语句影响的每一行都触发一次，因此，在新数据插入到表之前或表中数据被更新之前，触发器将检查这些新数据
    后触发器：（after trigger） 可对受语句影响的每一行都触发一次，或者在语句成功完成以后执行一次触发操作，这取决于所定义的粒度。因此，触发器在相容的状态下（所有的事务都已完成）察看该表
   触发器的另一个特点是，初发期可以触发其他的触发器（或它本身）或者约束，这称作级连触发器。

diablo2

再详细点，完备点

可以精华了

myfriend2010

恭喜！您刚拣到ITPUB送出的新年红包。25PUB币!

如果您的浏览器没有自动跳转，请点击这里

myfriend2010

恭喜！您刚拣到ITPUB送出的新年红包。40PUB币!

如果您的浏览器没有自动跳转，请点击这里

myfriend2010

算了,俺不要精华了!2个红包已经够了!

wangzhonnew

俺来加两句comment。
估计很多同学们都会认为，所谓trigger就是一段程序存在某个地方，然后当db2判断符合条件的时候，如果是before trigger，那么就在执行之前调用一下，如果是after trigger，就在语句调用完成之后call一下，是不是？　
所以以前见过有同学问过：如果insert成功但是trigger部分出错怎样处理呢？

实际上，上面的理解本身就是不正确的　：）
db2实际上在QRW阶段就会把trigger加入到语句里面。从db2exfmt我们可以看到，用刚才myfriend的例子建立的表与trigger，用下面的语句插入数据：
insert into test values (2,'2',2)

猜一猜，access plan和rewritten query是什么样的？

Original Statement:
------------------
insert into test values (2,'2',2)


Optimized Statement:
-------------------
$WITH CONTEXT$($TRIGGER$(TAOEWANG.ZXT_TEST_INSERT))
INSERT INTO TAOEWANG.TEST AS Q3
   SELECT 2, '2', 2
   FROM (VALUES 1) AS Q1

可以看出trigger是在语句被改写时就被引用的，然后当db2生成访问计划的时候会把trigger里面的东西一起加入insert，而不是作为一个单独的程序段来运行：


嘿嘿，一个简简单单的小insert变成了那么大的access plan tree……
为什么我们要提这个问题呢？以前见过一个case，用户抱怨说对于一个简单的insert竟然报说stmtheap不够，并且要花很长的时间去compile。
然后俺一看好家伙，四五个trigger外加无数的FK……
要知道一个复杂的trigger可能涉及很多逻辑，这样产生出来的访问计划可能会比较大。如果一个复杂的语句（比如用了复杂的view），同时用到了很多trigger什么的，计算access plan可能会花很长时间
所以说不能认为增加一个trigger，sql编译器的开销就是计算那个trigger本身，而是要把所有的trigger都带入相应的语句然后计算，这样生成访问计划的开销也许就是成倍数甚至指数级增长了……

myfriend2010

我感觉trigger和DML是在同一个事务中进行的,也就是说DML如果失败会导致trigger也失败,同样trigger失败,DML也回回滚的,至少oracle中是这样的.


不过在oracle中也提到了自制事务,也就是trigger和DML不在同一个事务处理的概念,狼来说说DB2中有没有!如果DB2中也存在自制事物的话,那么执行计划应该又会发生变化...

wangzhonnew

what is 自制事务?