我的第一次触发 [英] My first trigger

问题描述

你好，


我想创建一个（我相信的）简单的触发器

根据更新的一个表更新一行中的一行相应的字段

同一行。它是周总数。总结7个单数的值的字段

" day"领域。以下是我使用TSQL

文档提出的建议。


CREATE TRIGGER trigger_stocksum ON tblStock FOR UPDATE

AS

如果更新（MonOrder）或更新（TueOrder）或更新（WedOrder）或

更新（ThursOrder）或更新（FriOrder）或更新（SatOrder）或

更新（SunOrder）

SET TotalOrder =

（MonOrder + TueOrder + WedOrder + ThursOrder + FriOrder + Sa tOrder + SunOrder）

所有这些字段（包括TotalOrder）都包含在tblStock中，我

得到语法错误

服务器：消息170，级别15，状态1，过程trigger_stocksum，第4行

第4行：''=''附近的语法不正确。


有人可以帮帮我吗？

解决方案

每天都有单独的列是一个错误 - 这使得它更难以使用您的数据并保持完整性。此外，如果您可以避免，请不要在数据库中存储

总计。由于总数是从同一行的列中得出的，因此它是多余的。


我希望看到一个类似于这样的表格来获取详细信息：


CREATE TABLE Stock（dt DATETIME NOT NULL，amt INTEGER NOT NULL / * other

列和主键未指定？* /）


然后使用这样的查询来生成摘要报告：

SELECT MIN（dt），

SUM（左边的情况下（DATENAME（ DW，dt），3）=''周一''然后结束）作为

mon_order，

SUM（左边的情况（DATENAME（DW，dt），3 ）=''星期二'然后结束）as

tue_order，

SUM（LEE（DATENAME（DW，dt），3）=''Wed' '那么结束了）

wed_order，

SUM（LEE（DATENAME（DW，dt），3）='''Thu''然后结束）AS

thu_order，

SUM（LEE（DATENAME（DW，dt），3）='''Fri''然后结束）作为

fri_order，

SUM（左边的情况（DATENAME（DW，dt），3 ）=''星期六''然后结束）as

sat_order，

SUM（LEE（DATENAME（DW，dt），3）=''太阳'时的情况'那么结束了）

sun_order

来自股票

GROUP BY ROUND（DATEDIFF（D，'''20050103''，dt）/ 7.0,0,1）


当然这根本不需要触发器。


希望这会有所帮助。


-

David Portas

SQL Server MVP

-

感谢David的快速回复。不幸的是，我无法更改库存表的

结构，因为它是由客户指定的。我ñ
同意总数已过时，但我仍然想知道

是否有合理的解决方案可以使用给定的数据

结构 - 我只是想确保所有报告和表格在

前端（Access）中引用TotalOrder工作，而不必

仔细检查或重写大部分前端。这就是为什么我认为触发器可能适合的原因。

这是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和ProductCode是主键。 YearWeek的结构类似于

YYYYWW，用于许多表格和表格中。前端所以我不能

改变这种结构。

tia

Axel

嗨


这可以使用计算列来完成，但看起来你好像是b $ b混淆了更新语句
< a rel =nofollowhref =http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ua-uz_82n9.asp?frame=truetarget =_ blank > http://msdn.microsoft.com/library/de...asp?frame=true
带有IF UPDATE（列）子句功能的
。
< a rel =nofollowhref =http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_7eeq.asp?frame=truetarget =_ blank> http://msdn.microsoft.com/library/de...asp?frame=true

您的数据库设计需要重新考虑，因为它没有规范化。


您可能还想使用而不是触发器，例如


CREATE TRIGGER trigger_stocksum ON tblStock INSTEAD OF UPDATE

AS
BEGIN

IF @@ ROWCOUNT = 0返回

设定值

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

来自tblStock t

加入我的i.pk = t.Pk

结束


其中pk代表主键专栏


John


" Axel" <斧*** @ gofree.indigo.ie>在消息中写道

news：11 ********************** @ o13g2000cwo.googlegr oups.com ...

你好，

我想创建一个（我相信的）简单的触发器，它可以根据相应字段的更新来更新一个表中的一行
同一行。它是周总数。总结7个单数天的值的字段领域。以下是我使用TSQL
文档提出的建议。

创建TRIGGER trigger_stocksum ON tblStock更新
如果更新（MonOrder）或更新（TueOrder） ）或更新（WedOrder）或
更新（ThursOrder）或更新（FriOrder）或更新（SatOrder）或
更新（SunOrder）
SET TotalOrder =
（MonOrder + TueOrder + WedOrder + ThursOrder + FriOrder + Sa tOrder + SunOrder）

所有这些字段（包括TotalOrder）都包含在tblStock中，我收到语法错误
服务器：消息170，级别15，状态1，程序trigger_stocksum，第4行
第4行：''=''附近的语法不正确。

有人可以帮助我吗？

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?

解决方案

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
--

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

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?

这篇关于我的第一次触发的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！