触发错误 [英] Trigger Error
问题描述
我有一张桌子：
------------------------------ ----------------------
CREATE TABLE CATEGORY（
CATEGORY_ID INTEGER IDENTITY（ 1,1）NOT NULL，
CATEGORY_NAME VARCHAR（40）NOT NULL，
PARENT_CATEGORY_ID INTEGER，
CATEGORY_ICON IMAGE，
DEPTH INTEGER，
CONSTRAINT PK__CATEGORY PRIMARY KEY（CATEGORY_ID）
）
GO
------------------------------------------------- ---
我尝试创建此触发器，但我失败了：
----------- -----------------------------------------
CREATE TRIGGER [AI_CATEGORY] ON [dbo]。[CATEGORY]
FOR INSERT
AS
DECLARE @TEMP_ID AS INT，@ COUNTER AS INT
UPDATE AI_CATEGORY
SET @TEMP_ID = CATEGORY_ID，@ COUNTER = 1
如果PARENT_CATEGORY_ID不为空
BEGIN
WHILE @TEMP_ID不为空
BEGIN
更新AI_类别
SET @TEMP_ID = PARENT_CATEGORY_ID，
@COUNTER = @COUNTER + 1
END
END
更新类别
设置深度= @COUNTER
----------------- -----------------------------------
我明白了使用SQL Server 2000时出现以下错误：
服务器：消息207，级别16，状态3，过程AI_CATEGORY，第7行
无效的列名称''PARENT_CATEGORY_ID ''。
任何线索？
Coosa
I have a table:
----------------------------------------------------
CREATE TABLE CATEGORY (
CATEGORY_ID INTEGER IDENTITY(1,1) NOT NULL,
CATEGORY_NAME VARCHAR(40) NOT NULL,
PARENT_CATEGORY_ID INTEGER,
CATEGORY_ICON IMAGE,
DEPTH INTEGER,
CONSTRAINT PK__CATEGORY PRIMARY KEY (CATEGORY_ID)
)
GO
----------------------------------------------------
and i try to create this trigger, but i fail:
----------------------------------------------------
CREATE TRIGGER [AI_CATEGORY] ON [dbo].[CATEGORY]
FOR INSERT
AS
DECLARE @TEMP_ID AS INT, @COUNTER AS INT
UPDATE AI_CATEGORY
SET @TEMP_ID = CATEGORY_ID, @COUNTER = 1
IF PARENT_CATEGORY_ID IS NOT NULL
BEGIN
WHILE @TEMP_ID IS NOT NULL
BEGIN
UPDATE AI_CATEGORY
SET @TEMP_ID = PARENT_CATEGORY_ID,
@COUNTER = @COUNTER + 1
END
END
UPDATE CATEGORY
SET DEPTH = @COUNTER
----------------------------------------------------
And i get the following Error using SQL Server 2000:
Server: Msg 207, Level 16, State 3, Procedure AI_CATEGORY, Line 7
Invalid column name ''PARENT_CATEGORY_ID''.
Any clue?
Coosa
推荐答案
coosa写道：
[...]
我得到了和你一样的错误。但是，如果你稍微修改
触发器：
创建触发器[AI_CATEGORY] ON [dbo]。[CATEGORY]
FOR INSERT
AS
DECLARE @TEMP_ID AS INT，@ COUNTER AS INT，@ C AS INT
更新AI_CATEGORY
SET @TEMP_ID = CATEGORY_ID，@ COUNTER = 1
SELECT @C = PARENT_CATEGORY_ID from INSERTED
IF @C IS NOT NOT
BEGIN
WHILE @TEMP_ID不为空
BEGIN
更新AI_CATEGORY
SET @TEMP_ID = PARENT_CATEGORY_ID，
@COUNTER = @COUNTER + 1
END
END
更新类别
SET深度= @COUNTER
这样可行。不知道为什么，但是当Erland来的时候，我希望他能告诉我们你好吗？
Edward
-
阅读小组的阅读小组：
http://www.bookgroup.org.uk
coosa wrote:
[...]
I got the same error as you. However, if you slightly amend the
trigger thus:
CREATE TRIGGER [AI_CATEGORY] ON [dbo].[CATEGORY]
FOR INSERT
AS
DECLARE @TEMP_ID AS INT, @COUNTER AS INT, @C AS INT
UPDATE AI_CATEGORY
SET @TEMP_ID = CATEGORY_ID, @COUNTER = 1
SELECT @C = PARENT_CATEGORY_ID FROM INSERTED
IF @C IS NOT NULL
BEGIN
WHILE @TEMP_ID IS NOT NULL
BEGIN
UPDATE AI_CATEGORY
SET @TEMP_ID = PARENT_CATEGORY_ID,
@COUNTER = @COUNTER + 1
END
END
UPDATE CATEGORY
SET DEPTH = @COUNTER
This works. Don''t know why, but when Erland comes by, I expect he''ll
tell us!
Edward
--
The reading group''s reading group:
http://www.bookgroup.org.uk
2005年5月25日00:48:46 -0700，coosa写道：
On 25 May 2005 00:48:46 -0700, coosa wrote:
我有一张桌子：
------------------------ ----------------------------
CREATE TABLE CATEGORY（
CATEGORY_ID INTEGER IDENTITY（1， 1）NOT NULL，
CATEGORY_NAME VARCHAR（40）NOT NULL，
PARENT_CATEGORY_ID INTEGER，
CATEGORY_ICON IMAGE，
DEPTH INTEGER，
约束PK__CATEGORY PRIMARY KEY（CATEGORY_ID）
）
GO
--------------------------------- -------------------
我尝试创建此触发器，但我失败了：
--- -------------------------------------------------
CREATE TRIGGER [AI _CATEGORY] ON [dbo]。[CATEGORY]
FOR INSERT
AS /> DECLARE @TEMP_ID AS INT，@ COUNTER AS INT
更新AI_CATEGORY
SET @TEMP_ID = CATEGORY_ID ，@ COUNTER = 1
如果PARENT_CATEGORY_ID不为空
BEGIN
@TEMP_ID不为空时
BEGIN
更新AI_CATEGORY
SET @TEMP_ID = PARENT_CATEGORY_ID，
@ COUNTER = @ COUNTER + 1
END
结束
更新类别
设置深度= @COUNTER
------ ----------------------------------------------
使用SQL Server 2000时出现以下错误：
服务器：Msg 207，Level 16，State 3，Procedure AI_CATEGORY，Line 7
无效的列名''PARENT_CATEGORY_ID' '。
任何线索？
Coosa
嗨Coosa，
那里你的代码中有几个问题。
DECLARE @TEMP_ID AS INT，@ COUNTER AS INT
这似乎表明你希望触发器只处理一个
一次排。在SQL Server中，一个触发器在
语句完成后执行一次 - 所以如果一个INSERT语句4在
中插入行一次，则触发器被触发一次，并且处理所有4个新行
（可以从插入的伪表中提取）。
UPDATE AI_CATEGORY
SET @TEMP_ID = CATEGORY_ID，@ COUNTER = 1
首先，您的帖子中没有表格AI_CATEGORY。
其次，您使用更新语句，但您只更改
一些变量。该语句将处理AI_CATEGORY
中的所有行，并为每一行设置两个变量。最后，你已经多次将@ 1
的值分配给@COUNTER，而@TEMP_ID将是最后处理的任何行的
category_id 。这可以从执行变为执行，并且与
（或行集合）无关>
导致此触发器触发。
如果PARENT_CATEGORY_ID不为空
这是非法语法。将PARENT_CATEGORY_ID替换为本地
变量或只返回一个值的查询，以便通过
语法检查。
UPDATE AI_CATEGORY
SET @TEMP_ID = PARENT_CATEGORY_ID，
@ COUNTER = @COUNTER + 1
与上一个声明一样，这将处理来自
$ b $的所有行b AI_CATEGORY。 @COUNTER的值会快速增加（对于AI_CATEGORY中的每一行，它将增加1
）。并且@TEMP_ID将再次
是一个随机选择的PARENT_CATEGORY_ID。行。如果你很幸运，
它将为NULL并且循环将完成。但是很有可能
这不会是NULL - 并且你可能最终等待触发器
陷入循环，直到@COUNTER溢出最大整数值。
UPDATE CATEGORY
SET DEPTH = @COUNTER
I have a table:
----------------------------------------------------
CREATE TABLE CATEGORY (
CATEGORY_ID INTEGER IDENTITY(1,1) NOT NULL,
CATEGORY_NAME VARCHAR(40) NOT NULL,
PARENT_CATEGORY_ID INTEGER,
CATEGORY_ICON IMAGE,
DEPTH INTEGER,
CONSTRAINT PK__CATEGORY PRIMARY KEY (CATEGORY_ID)
)
GO
----------------------------------------------------
and i try to create this trigger, but i fail:
----------------------------------------------------
CREATE TRIGGER [AI_CATEGORY] ON [dbo].[CATEGORY]
FOR INSERT
AS
DECLARE @TEMP_ID AS INT, @COUNTER AS INT
UPDATE AI_CATEGORY
SET @TEMP_ID = CATEGORY_ID, @COUNTER = 1
IF PARENT_CATEGORY_ID IS NOT NULL
BEGIN
WHILE @TEMP_ID IS NOT NULL
BEGIN
UPDATE AI_CATEGORY
SET @TEMP_ID = PARENT_CATEGORY_ID,
@COUNTER = @COUNTER + 1
END
END
UPDATE CATEGORY
SET DEPTH = @COUNTER
----------------------------------------------------
And i get the following Error using SQL Server 2000:
Server: Msg 207, Level 16, State 3, Procedure AI_CATEGORY, Line 7
Invalid column name ''PARENT_CATEGORY_ID''.
Any clue?
Coosa
Hi Coosa,
There are several problems in your code.
DECLARE @TEMP_ID AS INT, @COUNTER AS INT
This seems to indicate that you expect the trigger to process only one
row at a time. In SQL Server, a trigger is executed once after a
statement is finished - so if an INSERT statement 4 inserts rows at
once, the trigger is fired once, and has to process all 4 new rows
(which can be extracted from the inserted pseudo-table).
UPDATE AI_CATEGORY
SET @TEMP_ID = CATEGORY_ID, @COUNTER = 1
First, there is no table AI_CATEGORY in your post.
Second, you use an update statement, but you only change the values of
some variables. This statement will process all rows from AI_CATEGORY
and set both variables for each row. In the end, you''ll have assigned
the value 1 to @COUNTER numerous times, and @TEMP_ID will be the
category_id of whatever row happens to be processed last. This can
change from execution to execution, and will have no relation to the row
(or collection of rows) that was/were inserted in the statement that
caused this trigger to fire.
IF PARENT_CATEGORY_ID IS NOT NULL
This is illegal syntax. Replace PARENT_CATEGORY_ID with either a local
variable or a query that returns exactly one value in order to pass the
syntax check.
UPDATE AI_CATEGORY
SET @TEMP_ID = PARENT_CATEGORY_ID,
@COUNTER = @COUNTER + 1
Like the previous statement, this will process all rows from
AI_CATEGORY. The value of @COUNTER wil increase rapidly (it will be
incremented by 1 for each row in AI_CATEGORY). And @TEMP_ID will again
be the PARENT_CATEGORY_ID of one "randomly chosen" row. If you''re lucky,
it''ll be NULL and the loop will finish. But there''s a good chance that
this will not be NULL - and you might end up waiting while the trigger
is stuck in a loop, until @COUNTER overflows the maximum integer value.
UPDATE CATEGORY
SET DEPTH = @COUNTER
这会将DEPTH列设置为相同的值
CATEGORY表中的所有行。我怀疑这是不是你想要的。
我可以帮助你改进你的代码，但老实说我不知道是什么
确切地说你正在努力实现。请为您的其他表（AI_CATEGORY）发布CREATE TABLE
语句，为两个表发布一些示例
数据（作为INSERT语句），然后向我们展示一个示例
INSERT操作和触发器执行的预期结果。对实际业务问题的简要说明也许有帮助。
Best，Hugo
-
（删除_NO_和_SPAM_以获取我的电子邮件地址）
This will set the DEPTH column to the same value in all rows in the
CATEGORY table. I doubt if that''s what you want.
I could help you to improve your code, but I honestly don''t kknow what
exactly you are trying to achieve. Please post the CREATE TABLE
statement for your other table (AI_CATEGORY) as well, post some sample
data (as INSERT statements) for both tables, then show us an example
INSERT operation and the expected result from the trigger execution. A
brief description of the actual business problem might help as well.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
啊，谢谢，
我修改为以下内容：
------------------------------- -------------------------------------------------- -----------
创建触发器[AI_CATEGORY] ON [dbo]。[CATEGORY]
FOR INSERT
AS
DECLARE @TEMP_ID AS INT，
@COUNTER AS INT，
@P_ID AS INT
SELECT @TEMP_ID = CATEGORY_ID，
@COUNTER = 1，
@P_ID = PARENT_CATEGORY_ID
FROM INSERTED
如果@P_ID不是空白
BEGIN
WHILE @TEMP_ID不为空
BEGIN
SELECT @TEMP_ID = @P_ID，
@COUNTER = @COUNTER + 1
来自INSERTED
结束
结束
更新类别
SET DEPTH = @ COUNTER
--------------------------- -------------------------------------------------- ---------------
现在至少当我检查语法时，SQL Server接受它并且我可以
然后将其保存在触发器下。
插入一些记录时，只要父类别为空，
它会将depthes更新为1，否则它会挂起，如果父类别
有些价值。
嗯，也许uc帮我一个想法，我需要跟踪
深度层次结构。
这就是一个例子：
CATEGORY_ID CATEGORY_NAME PARENT_CATEGORY_ID DEPTH
---------- -------------------------------------------
29 PC< NULL> 1
30 MOBILES< NULL> 1
32 DIGITAL CAMERAS< NULL> 1
33网络29 2
35音频29 2
36视频29 2
37音频卡35 3
38 IDE智能卡37 4
---------------------------- -------------------------
因此，当我插入记录时，我将深度留空以接受null
值，但在我插入一些记录之后，我希望触发器被触发
来自动检测每个类别的深度并更新这个
深度。
有什么想法吗？我不知道代码中有什么问题，但是因为它在运行时挂起，所以它定义为语义错误。
Coosa
Ah thanks,
I modified to the following:
--------------------------------------------------------------------------------------------
CREATE TRIGGER [AI_CATEGORY] ON [dbo].[CATEGORY]
FOR INSERT
AS
DECLARE @TEMP_ID AS INT,
@COUNTER AS INT,
@P_ID AS INT
SELECT @TEMP_ID = CATEGORY_ID,
@COUNTER = 1,
@P_ID = PARENT_CATEGORY_ID
FROM INSERTED
IF @P_ID IS NOT NULL
BEGIN
WHILE @TEMP_ID IS NOT NULL
BEGIN
SELECT @TEMP_ID = @P_ID,
@COUNTER = @COUNTER + 1
FROM INSERTED
END
END
UPDATE CATEGORY
SET DEPTH = @COUNTER
--------------------------------------------------------------------------------------------
Now at least when i check the syntax, SQL Server accepts it and i can
then save it under the triggers.
When inserting some records, as long the parent categories are nulls,
it updates the depthes to 1, else it hangs, if the parent category has
some value.
Hmm, maybe u c an help me with an idea, i need to keep track of the
depth of hierarchy.
It means as an example:
CATEGORY_ID CATEGORY_NAME PARENT_CATEGORY_ID DEPTH
-----------------------------------------------------
29 PC <NULL> 1
30 MOBILES <NULL> 1
32 DIGITAL CAMERAS <NULL> 1
33 NETWORKS 29 2
35 AUDIO 29 2
36 VIDEO 29 2
37 AUDIO CARDS 35 3
38 IDE AUDIO CARDS 37 4
-----------------------------------------------------
So when I insert records, i leave the depth empty to accept null
values, but after i insert some records, i want the trigger to be fired
to automatically detect the depth of each category and update this
depth.
Any idea? I don''t know what''s wrong in the code, but it''s definetely a
semantic error since it hangs in run time.
Coosa
这篇关于触发错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
