基于行和语句的触发器问题

本文介绍了基于行和语句的触发器问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

大家好， 这个问题与iSeries V5R4和db2有关。 我想实现一个AFTER DELETE触发器来保存已删除的行 到存档表，我最初将其定义为FOR EACH STATEMENT 触发器，它将在一个操作中插入所有已删除的行，如 这个： 创建触发器MyTable_TD 删除MyTable后 参考旧表已删除 每个声明 BEGIN INSERT INTO MyTableA SELECT * from Deleted END 这很好用，但是如果我批量删除了数百万美元的b $ b记录，删除会运行很长时间而不会写任何 存档记录。只有完成所有删除后才会写入任何存档 记录。如果操作被中断，删除 将结束不完整，并且根本不会写入存档记录 - 记录将永久丢失。归档表不是记录的。 我可以看到这种方法的性能优势，因为插入是作为单个操作完成的（尽管可能有判罚为/ b $ b个人插入临时表。 我现在正试图将其重写为基于FOR EACH ROW的触发器， ，期望在每行删除后每行插入一行，因此如果操作被中断，最多只有一行 将丢失。 br /> 这就是我要做的事情： 创建触发器MyTable_TD 删除MyTable后 引用旧行已删除 每行行 BEGIN INSAT INTO MyTableA SELECT已删除。*来自SYSIBM.SYSDUMMY1 END 但当然触发器有效负载无效，因为Deleted是一行 引用而不是表引用。 所以我的问题是：基于行的触发器，如何我可以在插入语句中引用旧的已删除行来引用 。我不能使用单独命名列的 列表作为a）我希望这是通用的b）我想要它是低维护和c）基础表有数百个 的列并且单独引用它们将是皇家PITA！ 提前感谢任何建议， JohnO

解决方案

5月9日上午11:45，JohnO< johno1 ... @ gmailwrote：

大家好， 这个问题与iSeries V5R4和db2有关。 我想实现一个AFTER DELETE触发器将已删除的行 保存到存档表中，我最初将其定义为FOR EACH STATEMENT 触发器，它将在一个操作中插入所有已删除的行，如 这个： CREATE TRIGGER MyTable_TD 删除MyTable后 引用旧表删除 每张声明 BEGIN INSERT INTO MyTableA SELECT * from Deleted END 这个效果很好，但如果我是批量删除数百万的 记录，删除将运行很长时间而不写任何 存档记录。只有完成所有删除后才会写入任何存档 记录。如果操作被中断，删除 将结束不完整，并且根本不会写入存档记录 - 记录将永久丢失。归档表不是记录的。 我可以看到这种方法的性能优势，因为插入是作为单个操作完成的（尽管可能有判罚为/ b $ b个人插入临时表。 我现在正试图将其重写为基于FOR EACH ROW的触发器， ，期望在每行删除后每行插入一行，因此如果操作被中断，最多只有一行 将丢失。 br /> 这就是我要做的事情： 创建触发器MyTable_TD 删除MyTable后 引用旧行已删除 每行行 BEGIN INSAT INTO MyTableA SELECT已删除。*来自SYSIBM.SYSDUMMY1 END 但当然触发器有效负载无效，因为Deleted是一行 引用而不是表引用。 所以我的问题是：基于行的触发器，如何我可以在插入语句中引用旧的已删除行来引用 。我不能使用单独命名列的 列表作为a）我希望这是通用的b）我想要它是低维护和c）基础表有数百个 的列并且单独引用它们将是皇家PITA！ 提前感谢任何建议， JohnO

我发现这个帖子： groups.google.co.nz /group/com...5649e6e626354d 其中Serge以否定的方式回复。 Serge，正如我所说，基于 语句的触发器有效，但是当我删除 数百万行时它会出现问题。

JohnO写道：

5月9日上午11点45分，JohnO< johno1 ... @ gmailwrote：

>大家好， 这个问题与iSeries V5R4和db2有关。 我想实现一个AFTER DELETE触发器将已删除的行保存到存档表中，我最初将其定义为FOR EACH STATEMENT 触发器，它将在一个操作中插入所有已删除的行，如这个： 创建触发器MyTable_TD 删除MyTable后参考旧表已删除每个声明开始插入MyTableA SELECT *来自已删除结束 这很好用，但如果我批量删除了数百万的记录，删除会运行很长时间而不会写任何弧蜂巢记录。只有完成所有删除操作后才会写入任何存档记录。如果操作被中断，则删除将结束不完整，并且根本不会写入存档记录 - 记录将永久丢失。归档表没有记录。 我可以看到这种方法的性能优势，因为插入作为单个操作完成（尽管可能会有个人的惩罚）插入到临时表中。 我现在正在尝试将其重写为基于FOR EACH ROW的触发器，期望在每个之后立即插入一行行被删除，所以如果操作被中断，最多只有一行会丢失。 这就是我想要做的：创建触发器MyTable_TD 之后删除MyTable 引用旧行已删除每行开始插入MyTableA SELECT已删除。*来自SYSIBM.SYSDUMMY1 END 引用而不是表引用。 所以我的问题是：基于行的触发器，我该如何引用我的insert语句中的旧删除行。我不能使用单独命名列的列表作为a）我希望这是通用的b）我希望它是低维护和c）基础表有数百个列并且单独引用它们将是皇家PITA！ 提前感谢任何建议， JohnO

我发现这个帖子： groups.google.co.nz/group/com...5649e6e626354d 其中Serge以否定的方式回复。 Serge，正如我所说，基于 语句的触发器有效，但是当我删除数百万行时，它会出现问题。

一直以来便利都有它的价格...... 在未来的DB2版本中你可以做到： INSERT IN T VALUES ROW newrow （或类似的东西）。但是那将是一段时间...... 干杯 Serge - Serge Rielau DB2解决方案开发 IBM多伦多实验室

5月9日下午1:04，Serge Rielau< srie ... @ ca.ibmwrote：

JohnO写道：

On 5月9日上午11：45，JohnO< johno1 ... @ gmailwrote：

Hi All，

此问题与iSeries V5R4和db2有关。

我想实现一个AFTER DELETE触发器来保存已删除的行 到一个存档表，我最初将它定义为FOR EACH STATEMENT 触发器，它将在一个操作中插入所有已删除的行，如 这个：

CREATE TRIGGER MyTable_TD 删除MyTable后 REFERENCING OLD TABLE AS已删除 每个语句 BEGIN INSERT INTO MyTableA SELECT * from Deleted END

这个效果很好，但是如果我是批量删除数百万的 记录，删除将运行很长时间而不写任何 存档记录。只有完成所有删除后才会写入任何存档 记录。如果操作被中断，删除 将结束不完整，并且根本不会写入存档记录 - 记录将永久丢失。归档表未记录。

我可以看到这种方法的性能优势，因为插入是 作为单个操作完成（尽管可能会对临时表中的单个插入有的惩罚）。

我现在正试图将其重写为基于FOR EACH ROW的触发器， 期望一行插入将在每行删除后立即发生，因此如果操作中断，最多一行 将丢失。

这就是我想要做的： CREATE TRIGGER MyTable_TD 删除MyTable后 引用旧行已删除 每行 BEGIN INSERT INTO MyTableA SELECT已删除。*来自SYSIBM.SYSDUMMY1 END

但当然触发器有效负载无效，因为Deleted是一行 引用而不是表引用。

所以我的问题是这样的：在基于行的触发器中，我该如何引用 我的insert语句中的旧删除行。我不能使用单独命名列的 列表作为a）我希望这是通用的b）我想要它是低维护和c）基础表有数百个b $ b b的列并且单独引用它们将成为皇家PITA！

提前感谢任何建议， JohnO

我找到了这个主题： groups.google.co.nz/group/com.../browse_frm/th 。 ..

其中Serge回答否定。 Serge，正如我所说，基于 语句的触发器有效，但是当我删除数百万行时，它会出现问题。

一如既往的便利性价格...... 在未来的DB2版本中你可以做到： INSERT IN T VALUES ROW newrow （或类似的东西）。但是那将是一段时间...... 干杯 Serge - Serge Rielau DB2解决方案开发 IBM多伦多实验室

谢谢Serge，我知道你会确认我的怀疑。 你能想出一种改进基于语句的触发器的方法吗？它可以使用，但是当删除数百万行时，我不习惯使用它，因为如果删除被中断，存档行会丢失 删除的记录保持删除状态。如果我在 存档表上启用日记会使它更安全吗？是否有性能 和磁盘空间损失？ 我真的希望删除运行一段时间， 然后能够中断删除，相应的 插入完成。然后我可以再次重新开始删除。 谢谢 johnO

Hi All, This question is related to iSeries V5R4 and db2. I want to implement an AFTER DELETE trigger to save the deleted rows to an archive table, I initially defined it as a FOR EACH STATEMENT trigger that would insert all the deleted rows in one operation like this: CREATE TRIGGER MyTable_TD AFTER DELETE ON MyTable REFERENCING OLD TABLE AS Deleted FOR EACH STATEMENT BEGIN INSERT INTO MyTableA SELECT * from Deleted END This worked pretty well, but if I was bulk deleting millions of records, the delete would run for a long time without writing any archive records. Only once all the deletes completed would any archive records get written. If the operation was interrupted, the delete would end incomplete, and no archive records are written at all - the records are lost forever. The archive table is not journaled. I can see a performance benefit in this approach as the inserts are done as a single operation (although there may be a penalty of individual inserts into a temp table). I am now attempting to rewrite this as a FOR EACH ROW based trigger, on the expectation that one row insert will occur immediately after each row deleted, so if the operation is interrupted, at most one row is lost. This is what I am trying to do: CREATE TRIGGER MyTable_TD AFTER DELETE ON MyTable REFERENCING OLD ROW AS Deleted FOR EACH ROW BEGIN INSERT INTO MyTableA SELECT Deleted.* from SYSIBM.SYSDUMMY1 END But of course the trigger payload is invalid as Deleted is a row reference not a table reference. So my question is this: in a row based trigger, how can I reference the old deleted row in my insert statement. I cannot use a list of individually named columns as a) I want this to be generic and b) I want it to be low maintenance and c) the underlying table has hundreds of columns and referencing them individually would be a royal PITA! Thanks in advance for any advice, JohnO

解决方案

On May 9, 11:45 am, JohnO <johno1...@gmailwrote:

Hi All, This question is related to iSeries V5R4 and db2. I want to implement an AFTER DELETE trigger to save the deleted rows to an archive table, I initially defined it as a FOR EACH STATEMENT trigger that would insert all the deleted rows in one operation like this: CREATE TRIGGER MyTable_TD AFTER DELETE ON MyTable REFERENCING OLD TABLE AS Deleted FOR EACH STATEMENT BEGIN INSERT INTO MyTableA SELECT * from Deleted END This worked pretty well, but if I was bulk deleting millions of records, the delete would run for a long time without writing any archive records. Only once all the deletes completed would any archive records get written. If the operation was interrupted, the delete would end incomplete, and no archive records are written at all - the records are lost forever. The archive table is not journaled. I can see a performance benefit in this approach as the inserts are done as a single operation (although there may be a penalty of individual inserts into a temp table). I am now attempting to rewrite this as a FOR EACH ROW based trigger, on the expectation that one row insert will occur immediately after each row deleted, so if the operation is interrupted, at most one row is lost. This is what I am trying to do: CREATE TRIGGER MyTable_TD AFTER DELETE ON MyTable REFERENCING OLD ROW AS Deleted FOR EACH ROW BEGIN INSERT INTO MyTableA SELECT Deleted.* from SYSIBM.SYSDUMMY1 END But of course the trigger payload is invalid as Deleted is a row reference not a table reference. So my question is this: in a row based trigger, how can I reference the old deleted row in my insert statement. I cannot use a list of individually named columns as a) I want this to be generic and b) I want it to be low maintenance and c) the underlying table has hundreds of columns and referencing them individually would be a royal PITA! Thanks in advance for any advice, JohnO

I found this thread: groups.google.co.nz/group/com...5649e6e626354d In which Serge replied in the negative. Serge, as I said, the statement based trigger works, but it has a problem when I am deleting millions of rows.

JohnO wrote:

On May 9, 11:45 am, JohnO <johno1...@gmailwrote:

>Hi All,This question is related to iSeries V5R4 and db2.I want to implement an AFTER DELETE trigger to save the deleted rowsto an archive table, I initially defined it as a FOR EACH STATEMENTtrigger that would insert all the deleted rows in one operation likethis:CREATE TRIGGER MyTable_TDAFTER DELETE ON MyTableREFERENCING OLD TABLE AS DeletedFOR EACH STATEMENTBEGIN INSERT INTO MyTableA SELECT * from DeletedENDThis worked pretty well, but if I was bulk deleting millions ofrecords, the delete would run for a long time without writing anyarchive records. Only once all the deletes completed would any archiverecords get written. If the operation was interrupted, the deletewould end incomplete, and no archive records are written at all - therecords are lost forever. The archive table is not journaled.I can see a performance benefit in this approach as the inserts aredone as a single operation (although there may be a penalty ofindividual inserts into a temp table).I am now attempting to rewrite this as a FOR EACH ROW based trigger,on the expectation that one row insert will occur immediately aftereach row deleted, so if the operation is interrupted, at most one rowis lost.This is what I am trying to do:CREATE TRIGGER MyTable_TDAFTER DELETE ON MyTableREFERENCING OLD ROW AS DeletedFOR EACH ROWBEGIN INSERT INTO MyTableA SELECT Deleted.* from SYSIBM.SYSDUMMY1ENDBut of course the trigger payload is invalid as Deleted is a rowreference not a table reference.So my question is this: in a row based trigger, how can I referencethe old deleted row in my insert statement. I cannot use a list ofindividually named columns as a) I want this to be generic and b) Iwant it to be low maintenance and c) the underlying table has hundredsof columns and referencing them individually would be a royal PITA!Thanks in advance for any advice,JohnO

I found this thread: groups.google.co.nz/group/com...5649e6e626354d In which Serge replied in the negative. Serge, as I said, the statement based trigger works, but it has a problem when I am deleting millions of rows.

As always convenience has its price.... In some future version of DB2 you may be able to do: INSERT INTO T VALUES ROW newrow (or something like that). But that''ll be a while... Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab

On May 9, 1:04 pm, Serge Rielau <srie...@ca.ibmwrote:

JohnO wrote:

On May 9, 11:45 am, JohnO <johno1...@gmailwrote:

Hi All,

This question is related to iSeries V5R4 and db2.

I want to implement an AFTER DELETE trigger to save the deleted rows to an archive table, I initially defined it as a FOR EACH STATEMENT trigger that would insert all the deleted rows in one operation like this:

CREATE TRIGGER MyTable_TD AFTER DELETE ON MyTable REFERENCING OLD TABLE AS Deleted FOR EACH STATEMENT BEGIN INSERT INTO MyTableA SELECT * from Deleted END

This worked pretty well, but if I was bulk deleting millions of records, the delete would run for a long time without writing any archive records. Only once all the deletes completed would any archive records get written. If the operation was interrupted, the delete would end incomplete, and no archive records are written at all - the records are lost forever. The archive table is not journaled.

I can see a performance benefit in this approach as the inserts are done as a single operation (although there may be a penalty of individual inserts into a temp table).

I am now attempting to rewrite this as a FOR EACH ROW based trigger, on the expectation that one row insert will occur immediately after each row deleted, so if the operation is interrupted, at most one row is lost.

This is what I am trying to do: CREATE TRIGGER MyTable_TD AFTER DELETE ON MyTable REFERENCING OLD ROW AS Deleted FOR EACH ROW BEGIN INSERT INTO MyTableA SELECT Deleted.* from SYSIBM.SYSDUMMY1 END

But of course the trigger payload is invalid as Deleted is a row reference not a table reference.

So my question is this: in a row based trigger, how can I reference the old deleted row in my insert statement. I cannot use a list of individually named columns as a) I want this to be generic and b) I want it to be low maintenance and c) the underlying table has hundreds of columns and referencing them individually would be a royal PITA!

Thanks in advance for any advice, JohnO

I found this thread: groups.google.co.nz/group/com.../browse_frm/th...

In which Serge replied in the negative. Serge, as I said, the statement based trigger works, but it has a problem when I am deleting millions of rows.

As always convenience has its price.... In some future version of DB2 you may be able to do: INSERT INTO T VALUES ROW newrow (or something like that). But that''ll be a while... Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab

Thanks Serge, I knew you would confirm my suspicions. Can you think of a way to improve the statement based trigger? It works, but I am not comfortable with using it when deleting millions of rows as the archive rows are lost if the delete is interrupted yet the deleted records stay deleted. If I enable journaling on the archive table would that make it safer? Would there be a performance and disk space penalty? What I would really like would be for the delete to run for a while, and then be able to interrupt the delete, with the corresponding inserts completing. Then I could restart the delete another time. Thanks johnO