Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

INSTEAD OF DELETE trigger or cascading ?

Posted on 2003-11-05
6
Medium Priority
?
727 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 500 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 500 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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 SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

730 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