Solved

INSTEAD OF DELETE trigger or cascading ?

Posted on 2003-11-05
6
722 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

617 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