RE: INSTEAD OF DELETE Trigger

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Alejandro Mesa (AlejandroMesa_at_discussions.microsoft.com)
Date: 02/07/05 

Date: Mon, 7 Feb 2005 11:09:02 -0800

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
    ... CREATE TRIGGER Table1_InsteadOfDeleteTrigger ... DELETE Table1 ... AMB ... "mekim" wrote: ...
    (microsoft.public.sqlserver.programming)
  • Re: Deleting a record from a database
    ... ALTER TABLE Table1 ... Create a trigger that archives the "deleted file" To do this, ... a table exactly like the original table, but you call it _History. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Key Fields
    ... Just watch out of there's a foreign key that references the table. ... A traditional trigger will ... >> Table1 has an IP Address field ... >> All records in Table2 that have the foreign key set to the original IP ...
    (microsoft.public.sqlserver.programming)
  • Re: Triggers
    ... I have Table1 which is the master table and Table2 which is the ... After that Table2 is dealt with. ... in Table2 come from stored procedure variables. ... What we want it to put a trigger one Table1 to do the insert into ...
    (comp.databases.oracle.server)
  • Re: Can this be done with a trigger?
    ... trigger fire, then go back and update the row..... ... Create a View on Table1 selecting * from Table1 and also adding a null ... Change your PL/SQL procedure to insert into the view instead of directly ... out for the SOURCE column as part of the INSERT parameters (since you ...
    (comp.databases.oracle.misc)