Archives of the TeradataForum

Message Posted: Thu, 20 Apr 2006 @ 09:21:09 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

We have the following row trigger:

     REPLACE TRIGGER DATABASENAME.TRIGGER1 ENABLED
             AFTER UPDATE OF (COLUMNX) ON   DATABASENAME.TABLE1
             REFERENCING OLD AS OLD_TAB NEW AS NEW_TAB
             FOR EACH ROW
               (INSERT INTO DATABASENAME.CHANGES
                VALUES (OLD_TAB.PRDID
                       ,OLD_TAB.MNH_EPD_CRD_AM
                       ,NEW_TAB.MNH_EPD_CRD_AM)
               ;)
             ;

We run the following update which fires the row trigger:

     Update Databasename.Table1  t1
     set T1.ColumnX = Table2.ColumnX
     where T1.IdColumn = Table2.IdColumn
     ;

Assuming the following position before the update:

     Databasename.Table1             Table2
     IdColumn        ColumnX                 IdColumn        ColumnX
     A               10                      A               15
     B               20                      B               25
     C               30                      C               35

I would expect the Changes table to be as follows:

     IdColumn        OldColumnX              NewColumnX
     A               10                      15
     B               20                      25
     C               30                      35

However, what is actually in the Changes table is as follows - the Old column values are populated with the New column values:

     IdColumn        OldColumnX              NewColumnX
     A               15                      15
     B               25                      25
     C               35                      35

Interestingly, if the update is performed using a hardcoded value, then the trigger appears to fire as I would expect it to.

     Update Databasename.Table1  t1
     set T1.ColumnX = 5
     where T1.IdColumn = Table2.IdColumn
     ;

     IdColumn        OldColumnX              NewColumnX
     A               10                      5
     B               20                      5
     C               30                      5

Is this a bug or am I missing something obvious ? We are running at V2R5.01.02.23. Any advice appreciated.

Many Thanks,

Alan Okell

	<Prev	Next>		<<First	<Prev	Next>	Last>>