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.

Conditional Trigger

2 posts in Trigger Last posting was on 2006-05-18 17:47:31.0Z
Amy Hatfield Posted on 2006-05-17 19:15:50.0Z
Reply-To: "Amy Hatfield" <amy@caterease.com>
From: "Amy Hatfield" <amy@caterease.com>
Newsgroups: Advantage.Trigger
Subject: Conditional Trigger
Date: Wed, 17 May 2006 15:15:50 -0400
Lines: 17
Organization: Horizon Business Services, Inc.
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869
NNTP-Posting-Host: 70.88.32.174
Message-ID: <446b751b@solutions.advantagedatabase.com>
X-Trace: 17 May 2006 13:10:19 -0700, 70.88.32.174
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!70.88.32.174
Xref: solutions.advantagedatabase.com Advantage.Trigger:232
Article PK: 1136299

Here is what I am trying to accomplish:

I have three tables:
Table1 - active data
Table1Archive - archived data storage for historic reasons
Table1Deletes - deleted records storage

Basically I want a AFTER DELETE trigger on Table1 that will move the records
to either Table1Archive OR Table1Delete based on a flag, depending on if it
was "deleted" from Table1 as a deleted item or an archive item. Is there a
way to define a condition within a trigger?

Thank you.

Amy


Joachim Duerr (ADS Support) Posted on 2006-05-18 17:47:31.0Z
From: "Joachim Duerr (ADS Support)" <jojo.duerr@gmx.de>
Subject: Re: Conditional Trigger
Newsgroups: Advantage.Trigger
References: <446b751b@solutions.advantagedatabase.com>
Organization: iAnywhere
User-Agent: XanaNews/1.18.1.2
X-Face: ,QMv7[luB)BpWAQ~:"kw6n%0ieY63.:g2K3n~8ky0;||5Xle*Xq+=~<Fy:0CVC2nx@8~vZ
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
NNTP-Posting-Host: 192.168.2.197
Message-ID: <446ca523@solutions.advantagedatabase.com>
Date: 18 May 2006 10:47:31 -0700
X-Trace: 18 May 2006 10:47:31 -0700, 192.168.2.197
Lines: 34
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!192.168.2.197
Xref: solutions.advantagedatabase.com Advantage.Trigger:233
Article PK: 1136298


Amy Hatfield wrote in <446b751b@solutions.advantagedatabase.com>:

> Here is what I am trying to accomplish:
>
> I have three tables:
> Table1 - active data
> Table1Archive - archived data storage for historic reasons
> Table1Deletes - deleted records storage
>
> Basically I want a AFTER DELETE trigger on Table1 that will move the
> records to either Table1Archive OR Table1Delete based on a flag,
> depending on if it was "deleted" from Table1 as a deleted item or an
> archive item. Is there a way to define a condition within a trigger?

If you could spend an additional field to your table...yes.

create trigger test
on mytable
after delete
begin
declare @num integer;
@num=(select count(*) from __old where archivflag=true);
if @num>0
then insert into tblarchive select * from __old;
else insert into tbldeletes select * from __old;
end if;
end;


--
Joachim Duerr
Senior Product Support Analyst (Advantage Database Server)
iAnywhere Solutions / Extended Systems
advantage[AT]extendsys.de