Trigger Error

[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

[teddysnips]

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

[Hugo Kornelis]

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)

[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

[coosa]

Thanks for your reply, please refer to the reply i wrote to Edward.
I'm new in databases and take me as a newbie; what i want is to keep
track of the depth of each node.
In this case i illustrated an example when i replied to Edward. Google
some how displayed the table not as expected, but u could see that i
want to keep track of the depth of each category relating to its
parents. As a root category, the depth will be 1 and this depth will
increment for any next subcategory.
Finally, there is no AI_Category table, it's the name of the trigger!
when i wrote set [...] in the trigger changing the local variable, i
received errors, but when i changed it to update the trigger itself, it
accepted, so i thought it's a way sql server works! :-)

[coosa]

To illustrate a pseudo code, i attempt the following:
1.) Iterate through all rows, row by row
2.) create a temporary variable to equal the current id
3.) Begin a loop in each row
4.) create a counter and initialize to equal 1
5.) For each row, set the temporary variable to equal the parent id (as
linked lists)
6.) increment the counter
7.) End the loop if the temporary variable is null (means it reached
the root category that has no further parents)
8.) Update the depth of that particular row to equal the counter
9.) continue to iterate through the rest of all remaining rows

How can i do it? :-)

Thanks in advance

[Erland Sommarskog]

coosa (co*****@gmail.com) writes:

To illustrate a pseudo code, i attempt the following:
1.) Iterate through all rows, row by row
2.) create a temporary variable to equal the current id
3.) Begin a loop in each row
4.) create a counter and initialize to equal 1
5.) For each row, set the temporary variable to equal the parent id (as
linked lists)
6.) increment the counter
7.) End the loop if the temporary variable is null (means it reached
the root category that has no further parents)
8.) Update the depth of that particular row to equal the counter
9.) continue to iterate through the rest of all remaining rows

How can i do it? :-)

The standard recomendation is that you post:

o CREATE TABLE statement for your table(s), possibly simplified.
o INSERT statements with sample data.
o The desired result given the sample.
o A short narrative that explains what you are trying to achieve from
a busniess-rules perspective.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[coosa]

Beside, the trigger code is modfied now, still i run into hanging in
run time due too the loop.

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,
@P_ID = PARENT_CATEGORY_ID,
@COUNTER = 1
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
IF @TEMP_ID IS NULL
BREAK
END
END

UPDATE CATEGORY
SET DEPTH = @COUNTER

For this code, the trigger works fine if a category has a null
parent_categor_id, but sql server hangs if there are values in the
parent_category_id, certainly because of the loop.

Coosa

[coosa]

i did, check my table and other replies from begin of this post

[Erland Sommarskog]

coosa (co*****@gmail.com) writes:

Beside, the trigger code is modfied now, still i run into hanging in
run time due too the loop.
Of course:
WHILE @TEMP_ID IS NOT NULL
BEGIN
SELECT @TEMP_ID = @P_ID,
@COUNTER = @COUNTER + 1
FROM INSERTED
IF @TEMP_ID IS NULL
BREAK
END
INSERTED is not changing for the duration of the the trigger, so this is
indeed an infinite loop. You are reading the same ID over and over
again.

I repeat the request from the previous post:

The standard recomendation is that you post:

o CREATE TABLE statement for your table(s), possibly simplified.
o INSERT statements with sample data.
o The desired result given the sample.
o A short narrative that explains what you are trying to achieve from
a busniess-rules perspective.

In another post, you said:
i did, check my table and other replies from begin of this post

CREATE TABLE was there, but the other points on my list are missing.
Of course, it is up to you, since you are the one who is asking for
help. If you prefer that people help you based only on guesses on
what you are trying to do and why, the quality of the response will
be in par with this. If we don't know what you want to do, we can't
tell you how to do it.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[coosa]

Man .. and I repeat again that u should check the root post where i
posted my code to craete the table! :-) That satisfies:
o CREATE TABLE statement for your table(s), possibly simplified
If u check the table then u'll see what's default and what can accept
null values and what not, which satisfies:
o INSERT statements with sample data.
For:
o The desired result given the sample.
If you checked my reply to Edward, You'll see that I provided a sample
output table how I wish it to appear!
For your last one:
o A short narrative that explains what you are trying to achieve from
a busniess-rules perspective, check the remaining replies!

In simple words, you don't READ what I write!
You posted twice complaining and analyzing, give me solutions! don't
give me problems!

[coosa]

Any way it's solved now and i shall post for Edward and others who
might be interested:
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 CATEGORY
AFTER INSERT AS
DECLARE @TEMP_ID AS INT
DECLARE @COUNTER AS INT
SET @COUNTER = 1
SELECT @TEMP_ID = PARENT_CATEGORY_ID
FROM inserted
IF @TEMP_ID IS NOT NULL
BEGIN
WHILE @TEMP_ID IS NOT NULL
BEGIN
SET @COUNTER = @COUNTER + 1
SELECT @TEMP_ID = PARENT_CATEGORY_ID
FROM CATEGORY
WHERE CATEGORY_ID = @TEMP_ID
END
END
UPDATE CATEGORY
SET DEPTH = @COUNTER
WHERE CATEGORY_ID = @@IDENTITY

[Erland Sommarskog]

coosa (co*****@gmail.com) writes:

Man .. and I repeat again that u should check the root post where i
posted my code to craete the table! :-) That satisfies:
o CREATE TABLE statement for your table(s), possibly simplified
If u check the table then u'll see what's default and what can accept
null values and what not, which satisfies:
o INSERT statements with sample data.
For:
o The desired result given the sample.
If you checked my reply to Edward, You'll see that I provided a sample
output table how I wish it to appear!
For your last one:
o A short narrative that explains what you are trying to achieve from
a busniess-rules perspective, check the remaining replies!

In simple words, you don't READ what I write!
You posted twice complaining and analyzing, give me solutions! don't
give me problems!

Permit me first to point out that in these newsgroups, you never get
less help than what you pay for. Most of the time you get more.

I found your old post to Edward, and there was some sample data, and
enough information to propose a solution. However, since the data
was not in form of INSERT statements, the solution is not tested.

Unless, I am missing something essential, your trigger could simply
be coded as

CREATE TRIGGER category_tri ON CATEGORY FOR INSERT AS
UPDATE CATEGORY
SET DEPTH = coalesce(p.DEPTH, 0) + 1
FROM CATEGORY c
JOIN inserted i ON c.CATEGORY_ID = i.CATEGORY_ID
LEFT JOIN CATEGORY p ON i.PARENT_CATEGORY_ID = p.CATEGORY_ID

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Hugo Kornelis]

On 26 May 2005 00:49:31 -0700, coosa wrote:

Any way it's solved now and i shall post for Edward and others who
might be interested:

(snip)

Hi coosa,

There are still a few problems with your code:

1. It won't work properly on multi-row inserts;
2. If you change a row, the calculated depth will be wrong for all
dependant rows.

Instead of storing a derived result, it's usually much better to
calculate it when querying the data. In this case, the easiest solution
is probably a user-defined function that traverses the tree to find the
depth (much like the algorithm you now use in your trigger). Then, you
can call that UDF when querying the data.

Another option would be to reconsider the model you have chosen to use
to store the tree. Your model is called the adjacency list model, and
it's commonly used because it is very intuitive. But several other
models are better suited for use in a relational database. The nested
sets model is the best known alternative. If you google for nested sets
model, you'll probably find some good examples. (Or you can buy a copy
of Trees and Hierarchies in SQL, by Joe Celko).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[coosa]

Ahh Thanks alot; Indeed i never thought about multiple inserts and i'm
not experienced in databases. Actually i'm working on my first big
project and before i started i tried to achieve a good model. Celko
replied to me in one of the forums and suggested the same. However, due
too my unexperience, the nested model seemed some how complex and i
checked out an article in http://www.sqlteam.com/item.asp?ItemID=8866
and i found the idea pretty simple :-)
Now I have to test multiple inserts as you said.
My question now, when i insert multiple records, isn't the trigger
still fired after each single insert? and arn't inserts done
Simultaneously in sequence order so that still one record a time is
inserted?
Could you tell me more about that please?

Thanks in Advance

[Hugo Kornelis]

On 26 May 2005 17:08:00 -0700, coosa wrote:

My question now, when i insert multiple records, isn't the trigger
still fired after each single insert?

Hi coosa,

Only if you use seperate insert statements. If you use something like

INSERT INTO MyTable (Col1, Col2, ...)
SELECT Col1, Col2, ...
FROM OtherTable
WHERE ....

then the trigger will fire once, regardless of whether the number of
rows inserted is zero, one, two or 3,000,000,000.

I stick to my advice: remove the derived column, and use a user-defined
function to calculate the depth when you retrieve data. Or, when you
don't want to do that, check out the suggestion posted by Erland: it
will work fone, even for multi-row inserts, as long as you never insert
both a parent and a child in the same statement (and it's not very hard
to adapt the code to cater for that possibility as well).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Erland Sommarskog]

Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:

I stick to my advice: remove the derived column, and use a user-defined
function to calculate the depth when you retrieve data.
Permit me to bump in and say that this is a trade-off on where you want
to take the performance penalty. However, a computed column would have
to traverse the tree from to bottom each time, whereas the trigger only
needs to access the parent level.

SELECT * FROM tbl

with a UDF call for tree-traversal for each row is likely to perform
poorly.

So my vote goes for the column. I think I would go for it, if would
have reasons to move around things in the tree, although it would be
bit hairly to maintain this column,
it will work fone, even for multi-row inserts, as long as you never
insert both a parent and a child in the same statement

Hey, the primary key is an IDENTITY column, so he can't do that!

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Hugo Kornelis]

On Fri, 27 May 2005 20:56:15 +0000 (UTC), Erland Sommarskog wrote:

SELECT * FROM tbl

with a UDF call for tree-traversal for each row is likely to perform
poorly.

So my vote goes for the column.

Hi Erland,

I agree that it's a trade-off. And there is no general "correct" answer,
that depends on the individual situation. How often is the depth
queried? How often does the tree change? Are these changes only
additions and deletions at the leaf level, or are other changes possible
as well?

it will work fone, even for multi-row inserts, as long as you never
insert both a parent and a child in the same statement

Hey, the primary key is an IDENTITY column, so he can't do that!

Ah, you're right - I had missed that.

So that would be the next thing to fix: define and declare the natural
key. :)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[coosa]

Thanks pal :-)

[coosa]

However, It gives me errors that category is ambigious

[Erland Sommarskog]

coosa (co*****@gmail.com) writes:

However, It gives me errors that category is ambigious

Correct. Change "UPDATE CATEGORY" to "UPDATE c". When a table appears
more than once in an UPDATE/DELETE statement you must use the alias
after DELETE, not the table name.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[coosa]

Ah thanks, it worked fine now.
One more thing, hmm, well for update, i read the maanual of SQL Server
2000 and i found a little about the Inserted and Deleted Scan
Operators; however, there is nothing called "Updated". So when i want
to create a trigger which gets fired after an update such as I change
the parent category id of one item which will by it self effect all the
sub categories dependant on it and so also the depth, so how do i do
the same for an update trigger?

[Erland Sommarskog]

coosa (co*****@gmail.com) writes:

Ah thanks, it worked fine now.
One more thing, hmm, well for update, i read the maanual of SQL Server
2000 and i found a little about the Inserted and Deleted Scan
Operators; however, there is nothing called "Updated". So when i want
to create a trigger which gets fired after an update such as I change
the parent category id of one item which will by it self effect all the
sub categories dependant on it and so also the depth, so how do i do
the same for an update trigger?

After an UPDATE statement, both the "inserted" and "deleted" tables
are popoulated, so that's the easy part. ("deleted" holds the updated
rows as they were before the UPDATE, "inserted" the new version.)

What is a little more ioteresting is to actually propagate the changes
futher down the tree. Best is probably to assemble the ids of all
affected sub-nodes in a table variable, and then update all accordingly.
In SQL 2005 there is support for recursive queries, but in SQL 2000 you
would have loop to find the sub-nodes.

It could be worth mentioning a little of how triggers nest here. If you
in a trigger update another table, and that table has a trigger, that
trigger fires, unless the server-wide configuration option "nested
triggers" is off (it's on by default). If the trigger updates its own table,
the trigger does not refire, unless the database option RECURSIVE_TRIGGERS
is on. (It's off by default.) Confusing? Yeah, but most of the time,
this is what you want.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[coosa]

Hmm, I tried but still it doesn't work for update.
Can u give me a hint how to iterate through all records in a table; i
mean to begin from row 0 till last row, and then in each row do some
thing.

I'm familiar with normal programming style such as:
if declaring an array or vector in C++ such as vector <int> my_vec;
then after some operations, i want to iterate through that vector, then
i would simply do like this:
for (int i = 0; i < my_vec.size(); i++)
{
..
..
..
}

So in my case here in SQL, is it some thing like this?
DECLARE @MAX_ROWS AS INT
DECLARE @ROW_COUNTER AS INT
SET @ROW_COUNTER = 0
SELECT @MAX_ROWS = COUNT (*)
FROM CATEGORY
WHILE @ROW_COUNTER < @MAX_ROWS
BEGIN
..
..
..
END
Or is there a faster convenient way to such loops?

[coosa]

I tried some thing like that too:
DECLARE @TEMP_ID AS INT,
@COUNTER AS INT
DECLARE MyCursor CURSOR FOR
SELECT CATEGORY_ID,
PARENT_CATEGORY_ID,
DEPTH
FROM CATEGORY
OPEN MyCursor
FETCH MyCursor INTO @TEMP_ID, @COUNTER
SET @COUNTER = 1
SET @TEMP_ID = PARENT_CATEGORY_ID
IF @TEMP_ID IS NOT NULL
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH MyCursor INTO @TEMP_ID, @COUNTER
WHILE @TEMP_ID IS NOT NULL
BEGIN
SET @COUNTER = @COUNTER + 1
SELECT @TEMP_ID = PARENT_CATEGORY_ID
FROM CATEGORY
WHERE CATEGORY_ID = @TEMP_ID
END
UPDATE CATEGORY
SET DEPTH = @COUNTER
END
CLOSE MyCursor
DEALLOCATE MyCursor

I actually tried to modify a code i saw online from
http://www.sqlservercentral.com/colu...server2000.asp
but i get this error when i try to run it:
Server: Msg 207, Level 16, State 3, Line 7
Invalid column name 'PARENT_CATEGORY_ID'.

Any Idea how to do this?

[coosa]

Hi again;

I modified some how the update trigger and it works fine if i create
that trigger on a later stage; meaning .. when i create the datatabase
and tables, create the after insert trigger then fill some values,
every thing goes fine and fast. When I after inserting the records,
create the after update trigger, then i change some node connections,
then the depth is changed automatically not just for that row but also
for all affected rows that are children of this changed parent row.
However, If I create the after update trigger before inserting the
records, it takes so much time when i parse my sql code to create the
tables and insert records and suddenly SQL Server informs me that I'm
out of resource!
Here are my two triggers again with the table structure:
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 CATEGORY
AFTER INSERT AS
UPDATE C
SET DEPTH = coalesce(P.DEPTH, 0) + 1
FROM CATEGORY C
JOIN inserted I ON C.CATEGORY_ID = I.CATEGORY_ID
LEFT JOIN CATEGORY P ON I.PARENT_CATEGORY_ID = P.CATEGORY_ID
GO
CREATE TRIGGER AU_CATEGORY
ON CATEGORY
AFTER UPDATE AS
DECLARE Iterator CURSOR FOR SELECT * FROM CATEGORY
OPEN Iterator
FETCH NEXT FROM Iterator
BEGIN
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE C
SET C.DEPTH = coalesce(P.DEPTH, 0) + 1
FROM CATEGORY C
JOIN CATEGORY P
ON C.PARENT_CATEGORY_ID = P.CATEGORY_ID
FETCH NEXT FROM Iterator
END
END
CLOSE Iterator
DEALLOCATE Iterator
GO

[coosa]

Hi again;

Here are my two triggers again with the table structure:

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 CATEGORY
AFTER INSERT AS
UPDATE C
SET DEPTH = coalesce(P.DEPTH, 0) + 1
FROM CATEGORY C
JOIN inserted I ON C.CATEGORY_ID = I.CATEGORY_ID
LEFT JOIN CATEGORY P ON I.PARENT_CATEGORY_ID = P.CATEGORY_ID
GO
CREATE TRIGGER AU_CATEGORY
ON CATEGORY
AFTER UPDATE AS
DECLARE Iterator CURSOR FOR SELECT * FROM CATEGORY
OPEN Iterator
FETCH NEXT FROM Iterator
BEGIN
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE C
SET C.DEPTH = coalesce(P.DEPTH, 0) + 1
FROM CATEGORY C
JOIN CATEGORY P
ON C.PARENT_CATEGORY_ID = P.CATEGORY_ID
FETCH NEXT FROM Iterator
END
END
CLOSE Iterator
DEALLOCATE Iterator
GO

The problem is, when I parse the AU_CATEGORY trigger before inserting
some records, SQL hangs and informs me that I'm out of resource.
If I create this trigger after inserting some records, then when I try
to change the parent_category_id of one row, it can't be changed;
automatically restored as before such as if i change from 16 to 4, it
restores back to 16 as if i did nothing.
The interesting thing is, when i don't have the trigger for update,
then i change manually one parent_category_id and then run the content
of the trigger:

DECLARE Iterator CURSOR FOR SELECT * FROM CATEGORY
OPEN Iterator
FETCH NEXT FROM Iterator
BEGIN
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE C
SET C.DEPTH = coalesce(P.DEPTH, 0) + 1
FROM CATEGORY C
JOIN CATEGORY P
ON C.PARENT_CATEGORY_ID = P.CATEGORY_ID
FETCH NEXT FROM Iterator
END
END
CLOSE Iterator
DEALLOCATE Iterator
GO

Then it works fine and updates the depth of all affected children of
that originally changed row.
Any clue?

Thanks in Advance

[Erland Sommarskog]

coosa (co*****@gmail.com) writes:

CREATE TRIGGER AU_CATEGORY
ON CATEGORY
AFTER UPDATE AS
DECLARE Iterator CURSOR FOR SELECT * FROM CATEGORY
OPEN Iterator
FETCH NEXT FROM Iterator
BEGIN
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE C
SET C.DEPTH = coalesce(P.DEPTH, 0) + 1
FROM CATEGORY C
JOIN CATEGORY P
ON C.PARENT_CATEGORY_ID = P.CATEGORY_ID
FETCH NEXT FROM Iterator
END
END
CLOSE Iterator
DEALLOCATE Iterator
GO

This is not a very good trigger. First of all, since you use FETCH
without an INTO clause, each FETCH statement will produce a result
set back to the client, and that is not what you want.

But you also need to use the "inserted" table, to delimit the update
to the nodes which are below the one you updated.

In fact, I don't think you should have any cursor at all. Rather it
would look something like this:

DECELARE @affected TABLE (category_id int NOT NULL)
INSERT @affected(category_id)
SELECT category_id
FROM inserted

WHILE @@rowcount > 0
BEGIN
INSERT @affected (category_id)
SELECT category_id
FROM categories c
WHERE EXISTS (SELECT *
FROM @affected a
WHERE a.category_id = c.category_id)
AND NOT EXISTS (SELECT *
FROM @affected a
WHERE a.category_id = c.category_id)
END

Then @affected holds the ids to update.

But this a fairly rough sketch, to give you an idea of where to go. I
might have more time to look into your posts tonight.

In another post, you mentioned that you are more used to program in C++.
This means that you have a lot to unlearn. Loops is something you do a
lot less often in SQL.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[coosa]

Can u explain your code? especially the part:
DECELARE @affected TABLE (category_id int NOT NULL)
INSERT @affected(category_id)
SELECT category_id
FROM inserted

[Erland Sommarskog]

coosa (co*****@gmail.com) writes:

Can u explain your code? especially the part:
DECELARE @affected TABLE (category_id int NOT NULL)
INSERT @affected(category_id)
SELECT category_id
FROM inserted

First I declare a table variable. I could also have created a temp
table, but my experience that for triggers table variables are better.
(Whether to use temp tables or table variables is a delicate choice.
Sometimes, table variables are better, sometimes temp tables are
better.)

Next I insert the id:s of all rows that were affected by the UPDATE
statement.

You can read about table variables in Books Online inder the DECLARE topic
in the Transact-SQL Reference. In the book "Creating and maintaing
Databases", there is a section "Enforcing Business Rules with Triggers".

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

So here is a trigger that is tested and tried. Still there is one thing
missing. What happens if you make a category a child to one of its children?
I guess this should be forbidden?

I should say that I don't think the update trigger is the most effecient
way to do this. But at least it's faster than travering the entire table.

CREATE TABLE CATEGORY
(
CATEGORY_ID INTEGER NOT NULL,
CATEGORY_NAME VARCHAR(40) NOT NULL,
PARENT_CATEGORY_ID INTEGER,
DEPTH INTEGER,
CONSTRAINT PK__CATEGORY PRIMARY KEY (CATEGORY_ID)
)
go
CREATE TRIGGER AI_CATEGORY
ON CATEGORY
AFTER INSERT AS
UPDATE C
SET DEPTH = coalesce(P.DEPTH, 0) + 1
FROM CATEGORY C
JOIN inserted I ON C.CATEGORY_ID = I.CATEGORY_ID
LEFT JOIN CATEGORY P ON I.PARENT_CATEGORY_ID = P.CATEGORY_ID
GO
CREATE TRIGGER cat_upd_tri ON CATEGORY FOR UPDATE AS

DECLARE @lvl int,
@rowc int

DECLARE @affected TABLE (category_id int NOT NULL PRIMARY KEY,
lvl int NOT NULL)

SELECT @lvl = 1

INSERT @affected(category_id, lvl)
SELECT CATEGORY_ID, @lvl
FROM inserted
SELECT @rowc = @@rowcount

WHILE @rowc <> 0
BEGIN
UPDATE C
SET DEPTH = coalesce(P.DEPTH, 0) + 1
FROM CATEGORY C
LEFT JOIN CATEGORY P ON C.PARENT_CATEGORY_ID = P.CATEGORY_ID
WHERE EXISTS (SELECT *
FROM @affected a
WHERE C.CATEGORY_ID = a.category_id
AND a.lvl = @lvl)

SELECT @lvl = @lvl + 1

INSERT @affected (category_id, lvl)
SELECT c.CATEGORY_ID, @lvl
FROM CATEGORY c
WHERE EXISTS (SELECT *
FROM @affected a
WHERE a.category_id = c.PARENT_CATEGORY_ID)
AND NOT EXISTS (SELECT *
FROM @affected a
WHERE a.category_id = c.CATEGORY_ID)
SELECT @rowc = @@rowcount
END
go
insert CATEGORY (CATEGORY_ID, CATEGORY_NAME, PARENT_CATEGORY_ID)
VALUES(1, 'Top level', NULL)
insert CATEGORY (CATEGORY_ID, CATEGORY_NAME, PARENT_CATEGORY_ID)
VALUES(2, 'Second level A', 1)
insert CATEGORY (CATEGORY_ID, CATEGORY_NAME, PARENT_CATEGORY_ID)
VALUES(3, 'Second level B', 1)
insert CATEGORY (CATEGORY_ID, CATEGORY_NAME, PARENT_CATEGORY_ID)
VALUES(4, 'Third level A1', 2)
insert CATEGORY (CATEGORY_ID, CATEGORY_NAME, PARENT_CATEGORY_ID)
VALUES(5, 'Third level A2', 2)
insert CATEGORY (CATEGORY_ID, CATEGORY_NAME, PARENT_CATEGORY_ID)
VALUES(6, 'Third level B1', 3)
insert CATEGORY (CATEGORY_ID, CATEGORY_NAME, PARENT_CATEGORY_ID)
VALUES(7, 'Third level B2', 3)
insert CATEGORY (CATEGORY_ID, CATEGORY_NAME, PARENT_CATEGORY_ID)
VALUES(8, 'Next Level', 1)
go
select * from CATEGORY
go
UPDATE CATEGORY
SET PARENT_CATEGORY_ID = 8
WHERE CATEGORY_ID = 2
go
select * from CATEGORY
go
UPDATE CATEGORY
SET PARENT_CATEGORY_ID = 1
WHERE CATEGORY_ID = 2
go
select * from CATEGORY
go
DROP TABLE CATEGORY
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[coosa]

Thanks alot :-) You bothered to write that much; I really appreciate it

[coosa]

May I also know for how long you have been working in the databases
field?

[Erland Sommarskog]

coosa (co*****@gmail.com) writes:

May I also know for how long you have been working in the databases
field?

Tip: don't ask Joe Celko that question. Ask him how long he has been
in the databases column! :-)

I've been in this trade for some time now. My first job that involved
an RDBMS was in 1988, and the RBDMS was DEC/Rdb. I was introduced to
Sybase in 1991, and moved over MS SQL Server which then still much was
of a Sybase clone in 1996.

It's not always good being a old-timer. I was trying to find the answer
to question in another thread, but my script just wouldn't work.
Eventually, I ran it on 6.5, where I got the expected results. MS saw
fit to make a change in SQL 7 without telling me...

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp