> > I forgot to JOIN tables:
>
> Yes, I picked that up as well, but I now get another error(s) with this code:
>
>
> CREATE TRIGGER Trig_PaymentsDetails
> ON PaymentsDetails
> FOR UPDATE, DELETE, INSERT
> AS
> BEGIN
> UPDATE Sales
> SET nBalance = nBalance + Tbl1.Amount
> FROM Sales
> INNER JOIN (SELECT iSaleid, SUM(nAmount) AS Amount
> FROM (SELECT iSaleid, -nAmount
> FROM INSERTED
> UNION ALL
> SELECT iSaleid, nAmount
> FROM DELETED) Tbl2
> GROUP BY iSaleid) Tbl1
> on Sales.iSaleid = Tbl1.iSaleid
>
> IF @@ERROR <> 0
> ROLLBACK TRANSACTION
>
> END
>
>
>
> Msg 8155, Level 16, State 2, Procedure Trig_PaymentsDetails, Line 11
> No column was specified for column 2 of 'Tbl2'.
> Msg 207, Level 16, State 1, Procedure Trig_PaymentsDetails, Line 14
> Invalid column name 'nAmount'.
>
>
> neither of which make any sense to me :-(
>
>
> The problem was -nAmount as a field name, I switched the UNION ALL so that I select from the Deleted table first, or I could have used an AS nAmount instead.
>
> Frank.
>
> Frank Cazabon
> Samaan Systems Ltd.
> www.SamaanSystems.com/
Grrrr :-)
....
(SELECT iSaleid, -nAmount AS nAmount
FROM INSERTED
UNION ALL
SELECT iSaleid, nAmount AS nAmount
FROM DELETED) Tbl2
....
-----------------
Borislav Borissov
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.