External SQL datasource

Hi Justin,

If I understood you correctly you need to create the trigger on dbo.Planodetail, not on dbo.WorkOrder because changes in the dbo.Planodetail are changing the CompletedQty field in dbo.WorkOrder.

Also, you need to monitor inserted and deleted inside the trigger because you are covering insert, update and delete trigger events.

Here is the code that I think would do the job:

CREATE TRIGGER tgr_SumQty
ON  dbo.PLANODETAIL
AFTER INSERT, DELETE, UPDATE
AS

BEGIN

        UPDATE p
                SET p.CompletedQty=pc.CompletedQty
        FROM dbo.WORKORDER p
                INNER JOIN (
                                        SELECT
                                                pc.Client
                                                , pc.Job
                                                , SUM(pc.completedAmount) AS CompletedQty
                                        FROM dbo.PLANODETAIL pc
                                                inner join (
                                                        select
                                                                i.Client, i.Job
                                                        from inserted i
                                                        union
                                                        select
                                                                d.Client, d.Job
                                                        from deleted d
                                                ) pcD on pc.Client = pcD.Client
                                                        and pc.Job = pcD.Job
                                        GROUP BY pc.Client, pc.Job
                ) pc ON p.Client=pc.Client
                        and p.Job = pc.Job
END

HTH

---

Regards, Dean Savović