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.

After Insert Trigger Help

6 posts in Trigger Last posting was on 2009-06-24 10:44:59.0Z
Ian Branch Posted on 2009-06-24 07:30:49.0Z
From: "Ian Branch" <branch@celestial.com.au>
Subject: After Insert Trigger Help
Newsgroups: Advantage.Trigger
Date: Wed, 24 Jun 2009 17:30:49 +1000
User-Agent: XanaNews/1.19.1.194
Message-ID: <xn0gbsmhk4ivkj000@devzone.advantagedatabase.com>
X-Ref: DevZone.AdvantageDatabase.com ~XNS:00000141
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
NNTP-Posting-Host: 218.215.152.219
X-Trace: 24 Jun 2009 01:29:56 -0700, 218.215.152.219
Lines: 49
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!218.215.152.219
Xref: solutions.advantagedatabase.com Advantage.Trigger:438
Article PK: 1136503

Hi Guys,

Working in D2007.

Still learning about SQL & Triggers and all that stuff...:-)

I have a .dbf invoice table with an invnumb field. I am unable ti use autoinc
fields as yet so the invnumb has to be derived from somewhere when a new
invoice is added.

I use a single field record table with a single field called invnumb.

At the moment I programatically do the following..
===================================
procedure TDM1.ttInvoicesBeforePost(DataSet: TDataSet);
var
nRecord: Integer;
begin
if (ttInvoices.State = dsInsert) then begin
//MessageDlg('Invoice Post Called.', mtInformation, [mbOK], 0);
with ttRecords do begin
Open;
nRecord := GetInteger('Invnumb');
Edit;
FieldByName('Invnumb').AsInteger := nRecord + 1;
Post;
Close;
end;
ttInvoices.FieldByName('invnumb').AsInteger := nRecord;
end;
end;
===================================

It occurs to me that this could be accomplished more elegantly in a trigger
script associated with the table, in the DD.

What I am unsure of is what should be the Trigger Type to accomplish the same
thing? AFTER INSERT? BEFORE UPDATE?.....

Some advice would be appreciated.

Regards & TIA,

Ian


Joachim Duerr (ADS) Posted on 2009-06-24 09:14:00.0Z
From: "Joachim Duerr (ADS)" <jojo.duerr@gmx.de>
Subject: Re: After Insert Trigger Help
Newsgroups: Advantage.Trigger
References: <xn0gbsmhk4ivkj000@devzone.advantagedatabase.com>
User-Agent: XanaNews/1.19.1.194
X-Face: u2p+</,mb|Ah!x!/qxX5q0t:O~.<1&JzwNHYhSqcviY{~&|iDc"U.Je1A.ZeHR`d;;y#R
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
NNTP-Posting-Host: 10.56.66.155
Message-ID: <4a41e048@solutions.advantagedatabase.com>
Date: 24 Jun 2009 02:14:00 -0700
X-Trace: 24 Jun 2009 02:14:00 -0700, 10.56.66.155
Lines: 19
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.56.66.155
Xref: solutions.advantagedatabase.com Advantage.Trigger:439
Article PK: 1136495


Ian Branch wrote:

> I have a .dbf invoice table with an invnumb field. I am unable ti
>use autoinc fields as yet so the invnumb has to be derived from
>somewhere when a new invoice is added.

create trigger [trig_ins]
on [invoice]
instead of insert
begin
update __new set [invnumb]=
(select coalesce(max([invnumb])+1,1) from [invoice]);
insert into [invoice] select * from __new;
end;

--
Joachim Duerr
Advantage Presales
check out my new ADS book on http://www.jd-engineering.de/adsbuch


Ian Branch Posted on 2009-06-24 08:47:33.0Z
From: "Ian Branch" <branch@celestial.com.au>
Subject: Re: After Insert Trigger Help
Newsgroups: Advantage.Trigger
References: <xn0gbsmhk4ivkj000@devzone.advantagedatabase.com> <4a41e048@solutions.advantagedatabase.com>
Date: Wed, 24 Jun 2009 18:47:33 +1000
User-Agent: XanaNews/1.19.1.194
Message-ID: <xn0gbsohc79jwn001@devzone.advantagedatabase.com>
X-Ref: DevZone.AdvantageDatabase.com ~XNS:00000144
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
NNTP-Posting-Host: 218.215.152.219
X-Trace: 24 Jun 2009 02:46:37 -0700, 218.215.152.219
Lines: 35
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!218.215.152.219
Xref: solutions.advantagedatabase.com Advantage.Trigger:440
Article PK: 1136500


Joachim Duerr (ADS) wrote:

> Ian Branch wrote:
>
> > I have a .dbf invoice table with an invnumb field. I am unable ti
> > use autoinc fields as yet so the invnumb has to be derived from
> > somewhere when a new invoice is added.
>
> create trigger [trig_ins]
> on [invoice]
> instead of insert
> begin
> update __new set [invnumb]=
> (select coalesce(max([invnumb])+1,1) from [invoice]);
> insert into [invoice] select * from __new;
> end;

Hi Joachim,

Again thank you for your input/advice.

I was about to ask where/how the routine got its invnumb from the record table
but i realised that you were very sneaky and have created effectively an
autoinc functionality for the invnumb field.

I have a minor complication in that the record table withthe invnumb field in
it is used by a Clipper app, not using Advantage, at the same time so i have to
update the record table as well.

Because I like your solution much better I am going to try and implement it in
the Clipper app.

Thanks again,

Ian


Ian Branch Posted on 2009-06-24 09:58:27.0Z
From: "Ian Branch" <branch@celestial.com.au>
Subject: Re: After Insert Trigger Help - Update.
Newsgroups: Advantage.Trigger
References: <xn0gbsmhk4ivkj000@devzone.advantagedatabase.com> <4a41e048@solutions.advantagedatabase.com> <xn0gbsohc79jwn001@devzone.advantagedatabase.com>
Date: Wed, 24 Jun 2009 19:58:27 +1000
User-Agent: XanaNews/1.19.1.194
Message-ID: <xn0gbsqbx9sq6j002@devzone.advantagedatabase.com>
X-Ref: DevZone.AdvantageDatabase.com ~XNS:00000145
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
NNTP-Posting-Host: 218.215.152.219
X-Trace: 24 Jun 2009 03:57:31 -0700, 218.215.152.219
Lines: 10
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!218.215.152.219
Xref: solutions.advantagedatabase.com Advantage.Trigger:441
Article PK: 1136501

I did this in the interim while I figure out the Clipper side.

update __new set [invnumb]= (select coalesce(max([invnumb])+1,1) from
[invoice]);
insert into [invoice] select * from __new;
update record set invnumb = (select invnumb from __new);

Thanks again Joachim

Ian


Ian Branch Posted on 2009-06-24 10:25:25.0Z
From: "Ian Branch" <branch@celestial.com.au>
Subject: Re: After Insert Trigger Help - Problem
Newsgroups: Advantage.Trigger
References: <xn0gbsmhk4ivkj000@devzone.advantagedatabase.com> <4a41e048@solutions.advantagedatabase.com> <xn0gbsohc79jwn001@devzone.advantagedatabase.com> <xn0gbsqbx9sq6j002@devzone.advantagedatabase.com>
Date: Wed, 24 Jun 2009 20:25:25 +1000
User-Agent: XanaNews/1.19.1.194
Message-ID: <xn0gbsr3garf26003@devzone.advantagedatabase.com>
X-Ref: DevZone.AdvantageDatabase.com ~XNS:00000146
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
NNTP-Posting-Host: 218.215.152.219
X-Trace: 24 Jun 2009 04:24:29 -0700, 218.215.152.219
Lines: 15
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!218.215.152.219
Xref: solutions.advantagedatabase.com Advantage.Trigger:442
Article PK: 1136502

Hi Joachim,

Hmmm. It is updating all the invoice data correctly, except for the .fpt
(memo) field data.

I checked and do have "Include memo and blob data in VALUES tables"
enabled/selected.

The .dbf format is Clipper with Apollo .cdx/.fpt files.

Any thoughts appreciated.

Regards,

Ian


Ian Branch Posted on 2009-06-24 10:44:59.0Z
From: "Ian Branch" <branch@celestial.com.au>
Subject: Disregard - It's working now. !@#$%^&* computers.
Newsgroups: Advantage.Trigger
References: <xn0gbsmhk4ivkj000@devzone.advantagedatabase.com> <4a41e048@solutions.advantagedatabase.com> <xn0gbsohc79jwn001@devzone.advantagedatabase.com> <xn0gbsqbx9sq6j002@devzone.advantagedatabase.com> <xn0gbsr3garf26003@devzone.advantagedatabase.com>
Date: Wed, 24 Jun 2009 20:44:59 +1000
User-Agent: XanaNews/1.19.1.194
Message-ID: <xn0gbsrktbgkfi004@devzone.advantagedatabase.com>
X-Ref: DevZone.AdvantageDatabase.com ~XNS:00000147
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
NNTP-Posting-Host: 218.215.152.219
X-Trace: 24 Jun 2009 04:44:03 -0700, 218.215.152.219
Lines: 2
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!218.215.152.219
Xref: solutions.advantagedatabase.com Advantage.Trigger:443
Article PK: 1136505