Bug in Trigger... Very small amount of code, but a sorta advanced topic

Here is my trigger:

ALTER TRIGGER [dbo].[DeleteAssetLocationsTrigger] ON [dbo].[AssetLocation] INSTEAD OF Delete AS
     
      Delete From Asset Where Id in (Select ObjectId from Deleted)
      Delete From Location Where Id in (Select ObjectId from Deleted)        
                     Delete From AssetLocation Where ParentId in (Select Id from Deleted)
      Delete From AssetLocation Where Id in (Select Id from Deleted)

AssetLocation is a table that represents a tree.  The asset and location tables are the objects of the tree.  AssetLocation mainly just holds the hierarchy, and the objectId is what it uses to get the details from each object in the hierarchy...

I need a trigger that works like this.  If you delete a node, then it deletes all nodes below it, and its details...  The one I have now seems to be deleting the hierarchy, but it isn't getting the details in the asset and locations tables.  

Tony
LVL 1
picsnetAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Smart_ManCommented:
alter is to change .. , where is your original (create) trigger ?

it is your choice to have a trigger. but the recommendation is to have a pl or a well designed sql statement(s) to achive your target. as triggers is a performance killer for the dbs.

if you are so determant to go on your trigger , please clearify which tables got affected and which is not ? (hierarchy?) ?

waiting for your reply
0
JimFiveCommented:
I would expect these two lines:

      Delete From Asset Where Id in (Select ObjectId from Deleted)
      Delete From Location Where Id in (Select ObjectId from Deleted)

To need different ObjectId's, one for the asset and one for the location.
--
JimFive
0
picsnetAuthor Commented:
umm smart man, change the word alter to the word create, and that is your create statement.  

Its a very simple structure......

         AssetLocation:  id, parentid, objectid, type
                Asset:  objectid
                Location: objectid

It is designed correctly...  The objectid is unique among the tables.... The trigger just isn't right
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

JimFiveCommented:
Are there Delete Triggers on the Asset and Location Tables?
--
JimFive
0
JimFiveCommented:
The only thing that seems odd to me is the lack of:
BEGIN
...
END
around your code.
0
picsnetAuthor Commented:
And heres your answer:

ALTER TRIGGER [dbo].[DeleteAssetLocationsTrigger] ON [dbo].[AssetLocation] For Delete AS
     
      If @@rowcount = 0 return

      Delete From Asset Where Id in (Select ObjectId from Deleted)
      Delete From Location Where Id in (Select ObjectId from Deleted)

      Delete t
      From AssetLocation as t      Join Deleted as d
            on t.parentId = d.id
      Go
0
Smart_ManCommented:
id or object id in the delete from asset , location ?

waiting for your reply
0
picsnetAuthor Commented:
And heres your answer:

ALTER TRIGGER [dbo].[DeleteAssetLocationsTrigger] ON [dbo].[AssetLocation] For Delete AS
     
      If @@rowcount = 0 return

      Delete From Asset Where Id in (Select ObjectId from Deleted)
      Delete From Location Where Id in (Select ObjectId from Deleted)

      Delete t
      From AssetLocation as t      Join Deleted as d
            on t.parentId = d.id
      Go
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Smart_ManCommented:
great. hope you got it running now :)
0
BrandonGalderisiCommented:
Note to JimFive:

Begin/End are not required in triggers or stored procedures.  Only User defined functions.
0
Smart_ManCommented:
waiting for you rreply
0
Vee_ModCommented:
Closed, 500 points refunded.
Vee_Mod
Community Support Moderator
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.