"Discontinued"-trigger?

"Discontinued"-trigger?

Post by Lasse Edsvi » Thu, 07 Mar 2002 00:13:43



Hello

I was wondering how I can set a tinyint coulumn "Discontinued" = 1 in TableA
if TableA.ID exists in TableB or TableC, else Delete it, using a trigger.

TIA
/Lasse

 
 
 

"Discontinued"-trigger?

Post by Tony Rogerso » Thu, 07 Mar 2002 00:23:53


Hi Lasse,

UPDATE b
FROM inserted i
        INNER JOIN TableA b ON b.yourpk = i.yourpk
WHERE EXISTS (
        SELECT *
        FROM TableB tb
        WHERE tb.ytourpkl = i.yourpk )

Delete is similar.

Make sense?

--
Tony Rogerson SQL Server MVP
Torver Computer Consultants Ltd
http://www.sql-server.co.uk [UK User Group, FAQ, KB's etc..]


Quote:> Hello

> I was wondering how I can set a tinyint coulumn "Discontinued" = 1 in
TableA
> if TableA.ID exists in TableB or TableC, else Delete it, using a trigger.

> TIA
> /Lasse


 
 
 

"Discontinued"-trigger?

Post by Lasse Edsvi » Thu, 07 Mar 2002 00:31:06


Tony,

hmm... not really, how can I do either the delete or update in one trigger
since when I delete a tuple the trigger should update or delete

/Lasse


> Hi Lasse,

> UPDATE b
> FROM inserted i
>         INNER JOIN TableA b ON b.yourpk = i.yourpk
> WHERE EXISTS (
>         SELECT *
>         FROM TableB tb
>         WHERE tb.ytourpkl = i.yourpk )

> Delete is similar.

> Make sense?

> --
> Tony Rogerson SQL Server MVP
> Torver Computer Consultants Ltd
> http://www.sql-server.co.uk [UK User Group, FAQ, KB's etc..]



> > Hello

> > I was wondering how I can set a tinyint coulumn "Discontinued" = 1 in
> TableA
> > if TableA.ID exists in TableB or TableC, else Delete it, using a
trigger.

> > TIA
> > /Lasse

 
 
 

"Discontinued"-trigger?

Post by Tony Rogerso » Thu, 07 Mar 2002 00:42:28


Oh I see.

You should do it in a before trigger instead then, essentially you don't
want to continue with the delete.

Check out BOL under INSTEAD OF triggers.

--
Tony Rogerson SQL Server MVP
Torver Computer Consultants Ltd
http://www.sql-server.co.uk [UK User Group, FAQ, KB's etc..]


> Tony,

> hmm... not really, how can I do either the delete or update in one trigger
> since when I delete a tuple the trigger should update or delete

> /Lasse



> > Hi Lasse,

> > UPDATE b
> > FROM inserted i
> >         INNER JOIN TableA b ON b.yourpk = i.yourpk
> > WHERE EXISTS (
> >         SELECT *
> >         FROM TableB tb
> >         WHERE tb.ytourpkl = i.yourpk )

> > Delete is similar.

> > Make sense?

> > --
> > Tony Rogerson SQL Server MVP
> > Torver Computer Consultants Ltd
> > http://www.sql-server.co.uk [UK User Group, FAQ, KB's etc..]



> > > Hello

> > > I was wondering how I can set a tinyint coulumn "Discontinued" = 1 in
> > TableA
> > > if TableA.ID exists in TableB or TableC, else Delete it, using a
> trigger.

> > > TIA
> > > /Lasse

 
 
 

1. "Discontinued" - Del Trigger

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

2. Can anyone help running out of time two days and still no responses.

3. max of ("...","...","..")

4. FoxPro in St. Louis MO

5. "."and ","

6. check constraint over multiple tables II

7. The ""string""

8. How do I set the 'Auto restart SQL Server Agent' option programatically?

9. aReport."Field".SetFilter("this")?

10. Record.open "", "URL=..."

11. "."and ","

12. The+instruction+at+"0x1f90db9b"+referenced+memory+at+"0x7fa03794"+The+memory+cou