?
Solved

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

Posted on 2007-10-01
8
Medium Priority
?
234 Views
Last Modified: 2010-03-19
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
Comment
Question by:picsnet
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 18

Expert Comment

by:Yveau
ID: 19992904
give us the table def and we will try ...
0
 
LVL 18

Expert Comment

by:Yveau
ID: 19992906
oops, id and parent id are the key columns I see :-)
Hang on ...
0
 
LVL 11

Assisted Solution

by:Swindle
Swindle earned 800 total points
ID: 19992943
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

Expert Comment

by:Yveau
ID: 19992946
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
 
LVL 1

Author Comment

by:picsnet
ID: 19993177
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
 
LVL 14

Accepted Solution

by:
ajitha75 earned 1200 total points
ID: 19993238
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
 
LVL 1

Author Comment

by:picsnet
ID: 19993280
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
 
LVL 11

Expert Comment

by:Swindle
ID: 19993723
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

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question