"Discontinued" - Del Trigger

"Discontinued" - Del Trigger

Post by Lasse Edsvi » Tue, 08 Oct 2002 20:47:12



Hello

I was wondering if you guys could help me with a simple problem.

I have a table that contains a tinyint column that is 0 or 1.

e.g

CREATE TABLE Foo(
     FooID int IDENTITY(1,1),
     FooName varchar(20),
     Discontinued tinyint,
)

CREATE TABLE Blah(
      BlahID int IDENTITY(1,1),
      FooID int NOT NULL,
      Blah varchar(20)
)

and when something gets deleted in table "Foo", I want to set Discontinued=1
if that FooID exists in table "Blah", else delete it.

TIA
/Lasse

 
 
 

"Discontinued" - Del Trigger

Post by David F?r » Tue, 08 Oct 2002 20:57:11


Maybe you could look into INSTEAD OF - triggers.
I haven't tried them myself but they should do what you're after.

syntax is:
CREATE TRIGGER xxx ON yyy
INSTEAD OF DELETE
AS
[...]

Note though, according to Books online:
Note  INSTEAD OF DELETE and INSTEAD OF UPDATE triggers cannot be defined on
a table that has a foreign key defined with a DELETE or UPDATE action.


Quote:> Hello

> I was wondering if you guys could help me with a simple problem.

> I have a table that contains a tinyint column that is 0 or 1.

> e.g

> CREATE TABLE Foo(
>      FooID int IDENTITY(1,1),
>      FooName varchar(20),
>      Discontinued tinyint,
> )

> CREATE TABLE Blah(
>       BlahID int IDENTITY(1,1),
>       FooID int NOT NULL,
>       Blah varchar(20)
> )

> and when something gets deleted in table "Foo", I want to set
Discontinued=1
> if that FooID exists in table "Blah", else delete it.

> TIA
> /Lasse


 
 
 

"Discontinued" - Del Trigger

Post by Uri Diman » Tue, 08 Oct 2002 22:05:18


Lasse,
If I undestand you correctly so..

CREATE TRIGGER MY_TRIG ON Foo
FOR DELETE

IF EXISTS (SELECT * FROM DELETED D
                   JOIN Blah B ON D.Fooid=B.Fooid)
UPDATE Foo SET Discontinued =1
                       FROM DELETED D JOIN Foo F ON D.fooid=F.fooid


Quote:> Hello

> I was wondering if you guys could help me with a simple problem.

> I have a table that contains a tinyint column that is 0 or 1.

> e.g

> CREATE TABLE Foo(
>      FooID int IDENTITY(1,1),
>      FooName varchar(20),
>      Discontinued tinyint,
> )

> CREATE TABLE Blah(
>       BlahID int IDENTITY(1,1),
>       FooID int NOT NULL,
>       Blah varchar(20)
> )

> and when something gets deleted in table "Foo", I want to set
Discontinued=1
> if that FooID exists in table "Blah", else delete it.

> TIA
> /Lasse

 
 
 

"Discontinued" - Del Trigger

Post by Lasse Edsvi » Tue, 08 Oct 2002 21:17:18


Hello

hmm..... should I use Instead of Trigger or a "normal" For Delete?

Uri's version seems nice, and I will test it later on.

/Lasse


Quote:> Hello

> I was wondering if you guys could help me with a simple problem.

> I have a table that contains a tinyint column that is 0 or 1.

> e.g

> CREATE TABLE Foo(
>      FooID int IDENTITY(1,1),
>      FooName varchar(20),
>      Discontinued tinyint,
> )

> CREATE TABLE Blah(
>       BlahID int IDENTITY(1,1),
>       FooID int NOT NULL,
>       Blah varchar(20)
> )

> and when something gets deleted in table "Foo", I want to set
Discontinued=1
> if that FooID exists in table "Blah", else delete it.

> TIA
> /Lasse

 
 
 

"Discontinued" - Del Trigger

Post by David F?r » Tue, 08 Oct 2002 22:05:18


Except that it's an AFTER trigger (default) which is only executed AFTER the
action has taken place, i.e. the record has been deleted....hmmm....

The other kind of trigger (INSTEAD OF) replaces the action and is used
usually for views and stuff (according to the books online, that never lie)

So, I'd suggest a trigger kinda like:

CREATE TRIGGER triDelete ON [dbo].[tblTest]
INSTEAD OF DELETE
AS

IF EXISTS(SELECT tblTest2.t1ID FROM tblTest2, deleted WHERE t1ID =
deleted.ID)
 UPDATE tblTest SET TestValue = 'Hey, this one existed !' FROM deleted WHERE
tblTest.ID = deleted.ID
ELSE
 DELETE tblTest WHERE tblTest.ID = (SELECT ID FROM deleted)

This is a little test I just whipped together and it worked as you wanted.
If I put IDs 1 & 2 in tblTest (ID, TestValue) and t1ID 2 in tblTest2 (t1ID)
the following happens

DELETE FROM tblTest WHERE ID = 1
This one is NOT deleted, instead the field TestValue is updated.
DELEET FROM tblTest WHERE ID = 2
This is a goner. Bye, bye.

Right, messy example but hey, it works...

/Dave


> Hello

> hmm..... should I use Instead of Trigger or a "normal" For Delete?

> Uri's version seems nice, and I will test it later on.

> /Lasse



> > Hello

> > I was wondering if you guys could help me with a simple problem.

> > I have a table that contains a tinyint column that is 0 or 1.

> > e.g

> > CREATE TABLE Foo(
> >      FooID int IDENTITY(1,1),
> >      FooName varchar(20),
> >      Discontinued tinyint,
> > )

> > CREATE TABLE Blah(
> >       BlahID int IDENTITY(1,1),
> >       FooID int NOT NULL,
> >       Blah varchar(20)
> > )

> > and when something gets deleted in table "Foo", I want to set
> Discontinued=1
> > if that FooID exists in table "Blah", else delete it.

> > TIA
> > /Lasse

 
 
 

"Discontinued" - Del Trigger

Post by David F?r » Tue, 08 Oct 2002 22:10:34


crap...
Of course the two DELETEs in my example were mixed up....switch them and
it'll be right...and of course I just realized that you'll get into some
trouble if you run a delete on multiple records....but anyway, it was
<almost> right ...


> Except that it's an AFTER trigger (default) which is only executed AFTER
the
> action has taken place, i.e. the record has been deleted....hmmm....

> The other kind of trigger (INSTEAD OF) replaces the action and is used
> usually for views and stuff (according to the books online, that never
lie)

> So, I'd suggest a trigger kinda like:

> CREATE TRIGGER triDelete ON [dbo].[tblTest]
> INSTEAD OF DELETE
> AS

> IF EXISTS(SELECT tblTest2.t1ID FROM tblTest2, deleted WHERE t1ID =
> deleted.ID)
>  UPDATE tblTest SET TestValue = 'Hey, this one existed !' FROM deleted
WHERE
> tblTest.ID = deleted.ID
> ELSE
>  DELETE tblTest WHERE tblTest.ID = (SELECT ID FROM deleted)

> This is a little test I just whipped together and it worked as you wanted.
> If I put IDs 1 & 2 in tblTest (ID, TestValue) and t1ID 2 in tblTest2
(t1ID)
> the following happens

> DELETE FROM tblTest WHERE ID = 1
> This one is NOT deleted, instead the field TestValue is updated.
> DELEET FROM tblTest WHERE ID = 2
> This is a goner. Bye, bye.

> Right, messy example but hey, it works...

> /Dave



> > Hello

> > hmm..... should I use Instead of Trigger or a "normal" For Delete?

> > Uri's version seems nice, and I will test it later on.

> > /Lasse



> > > Hello

> > > I was wondering if you guys could help me with a simple problem.

> > > I have a table that contains a tinyint column that is 0 or 1.

> > > e.g

> > > CREATE TABLE Foo(
> > >      FooID int IDENTITY(1,1),
> > >      FooName varchar(20),
> > >      Discontinued tinyint,
> > > )

> > > CREATE TABLE Blah(
> > >       BlahID int IDENTITY(1,1),
> > >       FooID int NOT NULL,
> > >       Blah varchar(20)
> > > )

> > > and when something gets deleted in table "Foo", I want to set
> > Discontinued=1
> > > if that FooID exists in table "Blah", else delete it.

> > > TIA
> > > /Lasse

 
 
 

"Discontinued" - Del Trigger

Post by Andrew Joh » Wed, 09 Oct 2002 21:26:42


Uri,

Updating a row that is just about to be deleted ?  Does not compute !

How about:

Create Trigger FooBlah on Foo
instead of delete
as

update f
set Discontinued = 1
from Foo f
inner join deleted d
 on d.FooID = f.BlahID
inner join Blah b
 on b.BlahID = d.FooID

delete f
from Foo f
inner join Deleted d
 on d.FooID = f.FooID
where f.Discontinued IS NULL
 or f.Discontinued <> 1

return

Regards
 AJ


> Lasse,
> If I undestand you correctly so..

> CREATE TRIGGER MY_TRIG ON Foo
> FOR DELETE

> IF EXISTS (SELECT * FROM DELETED D
>                    JOIN Blah B ON D.Fooid=B.Fooid)
> UPDATE Foo SET Discontinued =1
>                        FROM DELETED D JOIN Foo F ON D.fooid=F.fooid



> > Hello

> > I was wondering if you guys could help me with a simple problem.

> > I have a table that contains a tinyint column that is 0 or 1.

> > e.g

> > CREATE TABLE Foo(
> >      FooID int IDENTITY(1,1),
> >      FooName varchar(20),
> >      Discontinued tinyint,
> > )

> > CREATE TABLE Blah(
> >       BlahID int IDENTITY(1,1),
> >       FooID int NOT NULL,
> >       Blah varchar(20)
> > )

> > and when something gets deleted in table "Foo", I want to set
> Discontinued=1
> > if that FooID exists in table "Blah", else delete it.

> > TIA
> > /Lasse