|
|
Posted by Erland Sommarskog on 05/28/05 19:44
coosa (coosa76@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, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|