Archives of the TeradataForum
Message Posted: Fri, 07 Oct 2005 @ 15:10:08 GMT
Problem: I've created what I thought needed to be a Statement Trigger (because I'm Inserting many rows from a Work table into my Target table) but I'm getting the resultant error message (7547) "Target row updated by multiple source rows", which prevents me from using the Trigger.
Basically what I'm trying to do is to have an Insert Tigger on a Child_Status table which will update it's Parent_Order table when a new status hits the Child_status table (and qualifies as being in a set of statuses for indicating 'delivered'). A Work table would be used to do an Insert/Select into the Child_Status table, for the new rows.
Given that there can be more than one Status in the Status_Work table for a given Order, Teradata is currently providing the error correctly.
One solution - I suppose - is to write only DISTINCT Order statuses from the Status_Work table to an intermediary table, where I'd then place the Trigger. That way, the Insert/Select would only be doing a single update per Order. But this is all a bit convoluted.
What I'd like to be able to do within the Trigger is to say "Apply Triggering action on last status in set" - suggests an OLAP Partition type thing.
Alternatively, would a FOR EACH ROW not suffer from the same thing? I think it will because the Insert/Select is still one Commit process.
Anyway, here's the code so far :
REPLACE TRIGGER CHILD_STATUS_TR1 AFTER INSERT ON CHILD_STATUS REFERENCING NEW_TABLE AS NewTable FOR EACH STATEMENT WHEN (1 = (SELECT 1 FROM NewTable WHERE EXISTS (SELECT 1 FROM STATUSGROUP SG WHERE SG.STATUS_CD = NewTable.STATUS_CD AND SG.STATUS_GROUP='DL' ))) BEGIN ATOMIC ( UPDATE PARENT_ORDER SET ORDER_DELIVERED_IN = 'Y' WHERE CON_SRKY_ID = NewTable.CON_SRKY_ID; ) END;
The When bit ensures that the incoming Status is in the sort of Status Group that qualifies as being Delivered.
Not even sure what the 'ATOMIC' means but I know the Trigger doesn't compile without it!
Any Trigger experts out there want to tell me how wrong I've got it?
Thanks in advance.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|