Posted by John Bell on 06/02/05 14:55

Hi

This can be done using a computed column instead but it seems like you are
confusing the update statement
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ua-uz_82n9.asp?frame=true
with the IF UPDATE (column) clause function.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_7eeq.asp?frame=true

Your database design needs re-considering as it is not normalised.

You may also want to use an instead of trigger such as

CREATE TRIGGER trigger_stocksum ON tblStock INSTEAD OF UPDATE
AS
BEGIN
IF @@ROWCOUNT = 0 RETURN
SET NOCOUNT ON

UPDATE t
SET TotalOrder =
i.MonOrder+i.TueOrder+i.WedOrder+i.ThursOrder+i.FriOrder+i.SatOrder+i.SunOrder,
MonOrder = i.MonOrder,
TueOrder = i.TueOrder,
WedOrder = i.WedOrder,
ThursOrder = i.ThursOrder,
FriOrder = i.FriOrder,
SatOrder = i.SatOrder,
SunOrder = i.SunOrder
FROM tblStock t
JOIN INSERTED i on i.pk = t.Pk
END

Where pk represents the primary key column(s)

John

"Axel" <axelg@gofree.indigo.ie> wrote in message
news:1117710424.075393.316300@o13g2000cwo.googlegroups.com...
> Hello,
>
> I would like to create a (what I believe is) simple trigger that
> updates a row in one table based on updates of corresponding fields of
> same row. Its a "week total" field that sums up values of 7 singular
> "day" fields. Here is what I have come up with using the TSQL
> documentation.
>
> CREATE TRIGGER trigger_stocksum ON tblStock FOR UPDATE
> AS
> IF UPDATE(MonOrder) OR UPDATE(TueOrder) OR UPDATE(WedOrder) OR
> UPDATE(ThursOrder) OR UPDATE(FriOrder) OR UPDATE(SatOrder) OR
> UPDATE(SunOrder)
> SET TotalOrder =
> (MonOrder+TueOrder+WedOrder+ThursOrder+FriOrder+SatOrder+SunOrder)
>
>
> all these fields (including TotalOrder) are contained in tblStock, I
> get a syntax error
> Server: Msg 170, Level 15, State 1, Procedure trigger_stocksum, Line 4
> Line 4: Incorrect syntax near '='.
>
> Can somebody please help me?
>

[Back to original message]