Link to home
Create AccountLog in
Avatar of picsnet
picsnet

asked on

How do I do a delete from a table like this...

I have a table called AssetLocation.  It holds a tree.  It has a field called Id, and one called ParentId.  If you delete a row, then you need to delete all rows that have their parentid equalling that id.  Of course this means recursion.  

I've never written a trigger, and not even sure how to do it.  I figured that I'd say that cause I feel that is what people are going to tell me to do.  I tried database diagrams, but it had the cascade delete options greyed out on this kinda relationship.  

So is this easy or difficult, and an example would be incredibly awesome.  

Tony
Avatar of Yveau
Yveau
Flag of Netherlands image

give us the table def and we will try ...
oops, id and parent id are the key columns I see :-)
Hang on ...
SOLUTION
Avatar of Swindle
Swindle
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Here's an example of a employee table. The query will return the 'level' in the hierarchy. That's the trick, delete records based on the level in the hierarchy.

CREATE TABLE Employees
(
       EmpId      int,
       ReportsTo      int
)
GO

-- Insert some data
INSERT INTO Employees VALUES (1, 2)
INSERT INTO Employees VALUES (2, NULL)
INSERT INTO Employees VALUES (3, 2)
INSERT INTO Employees VALUES (4, 2)
INSERT INTO Employees VALUES (5, 2)
INSERT INTO Employees VALUES (6, 5)
INSERT INTO Employees VALUES (7, 5)
INSERT INTO Employees VALUES (8, 2)
INSERT INTO Employees VALUES (9, 5)

-- create temp table
CREATE TABLE #E
(
       EmpId      int,
       ReportsTo      int,
       HierarchyLevel int
)
GO

declare @level int
select @level = 0

-- insert top level
insert
into    #e
select  *
,       @level
from    employees
where   reportsto is null

while (select count(*) from employees e1 left outer join #e e2 on e1.empid = e2.empid inner join #e e3 on e1.reportsto = e3.empid where e2.empid is null) > 0
begin
        select  @level = @level + 1

        insert
        into    #e
        select  e1.*
        ,       @level
        from    employees e1 left outer join #e e2 on e1.empid = e2.empid inner join #e e3 on e1.reportsto = e3.empid where e2.empid is null
end

select * from #e


Hope this helps ...

Avatar of picsnet
picsnet

ASKER

i think this is close, but i don't know how to reference anything..... From examples I looked up, i know i'm suppose to reference what originally was supposed to happen, but i don't know how...

CREATE TRIGGER DeleteAssetLocationsTrigger ON AssetLocation INSTEAD OF Delete AS
      IF @@rowcount = 0 RETURN         
      Delete From AssetLocation Where ParentId =

GO
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of picsnet

ASKER

Ok it created it with no problem.  Why doesn't it show up in my databasetriggers folder under progammability.  I've also always been confused about why when I right click it that it only says refresh instead of giving creation options.  
the trigger was created on the table, not the database.  Click the plus sign next to the table you put it on and you'll find the "triggers" folder.  It should be in there.