Trigger Error

[Hugo Kornelis]

Re: Trigger Error

On 26 May 2005 17:08:00 -0700, coosa wrote:
[color=blue]
>My question now, when i insert multiple records, isn't the trigger
>still fired after each single insert?[/color]

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]

Re: Trigger Error

Hugo Kornelis (hugo@pe_NO_rFa ct.in_SPAM_fo) writes:[color=blue]
> I stick to my advice: remove the derived column, and use a user-defined
> function to calculate the depth when you retrieve data.[/color]

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,
[color=blue]
> 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[/color]

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



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Hugo Kornelis]

Re: Trigger Error

On Fri, 27 May 2005 20:56:15 +0000 (UTC), Erland Sommarskog wrote:
[color=blue]
> 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.[/color]

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?

[color=blue][color=green]
>> 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[/color]
>
>Hey, the primary key is an IDENTITY column, so he can't do that![/color]

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]

Re: Trigger Error

Thanks pal :-)

[coosa]

Re: Trigger Error

However, It gives me errors that category is ambigious

[Erland Sommarskog]

Re: Trigger Error

coosa (coosa76@gmail. com) writes:[color=blue]
> However, It gives me errors that category is ambigious[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[coosa]

Re: Trigger Error

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]

Re: Trigger Error

coosa (coosa76@gmail. com) writes:[color=blue]
> 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?[/color]

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_TRIGG ERS
is on. (It's off by default.) Confusing? Yeah, but most of the time,
this is what you want.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[coosa]

Re: Trigger Error

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]

Re: Trigger Error

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/columnists/aGrinberg/thearrayinsqlserver2000.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_CATEGOR Y_ID'.

Any Idea how to do this?

[coosa]

Re: Trigger Error

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.DEPT H, 0) + 1
FROM CATEGORY C
JOIN inserted I ON C.CATEGORY_ID = I.CATEGORY_ID
LEFT JOIN CATEGORY P ON I.PARENT_CATEGO RY_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.DEPT H, 0) + 1
FROM CATEGORY C
JOIN CATEGORY P
ON C.PARENT_CATEGO RY_ID = P.CATEGORY_ID
FETCH NEXT FROM Iterator
END
END
CLOSE Iterator
DEALLOCATE Iterator
GO

[coosa]

Re: Trigger Error

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.DEPT H, 0) + 1
FROM CATEGORY C
JOIN inserted I ON C.CATEGORY_ID = I.CATEGORY_ID
LEFT JOIN CATEGORY P ON I.PARENT_CATEGO RY_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.DEPT H, 0) + 1
FROM CATEGORY C
JOIN CATEGORY P
ON C.PARENT_CATEGO RY_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.DEPT H, 0) + 1
FROM CATEGORY C
JOIN CATEGORY P
ON C.PARENT_CATEGO RY_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]

Re: Trigger Error

coosa (coosa76@gmail. com) writes:[color=blue]
> 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.DEPT H, 0) + 1
> FROM CATEGORY C
> JOIN CATEGORY P
> ON C.PARENT_CATEGO RY_ID = P.CATEGORY_ID
> FETCH NEXT FROM Iterator
> END
> END
> CLOSE Iterator
> DEALLOCATE Iterator
> GO[/color]

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(categ ory_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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[coosa]

Re: Trigger Error

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

[Erland Sommarskog]

Re: Trigger Error

coosa (coosa76@gmail. com) writes:[color=blue]
> Can u explain your code? especially the part:
> DECELARE @affected TABLE (category_id int NOT NULL)
> INSERT @affected(categ ory_id)
> SELECT category_id
> FROM inserted[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp