We help IT Professionals succeed at work.

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
Comment
Watch Question

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

Commented:
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

Author

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

Commented:
Are there Delete Triggers on the Asset and Location Tables?
--
JimFive

Commented:
The only thing that seems odd to me is the lack of:
BEGIN
...
END
around your code.

Author

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
id or object id in the delete from asset , location ?

waiting for your reply
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
great. hope you got it running now :)
Note to JimFive:

Begin/End are not required in triggers or stored procedures.  Only User defined functions.
waiting for you rreply

Commented:
Closed, 500 points refunded.
Vee_Mod
Community Support Moderator

Explore More ContentExplore courses, solutions, and other research materials related to this topic.