Trigger Error

[Erland Sommarskog]

Re: Trigger Error

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.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 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(categ ory_id, lvl)
SELECT CATEGORY_ID, @lvl
FROM inserted
SELECT @rowc = @@rowcount

WHILE @rowc <> 0
BEGIN
UPDATE C
SET DEPTH = coalesce(P.DEPT H, 0) + 1
FROM CATEGORY C
LEFT JOIN CATEGORY P ON C.PARENT_CATEGO RY_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_CATEGO RY_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, 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

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

[coosa]

Re: Trigger Error

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

[Erland Sommarskog]

Re: Trigger Error

coosa (coosa76@gmail. com) writes:[color=blue]
> May I also know for how long you have been working in the databases
> field?[/color]

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

Books Online for SQL Server SP3 at

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