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.

Testing __Old & __New values in a Trigger?

4 posts in Trigger Last posting was on 2007-08-20 12:22:33.0Z
Ian Branch Posted on 2007-08-15 11:38:51.0Z
From: "Ian Branch" <branch@sitathome.net>
Subject: Testing __Old & __New values in a Trigger?
Newsgroups: Advantage.Trigger
Date: Wed, 15 Aug 2007 21:38:51 +1000
User-Agent: XanaNews/1.18.1.6
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
NNTP-Posting-Host: 218.215.129.164
Message-ID: <46c2e4a0@solutions.advantagedatabase.com>
X-Trace: 15 Aug 2007 05:33:52 -0700, 218.215.129.164
Lines: 23
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!218.215.129.164
Xref: solutions.advantagedatabase.com Advantage.Trigger:346
Article PK: 1136410

Hi Guys,

When a field called QtyUsed is changed I want to create an After Update
Trigger that does the following..

if __Old.QtyUsed <> __New.QtyUsed then
insert into Partslog ( Partnum, Qty, Action, Invnumb) values
(__New.PartNum, __New.QtyUsed - __Old.QtyUsed, 'IA', __New.Invnumb);
endif;


This however doesn't run as the field QtyUsed in 'If __Old.QtyUsed <> .... '
is not recognised.

Thoughts/Suggestions welcome.

Regards & TIA,

Ian

--


Jeremy Mullin Posted on 2007-08-16 18:38:46.0Z
Date: Thu, 16 Aug 2007 18:38:46 +0000 (UTC)
Message-ID: <e96e9af231c238c9ae19859f6c3c@devzone.advantagedatabase.com>
From: Jeremy Mullin <no@email.com>
Subject: Re: Testing __Old & __New values in a Trigger?
Newsgroups: Advantage.Trigger
References: <46c2e4a0@solutions.advantagedatabase.com>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Content-Type: text/plain; charset=iso-8859-1; format=flowed
X-Newsreader: JetBrains Omea Reader 1098.1
NNTP-Posting-Host: 10.24.38.100
X-Trace: 16 Aug 2007 12:35:46 -0700, 10.24.38.100
Lines: 40
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.24.38.100
Xref: solutions.advantagedatabase.com Advantage.Trigger:347
Article PK: 1136409

Hi Ian,

You need to open a cursor that references the __old table. For example:

declare old cursor as select * from __old;

open old;
fetch old;

try
if old.QtyUsed <> ...

finally
close old;
end try;

J.D. Mullin
Advantage R&D

> Hi Guys,
>
> When a field called QtyUsed is changed I want to create an After
> Update Trigger that does the following..
>
> if __Old.QtyUsed <> __New.QtyUsed then
> insert into Partslog ( Partnum, Qty, Action, Invnumb) values
> (__New.PartNum, __New.QtyUsed - __Old.QtyUsed, 'IA',
> __New.Invnumb);
> endif;
> This however doesn't run as the field QtyUsed in 'If __Old.QtyUsed <>
> .... ' is not recognised.
>
> Thoughts/Suggestions welcome.
>
> Regards & TIA,
>
> Ian
>


Ian Branch Posted on 2007-08-17 00:09:16.0Z
From: "Ian Branch" <branch@sitathome.net>
Subject: Next step..
Newsgroups: Advantage.Trigger
References: <46c2e4a0@solutions.advantagedatabase.com> <e96e9af231c238c9ae19859f6c3c@devzone.advantagedatabase.com>
Date: Fri, 17 Aug 2007 10:09:16 +1000
User-Agent: XanaNews/1.18.1.6
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
NNTP-Posting-Host: 218.215.129.164
Message-ID: <46c4e604@solutions.advantagedatabase.com>
X-Trace: 16 Aug 2007 18:04:20 -0700, 218.215.129.164
Lines: 57
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!218.215.129.164
Xref: solutions.advantagedatabase.com Advantage.Trigger:348
Article PK: 1136411

Hi Jeremy,

This is what I have as a starter and it works perfectly. The code could
probably be a little cleaner but it is readable att.
==============================================
// Updates Partslog when the QtyUsed value in LineItems is changed.
declare old cursor as select * from __old;
declare new cursor as select * from __new;
declare invoice cursor as select user_id from Invoice as i where i.invnumb =
new.invnumb;

open old;
fetch old;
open new;
fetch new;
open invoice;
fetch invoice;

try
if Old.QtyUsed < New.QtyUsed then
insert into Partslog ( Partnum, Action, Qty, WtyRepair, Invnumb,
User_ID) values (New.PartNum, 'IA', New.QtyUsed - Old.QtyUsed, New.WtyStock,
New.Invnumb, Invoice.user_id);
endif;
if Old.QtyUsed > New.QtyUsed then
insert into Partslog ( Partnum, Action, Qty, WtyRepair, Invnumb,
User_ID) values (New.PartNum, 'ID', New.QtyUsed - Old.QtyUsed, New.WtyStock,
New.Invnumb, Invoice.user_id);
endif;
finally
close old;
close invoice;
close new;
end try;
======================================
You will note that there is either 'IA' or 'ID' used dependant on the nature of
the change.

This posed the question in my mind...Can something be passed programatically at
run time to a Trigger for incorporation in the Trigger activity?

i.e. From a program module, send something to indicate which program
module/activity caused the trigger to be fired. An example that comes to mind
is a change in a Parts table where parts are added as a result of being removed
from a Invoice line & returned to stock, parts being received into stock from a
parts order, a new part being added. All affect the Parts Qty field but are
triggered by different program modules.

I hope my explanation is clear enough.

Regards & TIA,

Ian


--


Joachim Duerr (ADS) Posted on 2007-08-20 12:22:33.0Z
From: "Joachim Duerr (ADS)" <jojo.duerr@gmx.de>
Subject: Re: Next step..
Newsgroups: Advantage.Trigger
References: <46c2e4a0@solutions.advantagedatabase.com> <e96e9af231c238c9ae19859f6c3c@devzone.advantagedatabase.com> <46c4e604@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: 10.56.66.108
Message-ID: <46c97979@solutions.advantagedatabase.com>
Date: 20 Aug 2007 05:22:33 -0700
X-Trace: 20 Aug 2007 05:22:33 -0700, 10.56.66.108
Lines: 18
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.56.66.108
Xref: solutions.advantagedatabase.com Advantage.Trigger:349
Article PK: 1136412


Ian Branch wrote in <46c4e604@solutions.advantagedatabase.com>:

> i.e. From a program module, send something to indicate which program
> module/activity caused the trigger to be fired. An example that
> comes to mind is a change in a Parts table where parts are added as a
> result of being removed from a Invoice line & returned to stock,
> parts being received into stock from a parts order, a new part being
> added. All affect the Parts Qty field but are triggered by different
> program modules.

you can either use a temp table and add something there or you use
applicationid and set it in your module before executing the SQL.

--
Joachim Duerr
System Consultant (Advantage Database Server)
Sybase iAnywhere
advantagesupport[AT]ianywhere.com