RE: INSTEAD OF DELETE Trigger

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: mekim (mekim_at_discussions.microsoft.com)
Date: 02/07/05 

Date: Mon, 7 Feb 2005 13:21:05 -0800

Hi Alejandro & Hugo,

Ok - I'll try to explain as clear as possible

The Problem:
I have a TableQueue that Queues records that when I delete those records
from TableQueue I need to do some work

The Solution:
in the "DELETE INSTEAD OF" trigger I "do some work" and then delete the
records from TableQueue that are shown via the "deleted" table

The Puzzle: :-)
It actually appears to work correctly - HOWEVER - and it's a BIG HOWEVER - I
don't understand why the "Delete TableQueue" only deletes the records in the
"deleted" table and not all the records...

You and Hugo CERTAINLY know what you are doing - so I'm even more puzzled
since you both clearly say it that "DELETE TableQueue" in a "DELETE INSTEAD
OF" trigger should delete all the records and not just the ones in the
"deleted" table ...I don't know if this is a bug or undocument feature or
whatever.

Best Regards,
Mekim

p.s. Plz ignore the original "SELECT * FROM TableQueue" ...that was just
plain silly apparently :-)

"Alejandro Mesa" wrote:

> Mekin,
>
> What are you trying to accomplish?, Let us know and may be somthing comes out.
>
> AMB
>
> "mekim" wrote:
>
> > Well...Apparently - as I would expect may I add :-) you are right
> >
> > HOWEVER....
> >
> > DELETE Table1
> >
> > Only deletes the records originally set to delete in Table1 and not ALL the
> > rows...the extra "SELECT * FROM deleted d" was doing exactly ---> nothing :)
> >
> > This is NOT what I would expect - or is it? Plz remember that this is being
> > run within the INSTEAD OF DELETE trigger
> >
> > I can not find any documentation that shows this feature
> >
> > Does this make any sense?
> >
> > Best Regards,
> > Mekim
> >
> >
> > "Alejandro Mesa" wrote:
> >
> > > > In the DELETE INSTEAD OF trigger...."DELETE Table1 SELECT * FROM deleted d"
> > > > seems to delete just the rows that are in "SELECT * FROM deleted d"
> > >
> > > Not really. It is deleting all rows from Table1 and returning a resultset
> > > (rows that were supposed to be deleted) to the client app. SQL server is
> > > processing two sentences instead one.
> > >
> > > 1 - delete table1
> > > 2 - select * from deleted d
> > >
> > >
> > > AMB
> > >
> > > "mekim" wrote:
> > >
> > > > Hi Alejandro,
> > > >
> > > > That's just it...all the rows are NOT being deleted...which is
> > > > correct...since it's suppose to do just that...just the ones that were
> > > > "suppose" to be deleted...
> > > >
> > > > In the DELETE INSTEAD OF trigger...."DELETE Table1 SELECT * FROM deleted d"
> > > > seems to delete just the rows that are in "SELECT * FROM deleted d"
> > > >
> > > > What appears to be the only downside is that a resultset is sent back to QA
> > > > ...which is not big deal...but that's extra unnecessary traffic.
> > > >
> > > > Best Regards,
> > > > Mekim
> > > >
> > > >
> > > > Best Regards,
> > > > Mekim
> > > >
> > > > "Alejandro Mesa" wrote:
> > > >
> > > > > Mekin,
> > > > >
> > > > > If you do not use the WHERE clause, then all rows will be deleted.
> > > > >
> > > > > CREATE TRIGGER Table1_InsteadOfDeleteTrigger
> > > > > ON dbo.Table1
> > > > > INSTEAD OF DELETE
> > > > > as
> > > > > DELETE Table1
> > > > > from deleted
> > > > > go
> > > > >
> > > > > you have to tell sql server which rows.
> > > > >
> > > > > CREATE TRIGGER Table1_InsteadOfDeleteTrigger
> > > > > ON dbo.Table1
> > > > > INSTEAD OF DELETE
> > > > > as
> > > > > DELETE Table1
> > > > > from deleted
> > > > > where table1.colA = deleted.colA
> > > > > go
> > > > >
> > > > >
> > > > > AMB
> > > > >
> > > > >
> > > > > "mekim" wrote:
> > > > >
> > > > > > Hi Alejandro,
> > > > > >
> > > > > > That works as well & without the return from "SELECT * FROM deleted d" into QA
> > > > > >
> > > > > > HOWEVER - Table1 is very large - and I was "assuming" that avoiding the
> > > > > > "where d.pkcol = table1.pkcol" would speed things up
> > > > > >
> > > > > > I don't see the benefit of adding the Where clause - but then again - I'm
> > > > > > not sure that's it's not needed either.
> > > > > >
> > > > > > Best Regards,
> > > > > > Mekim
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Alejandro Mesa" wrote:
> > > > > >
> > > > > > > Try,
> > > > > > >
> > > > > > > CREATE TRIGGER Table1_InsteadOfDeleteTrigger
> > > > > > > ON dbo.Table1
> > > > > > > INSTEAD OF DELETE
> > > > > > > as
> > > > > > > DELETE Table1
> > > > > > > where exists(SELECT * FROM deleted d where d.pkcol = table1.pkcol)
> > > > > > > go
> > > > > > >
> > > > > > >
> > > > > > > AMB
> > > > > > >
> > > > > > > "mekim" wrote:
> > > > > > >
> > > > > > > > Hi All,
> > > > > > > >
> > > > > > > > I am writing my first "INSTEAD OF DELETE" trigger and what I need to do is
> > > > > > > > delete the actual records that would have been deleted if not for the
> > > > > > > > "INSTEAD OF DELETE"
> > > > > > > >
> > > > > > > > My question is that when I run a trigger I get back a result set in QA (I'm
> > > > > > > > not even sure if that matters) and was wondering if there is a better/quicker
> > > > > > > > way than I am doing it - it seems too straight forward for me to have gotten
> > > > > > > > it right :-)
> > > > > > > >
> > > > > > > > the trigger code is listed below
> > > > > > > >
> > > > > > > > Best Regards
> > > > > > > > Mekim
> > > > > > > >
> > > > > > > > CREATE TRIGGER Table1_InsteadOfDeleteTrigger
> > > > > > > > ON dbo.Table1
> > > > > > > > INSTEAD OF DELETE
> > > > > > > > as
> > > > > > > > DELETE Table1 SELECT * FROM deleted d
> > > > > > > >
> > > > > > > >
> > > > > > > >

Relevant Pages

  • Re: INSTEAD OF DELETE Trigger
    ... I replied to Alejandro as per his request for an explaination of what I am ... I have a TableQueue that Queues records that when I delete those records ... OF" trigger should delete all the records and not just the ones in the ... > DELETE Table1 ...
    (microsoft.public.sqlserver.programming)
  • RE: INSTEAD OF DELETE Trigger
    ... an empty resultset because all rwos were deleted by the io trigger). ... "mekim" wrote: ... > I have a TableQueue that Queues records that when I delete those records ... >> AMB ...
    (microsoft.public.sqlserver.programming)