On May 9, 11:45 am, JohnO <johno1...@[EMAIL PROTECTED]
> wrote:
> 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/comp.databases.ibm-db2/browse_frm/thread/3318907b813114ed/0a5649e6e626354d?hl=en&lnk=gst&q=referencing+old+row#0a5649e6e626354d
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.
|
