Archives of the TeradataForum

Message Posted: Fri, 07 Oct 2005 @ 15:10:08 GMT

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

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.

_______________________
Dave Clough
Database Designer
Express ICS

www.tnt.com

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