Solved

INSTEAD OF DELETE trigger or cascading ?

Posted on 2003-11-05
6
668 Views
Last Modified: 2012-08-13
I have a problem with a trigger I had implemented as a FOR DELETE trigger.
I need to remove dependent records before executing the DELETE.  This is true for the business model as well as database integrity.  Due to the foreign key constraint in place to prevent orphaning the dependent records, the delete fails so the trigger which deletes the dependent records never executes.  I am thinking of changing the trigger to an INSTEAD OF DELETE trigger which would include deletion of the dependent record first then the base record.  

I have 3 related questions:
  Will the base record be in the DELETED table throughout the duration of the trigger?
  I read that unlike an AFTER trigger, the INSTEAD OF trigger can include the action that it is instead of without recursion?  In other words if in my trigger I perform the same delete that it is instead of, it won't try to delete it twice.
  And third, would cascading delete be preferable?  If so, how would I implement this instead? I prefer to use the Enterprise Manager GUI if possible.

Thanks,
joefunsmith

0
Comment
Question by:joefunsmith
  • 3
  • 2
6 Comments
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 125 total points
ID: 9689803
1) yes until you delete it
2) correct...
3) perhaps ... if you have the cascade delete then you can't have an instead of trigger on the table
     but your instead of trigger should usually be created on views rather than tables anyway...

good luck.
0
 
LVL 15

Accepted Solution

by:
namasi_navaretnam earned 125 total points
ID: 9689935
1) Yes
2) Yes.

Delete table will have more than one record if batch delete is performed.
(Batch delete is a sql that deletes more than one row at once.)
Example:
DELETE FROM MyTable where status = 1

If there are many records with status = 1 then delete trigger will fire only once and all the deleted records will be placed into deleted table

So, when you delete join delete table with child table using primary keys

Example

DELETE ChildTable
FROM deleted d, ChildTable c
where d.primarykey = c.primarykey


3) Cascade delete Will work. You have more control if you delete child records yourself.

0
 
LVL 2

Author Comment

by:joefunsmith
ID: 9690156
lowfatspread:
 
  >  1) yes until you delete it
 
  i assume that what you mean is, when, inside the trigger, I delete the record that caused the trigger to execute, that record is no longer in the DELETED table? or when trigger execution ends?
------------------------------------------------------------------
namasi_navaretnam:

  re: Multiple records in the DELETED table,  Thanks for reminding me of that.
------------------------------------------------------------------
re: cascade delete vs. instead of trigger

  good point re: control.  I think I will stick with the instead of trigger because  I could see possible adding something to it in the future such as causing an insert on an audit trail archive table.
  Just for grins, I am guessing the cascade delete is more efficient even assuming no additional functionality?  The table I am performing this on has 15,000 records, will probably double in size over the next six months, and has a composite, indexed primary key.

Thanks,
joefunsmith
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9690213
1) sorry I'm not sure if you can remove rows from the DELETED / INSERTED Tables
    i was referring (as i thought, you where) to the base table on which the originating
    delete action was raised....
    what ever processing you initiate in the trigger and through any subsequent nested
    trigger levels the record remains until "removed"


hope that clear...
i feel tired.
;-)
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9690324
lowfatspread,

People set them up and you know them down.

Tired..You must be feeling very enegetic with all those big points you scoring today....

:)
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9690330
I meant to say,

People set them up and you knock them down.  
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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…

760 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now