Posted by Erland Sommarskog on 05/26/05 23:57

coosa (coosa76@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, esquel@sommarskog.se

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

[Back to original message]