to trigger or not to trigger

[Axel]

Hello,

I have detail sections in several subforms that are used to fill daily
order data per product. Each row contains textboxes for the weekdays
and a locked textbox (txtTotal) for the week's sum. The week's sum is
also contained in the underlying table and I would like to update it
whenever any of the MonOrder .. SunOrder are changed. I believe the
easiest way to do this is in the backend (SQL Server) using a trigger
(?) - it would save me to go through the many subforms and finding the
event that is triggered when txtTotal.Value changes.

If there is a simple alternative to using a trigger, please tell me.

I think it can be achieved with a 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_stocksu m ON tblStock FOR UPDATE
AS
IF UPDATE(MonOrder ) OR UPDATE(TueOrder ) OR UPDATE(WedOrder ) OR
UPDATE(ThursOrd er) OR UPDATE(FriOrder ) OR UPDATE(SatOrder ) OR
UPDATE(SunOrder )
SET TotalOrder =
(MonOrder+TueOr der+WedOrder+Th ursOrder+FriOrd er+SatOrder+Sun Order)

all these fields (including TotalOrder) are contained in tblStock, I
get a syntax error
Server: Msg 170, Level 15, State 1, Procedure trigger_stocksu m, Line 4
Line 4: Incorrect syntax near '='.

Can somebody please help me?

[Axel]

Hi group,

after some research on
http://groups.google.ie/group/comp.d...qlserver?hl=en

I have opted for changing the field to a computed field.
this is the new table
(snipped):

<snip>

ALTER TABLE dbo.tblStock
DROP CONSTRAINT DF_tblStock_Sun Order
GO
CREATE TABLE dbo.Tmp_tblStoc k
(
YearWeek int NOT NULL,
ProductCode nvarchar(50) NOT NULL,
StockonHand int NULL,
StockonOrder int NULL,
TotalOrder AS
MonOrder+TueOrd er+WedOrder+Thu rsOrder+FriOrde r+SatOrder+SunO rder,

MonOrder int NULL,
TueOrder int NULL,
WedOrder int NULL,
ThursOrder int NULL,
FriOrder int NULL,
SatOrder int NULL,
SunOrder int NULL,
Dummy1 nvarchar(50

) NULL
) ON [PRIMARY]
GO
<snip>
the reason I can not normalize the table so easily (as much as I would
like to) is that it is currenty used in at least 22 queries and 11
forms in the frontend. It is used during Stock upload (importing a raw
table which is normalized in the same way) and stock reporting and in
all cases the separate storage of days in week fields seems to be quite
intuitive and performance appears to work quite well, so I am not too
bothered about changing it any time soon. Apart from that the data of
this table is not used anywhere else so this is more like a view
anyway. I am quite glad though that I did not create a trigger as it
would not work on row level anyway and would decrease performance.

hth
axel

[David Schofield]

On 2 Jun 2005 04:37:44 -0700, "Axel" <ax***@gofree.i ndigo.ie> wrote:

Hello,

I have detail sections in several subforms that are used to fill daily
order data per product. Each row contains textboxes for the weekdays
and a locked textbox (txtTotal) for the week's sum. The week's sum is
also contained in the underlying table and I would like to update it
whenever any of the MonOrder .. SunOrder are changed. I believe the
easiest way to do this is in the backend (SQL Server) using a trigger
(?) - it would save me to go through the many subforms and finding the
event that is triggered when txtTotal.Value changes.

If there is a simple alternative to using a trigger, please tell me.

I think it can be achieved with a 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_stocksu m ON tblStock FOR UPDATE
AS
IF UPDATE(MonOrder ) OR UPDATE(TueOrder ) OR UPDATE(WedOrder ) OR
UPDATE(ThursOr der) OR UPDATE(FriOrder ) OR UPDATE(SatOrder ) OR
UPDATE(SunOrde r)
SET TotalOrder =
(MonOrder+TueO rder+WedOrder+T hursOrder+FriOr der+SatOrder+Su nOrder)

all these fields (including TotalOrder) are contained in tblStock, I
get a syntax error
Server: Msg 170, Level 15, State 1, Procedure trigger_stocksu m, Line 4
Line 4: Incorrect syntax near '='.

Can somebody please help me?

Hi
Why store the sum in a table at all? Normal practice would be to
calculate it in a view or query.
If you are using forms, the afterupdate events can be used in place of
triggers.
David

[Axel]

> Why store the sum in a table at all? Normal practice would be to calculate it in a view or query.

Hi David,

true. The only reason why I did it this way was that I did not have
time to go through the frontend Access database (my colleague had
written most of this) to find all the places where the field was used.
But since it now is a calculated field it does not even take any
storage space, so I think thats a pretty neat solution.

I did not create a view because I have never done that before - and I
already had 'wasted' over an hour to investigate triggers and their
syntax.

In hindsight I could probably also have created a pass through query in
Access to replace the original table - which would have lead to the
same goal. For me it was important not to break the existing code or to
have the customer relink any tables.

regards,
Axel

PS: I am using google's web interface to create this message any idea
how I can hide my email addy? According to this page
"The group you are posting to is a Usenet group. Messages posted to
this group will make your email visible to anyone on the Internet."
To rephrase the question, does anybody know how I can add this group to
my thunderbird newsgroups?