Posted by Hugo Kornelis on 05/25/05 12:57

On 25 May 2005 00:48:46 -0700, coosa wrote:

>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

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)

[Back to original message]