• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

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
0
picsnet
Asked:
picsnet
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
YveauCommented:
give us the table def and we will try ...
0
 
YveauCommented:
oops, id and parent id are the key columns I see :-)
Hang on ...
0
 
SwindleCommented:
The problem is that if it is a true relationship, and you have a constraint set to where you cannot have a child record without a parent record, then attempting to delete a parent record without first deleting the child records will error.  What I would suggest is using an INSTEAD OF trigger.  Basically it would fire if someone attempts to delete a record from your table.  Instead of just deleting the row, you can have the trigger code check if there are child records, delete them first, then delete the parent record.  Here is a link to a good article on triggers, and it discusses INSTEAD OF triggers.

http://msdn.microsoft.com/msdnmag/issues/03/12/DataPoints/#S1
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
YveauCommented:
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 ...

0
 
picsnetAuthor Commented:
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
0
 
ajitha75Commented:
Reference is through keyword deleted (indicates deleted record)

CREATE TRIGGER DeleteAssetLocationsTrigger ON AssetLocation INSTEAD OF Delete AS
      IF @@rowcount = 0 RETURN          
      Delete From AssetLocation Where ParentId in (select id from  deleted)

GO
0
 
picsnetAuthor Commented:
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.  
0
 
SwindleCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now