Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Trigger Question

3 posts in Windows NT Last posting was on 1997-07-11 17:23:15.0Z
Alex Posted on 1997-07-05 18:25:36.0Z
X-Newsreader: Microsoft Outlook Express 4.71.0544.0
From: "Alex" <alex@iecinc.com>
Subject: Trigger Question
Date: Sat, 5 Jul 1997 13:25:36 -0500
Lines: 26
X-MimeOLE: Produced By Microsoft MimeOLE Engine V4.71.0544.0
Message-ID: <Pwv#egWi8GA.200@forums.powersoft.com>
Newsgroups: sybase.public.sqlserver.nt
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5591
Article PK: 1081668

I am trying to create a trigger which ensures links stay valid when an
entry is removed by putting the link the deleted row used into the rows
which refer to the row being deleted (Orphans and widow trigger). This
trigger does not work (does not see the deleted table).

Does anyone know how I can make this work?

Thanks,

Alex
alex@iecinc.com

TEXT OF TRIGGER
---------------------------

create trigger BDcascadedelMessage
on BDMSG
for delete
as
update BDMSG
SET BDMSG.RMID = deleted.RMID, BDMSG.RUID = deleted.RUID, BDMSG.RCID =
deleted.RCID
where BDMSG.RMID = deleted.MID AND BDMSG.RUID = deleted.UID AND BDMSG.RCID
= deleted.CID


George Patrick Sand Posted on 1997-07-11 17:23:15.0Z
Message-ID: <33C66C03.462@mci.com>
Date: Fri, 11 Jul 1997 13:23:15 -0400
From: George Patrick Sand <Pat.Sand@mci.com>
Reply-To: Pat.Sand@mci.com
Organization: MCI FIS (3392/082)
X-Mailer: Mozilla 3.0Gold (Win95; I)
MIME-Version: 1.0
To: Alex <alex@iecinc.com>
Subject: Re: Trigger Question
References: <Pwv#egWi8GA.200@forums.powersoft.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 37
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5587
Article PK: 1081664


Alex wrote:
>
> I am trying to create a trigger which ensures links stay valid when an
> entry is removed by putting the link the deleted row used into the rows
> which refer to the row being deleted (Orphans and widow trigger). This
> trigger does not work (does not see the deleted table).
>
> Does anyone know how I can make this work?
>
> Thanks,
>
> Alex
> alex@iecinc.com
>
> TEXT OF TRIGGER
> ---------------------------
>
> create trigger BDcascadedelMessage
> on BDMSG
> for delete
> as
> update BDMSG
> SET BDMSG.RMID = deleted.RMID, BDMSG.RUID = deleted.RUID, BDMSG.RCID =
> deleted.RCID
> where BDMSG.RMID = deleted.MID AND BDMSG.RUID = deleted.UID AND BDMSG.RCID
> = deleted.CID

Uh, my coffee hasn't kicked in yet...so here is another thing I thought
of JUST AFTER sending out the first one...

You should play it safe and have a FROM clause to explicitly state where
you are updating from. After version 4.9.x, the rules have been
tightened up on UPDATES to conform to the ANSI standard. Try the
following:

create trigger BDcascadedelMessage
on BDMSG
for delete
as
update BDMSG
SET RMID = d.RMID, RUID = d.RUID, RCID =d.RCID
from deleted d, BDMSG b
where b.RMID = d.MID AND b.RUID = d.UID AND b.RCID= d.CID

This will guarantee that deleted is seen...Hope it helps...
--
"Travelin' light but right..."
G. Patrick Sand (Pat.Sand@MCI.COM)
Pager: 1-800-MCI-WORD Pin: 212-2610


George Patrick Sand Posted on 1997-07-11 17:18:23.0Z
Message-ID: <33C66ADF.53D1@mci.com>
Date: Fri, 11 Jul 1997 13:18:23 -0400
From: George Patrick Sand <Pat.Sand@mci.com>
Reply-To: Pat.Sand@mci.com
Organization: MCI FIS (3392/082)
X-Mailer: Mozilla 3.0Gold (Win95; I)
MIME-Version: 1.0
To: Alex <alex@iecinc.com>
Subject: Re: Trigger Question
References: <Pwv#egWi8GA.200@forums.powersoft.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 34
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5588
Article PK: 1081665


Alex wrote:
>
> I am trying to create a trigger which ensures links stay valid when an
> entry is removed by putting the link the deleted row used into the rows
> which refer to the row being deleted (Orphans and widow trigger). This
> trigger does not work (does not see the deleted table).
>
> Does anyone know how I can make this work?
>
> Thanks,
>
> Alex
> alex@iecinc.com
>
> TEXT OF TRIGGER
> ---------------------------
>
> create trigger BDcascadedelMessage
> on BDMSG
> for delete
> as
> update BDMSG
> SET BDMSG.RMID = deleted.RMID, BDMSG.RUID = deleted.RUID, BDMSG.RCID =
> deleted.RCID
> where BDMSG.RMID = deleted.MID AND BDMSG.RUID = deleted.UID AND BDMSG.RCID
> = deleted.CID

Can you post the relevant part of the table here? and any error messages
you get...

You might want to check to make sure that your Update trigger isn't
doing anything funny. I just had a recent case of this bite me...

You might also want to make sure that you follow the links all the way
through in the deleted set so that they start and end at rows still in
the set. If you deleted several sucessive links in the chain at one
time, you could have problems.

Hope to see the schema and messages...
--
"Travelin' light but right..."
G. Patrick Sand (Pat.Sand@MCI.COM)
Pager: 1-800-MCI-WORD Pin: 212-2610