question about delete triggers referencing old/new 1

[ddiamond]

I found the following example of a delete trigger:

CREATE TRIGGER D_TRANSLOGGER
AFTER DELETE OF TRIGTEST/ATMTXN
REFERENCING OLD AS O_ROW NEW AS N_ROW
FOR EACH ROW MODE DB2SQL
INSERT INTO TRIGTEST/ATMTXNLOG
VALUES (
  'D', DATE(CURRENT TIMESTAMP), TIME(CURRENT TIMESTAMP),
  CHAR(N_ROW.ATMID)  || CHAR(N_ROW.ACCTID)|| CHAR(N_ROW.TCODE)||
  CHAR(N_ROW.AMOUNT) || CHAR(N_ROW.DESC),
  'DUMMY',
  CHAR(ATMID)||CHAR(ACCTID)|| CHAR(TCODE)||
  CHAR(AMOUNT)|| CHAR(DESC),
  'DUMMY' )

Why are they using N_ROW instead of O_ROW. I would expect O_ROW to contain the data before the delete, and N_ROW to contain the data after the delete which would be nil.

So I guess my real question is what do the old and new records refer to in a delete trigger.

 

[sathyarams]

I guess this is a bug ... What version/level/platform of db2 is this?

You shouldn't have been allowed to define "new row" in delete trigger

Cheers
Sathyaram


For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

&

http://www.idug.org/user/UserLogin.asp

 

[sathyarams]

This is the db2 message you should get when creating the trigger:

SQL0696N The definition of trigger "<trigger-name>" includes an
          invalid use of correlation name or transition table
          name "<name>".  Reason code="<reason-code>".

Explanation:

The trigger definition included an invalid use of "<name>".  The
value of "<reason-code>" indicates the specific problem as
follows.


 1 NEW correlation name and NEW_TABLE name are not allowed in a
DELETE trigger.

 2 OLD correlation name and OLD_TABLE name are not allowed in an
INSERT trigger.

 3 OLD_TABLE name and NEW_TABLE name are not allowed in a BEFORE
trigger.

 The statement cannot be processed.

User Response:

Remove the invalid correlation name or transition table name
along with the preceding keyword.

 sqlcode :  -696

 sqlstate :  42898

For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

&

http://www.idug.org/user/UserLogin.asp

 

[ddiamond]

Thanks for your replies. So in a delete trigger, you should only reference old.

BTW - My platform as OS/400 v5r4.

 

[sathyarams]

 My platform as OS/400 v5r4.

Ooooo .. I haven't got a clue about this version.
My message was in DB2 LUW

So in a delete trigger, you should only reference old.

Logically, YES, unless DB2 iseries team have decided for some reason, OLD and NEW will have same values for INSERT and DELETE.
I would recommend to open a PMR and also check any of the recent APARs to see if this issue has been addressed. In any case, it is best to get the triggers correct now to avoid future "surprises"!!!

Sathyaram





For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

&

http://www.idug.org/user/UserLogin.asp