JohnO wrote:
> 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.
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
|
