By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,866 Members | 1,704 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,866 IT Pros & Developers. It's quick & easy.

My first trigger

P: n/a
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+Sa tOrder+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?

Jul 23 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a
Having separate columns for each day is a mistake - it makes it much
harder to use your data and maintain integrity. Also, never store
totals in the database if you can avoid it. Since the total is derived
from columns in the same row it is redundant.

I would expect to see a table something like this for the detail info:

CREATE TABLE Stock (dt DATETIME NOT NULL, amt INTEGER NOT NULL /* other
columns and primary key unspecified ? */)

and then use a query like this to produce the summary report:
SELECT MIN(dt),
SUM(CASE WHEN LEFT(DATENAME(DW,dt),3) = 'Mon' THEN amt END) AS
mon_order,
SUM(CASE WHEN LEFT(DATENAME(DW,dt),3) = 'Tue' THEN amt END) AS
tue_order,
SUM(CASE WHEN LEFT(DATENAME(DW,dt),3) = 'Wed' THEN amt END) AS
wed_order,
SUM(CASE WHEN LEFT(DATENAME(DW,dt),3) = 'Thu' THEN amt END) AS
thu_order,
SUM(CASE WHEN LEFT(DATENAME(DW,dt),3) = 'Fri' THEN amt END) AS
fri_order,
SUM(CASE WHEN LEFT(DATENAME(DW,dt),3) = 'Sat' THEN amt END) AS
sat_order,
SUM(CASE WHEN LEFT(DATENAME(DW,dt),3) = 'Sun' THEN amt END) AS
sun_order
FROM Stock
GROUP BY ROUND(DATEDIFF(D,'20050103',dt)/7.0,0,1)

Of course this doesn't require a trigger at all.

Hope this helps.

--
David Portas
SQL Server MVP
--

Jul 23 '05 #2

P: n/a
Thanks David for the quick reply. Unfortunately I can not change the
structure of the stock table as it is specified by the customer. I
agree that the total is obsolete but I would still like to know
whether there is a reasonable solution that works with the given data
structure - I simply want to make sure that all reports and forms in
the frontend (Access) that refer to TotalOrder work, without having to
scrutinize or rewrite large portions of the frontend. This is why I
thought a trigger might be suitable.

This is the definition of tblStock:

CREATE TABLE [dbo].[tblStock] (
[YearWeek] [int] NOT NULL ,
[ProductCode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[StockonHand] [int] NULL ,
[StockonOrder] [int] NULL ,
[TotalOrder] [int] NULL ,
[MonOrder] [int] NULL ,
[TueOrder] [int] NULL ,
[WedOrder] [int] NULL ,
[ThursOrder] [int] NULL ,
[FriOrder] [int] NULL ,
[SatOrder] [int] NULL ,
[SunOrder] [int] NULL ,
[Dummy1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

YearWeek and ProductCode are primary keys. YearWeek is formated like
YYYYWW and is used in a lot of the tables & frontend so I can not
change that structure anymore.
tia
Axel

Jul 23 '05 #3

P: n/a
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/de...asp?frame=true
with the IF UPDATE (column) clause function.
http://msdn.microsoft.com/library/de...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.Fr iOrder+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" <ax***@gofree.indigo.ie> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.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+Sa tOrder+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?

Jul 23 '05 #4

P: n/a
Thanks John,

seems you have a more elegant solution - computed column sounds like
just what I need. Do I need to replace tblStock with a view or can I
have a computed column in a conventional table?

Also I have followed your links to the 2 MSDN topics maybe I
misunderstand the IF UPDATE. Doesn't IF UPDATE specify the field that
triggers the change? (so IF UPDATE(MonOrder) means somebody changed
MonOrder? Also can I not trigger on row level in order to speed up the
trigger?

I am also considering just dropping the total field and letting the
frontend 'break' so i can switch to a calculated total - I am just
afraid of the additional overhead and subsequent performance hit and
that I might oversee it used somewhere.
tia
Axel

Jul 23 '05 #5

P: n/a
You can put computed columns in a table or a view. I suggest a view.
Maybe you can replace the whole of this unfortunate table with a view
eventually.

IF UPDATE() doesn't determine if any value changed it just tells you
whether a column was referenced after the SET clause of an UPDATE
statement.

There are no row-level triggers in SQL Server and you don't need them.
It's much more efficient to do a set-based UPDATE.

--
David Portas
SQL Server MVP
--

Jul 23 '05 #6

P: n/a
David,

many thanks for clearing this up. I have went for the computed field
this is the stored procedure that changes the underlying table
(snipped):

<snip>

ALTER TABLE dbo.tblStock
DROP CONSTRAINT DF_tblStock_SunOrder
GO
CREATE TABLE dbo.Tmp_tblStock
(
YearWeek int NOT NULL,
ProductCode nvarchar(50) NOT NULL,
StockonHand int NULL,
StockonOrder int NULL,
TotalOrder AS
MonOrder+TueOrder+WedOrder+ThursOrder+FriOrder+Sat Order+SunOrder,
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 change the design 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.

Is your suggestion of Normalization based on 3rd NF?

regards
Axel

Jul 23 '05 #7

P: n/a
Your design isn't even 1NF (because of the repeating group). 3NF is
normally the minimum expected standard.

--
David Portas
SQL Server MVP
--

Jul 23 '05 #8

P: n/a
Hi

You may want to make the columns NOT NULL with a zero default and a FK for
your ProductCode (which seems quite long!).

John

"Axel" <ax***@gofree.indigo.ie> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
David,

many thanks for clearing this up. I have went for the computed field
this is the stored procedure that changes the underlying table
(snipped):

<snip>

ALTER TABLE dbo.tblStock
DROP CONSTRAINT DF_tblStock_SunOrder
GO
CREATE TABLE dbo.Tmp_tblStock
(
YearWeek int NOT NULL,
ProductCode nvarchar(50) NOT NULL,
StockonHand int NULL,
StockonOrder int NULL,
TotalOrder AS
MonOrder+TueOrder+WedOrder+ThursOrder+FriOrder+Sat Order+SunOrder,
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 change the design 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.

Is your suggestion of Normalization based on 3rd NF?

regards
Axel

Jul 23 '05 #9

P: n/a
Dear David,

you wrote
Your design isn't even 1NF (because of the repeating group). 3NF is

normally the minimum expected standard.

As regards 3NF I am not too sure whether I really understand. In this
case I have a PK consisting of YearWeek and ProductCode which means
every row contains information about the stock of precisely that
product at precisely that week. The 7 "day" fields are always used and
each contain unique data (recording stock fluctuations during that
week).

I know instead I could have a PK of ProductCode and Day instead and
then dereference the day of week from a calendar table but does this
step really get rid of duplicative columns? Bearing in mind that (at
least in the definition of the customer) each week always has data for
7 unique days, I can not see any obsolete repetition here.

I must admit computer science classes are a long way back (hence my
uncertainty about 1NF) and the original table design is from my
colleague which is my main reason for not changing it. Still I am not
too sure what you referred to with "repeating group"... please clarify.

thanks
Axel

Jul 23 '05 #10

P: n/a
Yes I was referring loosely to the 7 day columns as a repeating group.
Informally, I would identify a repeating group as any situation where
the same attribute appears in mutliple columns, even if the number of
non-null values of that attribute is fixed. That definition isn't
necessarily equivalent to a more formal, theoretical definition of a
repeating group but I think it does represent the spirit of what most
database architects would understand to be a suspect design.

Hypothetically it could be argued (unreasonably in my judgement) that
in some conceptual model the value recorded for Monday is a completely
different attribute to the value recorded for Tuesday purely because
that value is recorded on a different date. I cannot think of any real
world scenario where that might make logical sense. In every case I can
conceive of there are just *two* attributes here: Date and Amount
(unspecified as to whether this is a monetary amount or quantity or
whatever). Unless, that is, the day of week is really hiding some other
semantics, such as the fact that on Monday you do a stock check of one
particular product category and only record that value and then on
Tuesday you record the stock for a different product category!

However that may be, from a purely practical point of view it is likely
to be very inconvenient to create 7 separate columns because that group
of columns makes so many queries much more difficult. Queries that
involve summation or monitoring trends over a range of dates will be
much easier to write and much more efficient if you have a single date
column and a single value to work with. Why do you doubt it?

--
David Portas
SQL Server MVP
--

Jul 23 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.