基于行和语句的触发器问题 [英] Row and Statement based Trigger Question

问题描述

大家好，


这个问题与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 ... @ gmail.comwrote：

大家好，


这个问题与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

我发现这个帖子：
http://groups.google.co.nz /group/com...5649e6e626354d


其中Serge以否定的方式回复。 Serge，正如我所说，基于

语句的触发器有效，但是当我删除

数百万行时它会出现问题。

JohnO写道：

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

>大家好，

这个问题与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

我发现这个帖子：
http://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.ibm.comwrote：

JohnO写道：

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

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

我找到了这个主题：
http://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 <[e-mail]:

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:
http://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 <[e-mail]:

>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:
http://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 <[e-mail]:

JohnO wrote:

On May 9, 11:45 am, JohnO <[e-mail]:

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:
http://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

这篇关于基于行和语句的触发器问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！