Solved

INSTEAD OF DELETE trigger or cascading ?

Posted on 2003-11-05
6
716 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
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 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

Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

759 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