RE: INSTEAD OF DELETE Trigger
From: Alejandro Mesa (AlejandroMesa_at_discussions.microsoft.com)
Date: 02/07/05
- Next message: Hugo Kornelis: "Re: INSTEAD OF DELETE Trigger"
- Previous message: Hugo Kornelis: "Re: INSTEAD OF DELETE Trigger"
- In reply to: mekim: "RE: INSTEAD OF DELETE Trigger"
- Next in thread: Hugo Kornelis: "Re: INSTEAD OF DELETE Trigger"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 7 Feb 2005 13:31:01 -0800
See this example. You will get three resuktsets, one from "select * from t1",
another from the statement inside the trigger (select * from deleted) and the
last one from the second "select * from t1 where colA > 0" (this one will be
an empty resultset because all rwos were deleted by the io trigger).
use northwind
go
create table t1 (
colA int not null identity(1, 1) unique,
)
go
create trigger tr_t1_io_del on t1
instead of delete
as
delete t1
select * from deleted as d
go
insert into t1 default values
insert into t1 default values
insert into t1 default values
go
select * from t1
go
delete t1 where colA = 2
go
select * from t1 where colA > 0
go
drop table t1
go
AMB
"mekim" wrote:
> 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
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
- Next message: Hugo Kornelis: "Re: INSTEAD OF DELETE Trigger"
- Previous message: Hugo Kornelis: "Re: INSTEAD OF DELETE Trigger"
- In reply to: mekim: "RE: INSTEAD OF DELETE Trigger"
- Next in thread: Hugo Kornelis: "Re: INSTEAD OF DELETE Trigger"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
