Archives of the TeradataForum

Message Posted: Mon, 18 Dec 2006 @ 14:11:12 GMT

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

Hi all,

I'm trying to construct an appropriate Trigger such that an Update date will be set against a (newly added) column (DAP_UPDT_DT) on a Target table, based upon an Update statement being applied to the table from a Work table, which itself holds the update entries. Currently, the Update statement is setting a last date of trade column for a customer, against a given product.

We don't want to change the load process so I offered to create a Before Update Trigger that sets the new column based upon the Update being applied.

When I tried to create the Trigger, like this :

     REPLACE TRIGGER DEVG123AHE.DIVACCOUNTPROD_TR1 before UPDATE ON
     DIVACCOUNTPROD
     REFERENCING OLD_TABLE AS o
     FOR EACH STATEMENT
     (
     UPDATE DIVACCOUNTPROD
     SET DAP_UPDT_DT = CURRENT_DATE
     WHERE /*the key of Target table matches key of Work table */
     COU_ID=o.COU_ID
     AND ACG_ID=o.ACG_ID
     AND DIV_ID=o.DIV_ID
     AND ACC_ID=o.ACC_ID
     AND PRD_ID=o.PRD_ID;
     );

I get a 'BEFORE Statement Triggers are not supported'.

Ok, so I try an 'After' Update Trigger and, sure enough, it compiles.

When I run it, however, on an Update statement of 1000 rows, I get a 5848 (Maximum recursive level of Triggers has <been> exceeded) message.

Now for the whole truth ... that's what I was doing until the team informs me that they're actually doing a Delete followed by a re-Insert ! So, no need for the Trigger, I just set a Default value of Current_Date on the new column definition.

So, sorry to get you to read to this point but, purely as an academic exercise, how would you create the appropriate Trigger ? As a Row level Trigger ? (I hadn't done this as I'd expected hundreds of thousands of updates from the Work table).

Whilst now an academic exercise, I still think this is an interesting question, and no doubt something I'll have to return to in the future.

So, for the academics amongst you, can you help me ?

Kind regards,

Dave Clough
Database Designer
Express ICS

www.tnt.com

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