Archives of the TeradataForum
Message Posted: Mon, 18 Dec 2006 @ 14:11:12 GMT
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 ?
|Copyright 2016 - All Rights Reserved|
|Last Modified: 15 Jun 2023|