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.

Auto Increment Based on other indexes

5 posts in Trigger Last posting was on 2006-07-28 08:22:06.0Z
Brent Nelson Posted on 2006-07-25 14:49:26.0Z
Date: Tue, 25 Jul 2006 08:49:26 -0600
From: Brent Nelson <bnelson@westernwats.com>
User-Agent: Thunderbird 1.5 (Windows/20051201)
MIME-Version: 1.0
Newsgroups: Advantage.Trigger
Subject: Auto Increment Based on other indexes
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 216.20.254.254
Message-ID: <44c62e95@solutions.advantagedatabase.com>
X-Trace: 25 Jul 2006 08:45:41 -0700, 216.20.254.254
Lines: 32
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!216.20.254.254
Xref: solutions.advantagedatabase.com Advantage.Trigger:262
Article PK: 1136329

I am still new to triggers and can't seem to figure this out, I want to
write a trigger that auto increments a field value based on two other
fields in my table.

For example, let's say I have a company id, a group id, and a task id. I
want to start the task id at zero and every time a record is added with
a company and a group id that matches previous entries I want to select
the last number and add one to it, if a number isn't there (there aren't
previous entries) I want to set it to zero.

Here is what I tried:

DECLARE newprojnum CURSOR;
DECLARE oldprojnum CURSOR;
OPEN newprojnum AS SELECT * FROM __new;
OPEN
TRY
@oldprojnum = ( SELECT TOP 1 proj_num FROM projects WHERE proj_cid =
newprojnum.proj_cid AND proj_did = newprojnum.proj_did );
IF @oldprojnum > -1
UPDATE projects SET proj_num = oldprojnum + 1 WHERE proj_id =
newprojnum.proj_id;
ELSE
UPDATE projects SET proj_num = 0 WHERE proj_id =
newprojnum.proj_id;
END IF;
FINALLY
CLOSE newprojnum;
END TRY;

I know that this is way off, but it was just a start... I am very new to
this SQL scripting in ADS.


Jeremy Mullin Posted on 2006-07-27 14:24:56.0Z
Date: Thu, 27 Jul 2006 14:24:56 +0000 (UTC)
Message-ID: <886edc1516a8e8c87f6d46dcf2cb@devzone.advantagedatabase.com>
From: Jeremy Mullin <no@email.com>
Subject: Re: Auto Increment Based on other indexes
Newsgroups: Advantage.Trigger
References: <44c62e95@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 928.2
NNTP-Posting-Host: 10.24.38.116
X-Trace: 27 Jul 2006 08:21:10 -0700, 10.24.38.116
Lines: 55
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.24.38.116
Xref: solutions.advantagedatabase.com Advantage.Trigger:263
Article PK: 1136331

Hi Brent,

Doesn't look "way off" to me. Looks like you are pretty close. What behavior
are you getting? Is this an INSTEAD OF UPDATE trigger, or an AFTER UPDATE
trigger?

A few thoughts after quickly scanning the script. These may provide some
direction:

- Should your SELECT TOP 1 statement use an order by clause so you can get
the largest ID? It will need to be descending too, so something like:
@oldprojnum = ( SELECT TOP 1 proj_num FROM projects WHERE proj_cid = newprojnum.proj_cid
AND proj_did = newprojnum.proj_did ORDER BY proj_num desc );

- if this is an INSTEAD OF trigger, you need to provide all of the fields
in the update statement, not just proj_num

J.D. Mullin
Advantage R&D

> I am still new to triggers and can't seem to figure this out, I want
> to write a trigger that auto increments a field value based on two
> other fields in my table.
>
> For example, let's say I have a company id, a group id, and a task id.
> I want to start the task id at zero and every time a record is added
> with a company and a group id that matches previous entries I want to
> select the last number and add one to it, if a number isn't there
> (there aren't previous entries) I want to set it to zero.
>
> Here is what I tried:
>
> DECLARE newprojnum CURSOR;
> DECLARE oldprojnum CURSOR;
> OPEN newprojnum AS SELECT * FROM __new;
> OPEN
> TRY
> @oldprojnum = ( SELECT TOP 1 proj_num FROM projects WHERE proj_cid
> =
> newprojnum.proj_cid AND proj_did = newprojnum.proj_did );
> IF @oldprojnum > -1
> UPDATE projects SET proj_num = oldprojnum + 1 WHERE proj_id =
> newprojnum.proj_id;
> ELSE
> UPDATE projects SET proj_num = 0 WHERE proj_id =
> newprojnum.proj_id;
> END IF;
> FINALLY
> CLOSE newprojnum;
> END TRY;
> I know that this is way off, but it was just a start... I am very new
> to this SQL scripting in ADS.
>


Brent Nelson Posted on 2006-07-27 20:24:48.0Z
Date: Thu, 27 Jul 2006 14:24:48 -0600
From: Brent Nelson <bnelson@westernwats.com>
User-Agent: Thunderbird 1.5 (Windows/20051201)
MIME-Version: 1.0
Newsgroups: Advantage.Trigger
Subject: Re: Auto Increment Based on other indexes
References: <44c62e95@solutions.advantagedatabase.com> <886edc1516a8e8c87f6d46dcf2cb@devzone.advantagedatabase.com>
In-Reply-To: <886edc1516a8e8c87f6d46dcf2cb@devzone.advantagedatabase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 216.20.254.254
Message-ID: <44c9202e@solutions.advantagedatabase.com>
X-Trace: 27 Jul 2006 14:21:02 -0700, 216.20.254.254
Lines: 102
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!216.20.254.254
Xref: solutions.advantagedatabase.com Advantage.Trigger:265
Article PK: 1136328

I am now getting the following error:

ERROR: Database error: 5177 - SQL error: [Extended Systems][Advantage
SQL][ASA] Error 7200: AQE Error: State = HY000; NativeError = 5177;
[Extended Systems][Advantage SQL][ASA] Error 5177: Trigger execution
failed. Error 7200: AQE Error: State = 42000; NativeError = 2223;
[Extended Systems][Advantage SQL Engine]The cursor is before first row
or after last row. Referencing: newprojnum -- Location of error in the
SQL statement is: 196 (line: 4 column: 102), SQL state HY000 in SQLExecute

Here is the code currently:

DECLARE newprojnum CURSOR;
DECLARE oldprojnum CURSOR;
OPEN newprojnum AS SELECT * FROM __new;
OPEN oldprojnum AS SELECT TOP 1 proj_num FROM projects WHERE proj_cid =
newprojnum.proj_cid AND proj_did = newprojnum.proj_did ORDER BY proj_num
DESC;

TRY
IF oldprojnum.proj_num > -1 THEN
INSERT INTO projects ( proj_cid, proj_did, proj_num, proj_pid,
proj_csr, proj_name, proj_need, proj_scheduleStartDate,
proj_scheduleEndDate, proj_modified_lid, proj_modifiedTimeStamp ) VALUES
( newprojnum.proj_cid, newprojnum.proj_did, oldprojnum.proj_num,
newprojnum.proj_pid, newprojnum.proj_csr, newprojnum.proj_name,
newprojnum.proj_need, newprojnum.proj_scheduleStartDate,
newprojnum.proj_scheduleEndDate, newprojnum.proj_modified_lid,
newprojnum.proj_modifiedTimeStamp );
ELSE
INSERT INTO projects ( proj_cid, proj_did, proj_num, proj_pid,
proj_csr, proj_name, proj_need, proj_scheduleStartDate,
proj_scheduleEndDate, proj_modified_lid, proj_modifiedTimeStamp ) VALUES
( newprojnum.proj_cid, newprojnum.proj_did, 0, newprojnum.proj_pid,
newprojnum.proj_csr, newprojnum.proj_name, newprojnum.proj_need,
newprojnum.proj_scheduleStartDate, newprojnum.proj_scheduleEndDate,
newprojnum.proj_modified_lid, newprojnum.proj_modifiedTimeStamp );
END IF;
FINALLY
CLOSE newprojnum;
CLOSE oldprojnum;
END TRY;

=S

Jeremy Mullin wrote:
> Hi Brent,
>
> Doesn't look "way off" to me. Looks like you are pretty close. What
> behavior are you getting? Is this an INSTEAD OF UPDATE trigger, or an
> AFTER UPDATE trigger?
>
> A few thoughts after quickly scanning the script. These may provide some
> direction:
>
> - Should your SELECT TOP 1 statement use an order by clause so you can
> get the largest ID? It will need to be descending too, so something like:
> @oldprojnum = ( SELECT TOP 1 proj_num FROM projects WHERE proj_cid =
> newprojnum.proj_cid AND proj_did = newprojnum.proj_did ORDER BY proj_num
> desc );
>
> - if this is an INSTEAD OF trigger, you need to provide all of the
> fields in the update statement, not just proj_num
>
> J.D. Mullin
> Advantage R&D
>
>> I am still new to triggers and can't seem to figure this out, I want
>> to write a trigger that auto increments a field value based on two
>> other fields in my table.
>>
>> For example, let's say I have a company id, a group id, and a task id.
>> I want to start the task id at zero and every time a record is added
>> with a company and a group id that matches previous entries I want to
>> select the last number and add one to it, if a number isn't there
>> (there aren't previous entries) I want to set it to zero.
>>
>> Here is what I tried:
>>
>> DECLARE newprojnum CURSOR;
>> DECLARE oldprojnum CURSOR;
>> OPEN newprojnum AS SELECT * FROM __new;
>> OPEN
>> TRY
>> @oldprojnum = ( SELECT TOP 1 proj_num FROM projects WHERE proj_cid
>> =
>> newprojnum.proj_cid AND proj_did = newprojnum.proj_did );
>> IF @oldprojnum > -1
>> UPDATE projects SET proj_num = oldprojnum + 1 WHERE proj_id =
>> newprojnum.proj_id;
>> ELSE
>> UPDATE projects SET proj_num = 0 WHERE proj_id =
>> newprojnum.proj_id;
>> END IF;
>> FINALLY
>> CLOSE newprojnum;
>> END TRY;
>> I know that this is way off, but it was just a start... I am very new
>> to this SQL scripting in ADS.
>>
>
>


Joachim Duerr (ADS Support) Posted on 2006-07-28 08:22:06.0Z
From: "Joachim Duerr (ADS Support)" <jojo.duerr@gmx.de>
Subject: Re: Auto Increment Based on other indexes
Newsgroups: Advantage.Trigger
References: <44c62e95@solutions.advantagedatabase.com> <886edc1516a8e8c87f6d46dcf2cb@devzone.advantagedatabase.com> <44c9202e@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: 195.2.185.25
Message-ID: <44c9bb1e@solutions.advantagedatabase.com>
Date: 28 Jul 2006 01:22:06 -0700
X-Trace: 28 Jul 2006 01:22:06 -0700, 195.2.185.25
Lines: 24
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!195.2.185.25
Xref: solutions.advantagedatabase.com Advantage.Trigger:266
Article PK: 1136333


Brent Nelson wrote in <44c9202e@solutions.advantagedatabase.com>:

> ERROR: Database error: 5177 - SQL error: [Extended Systems][Advantage
> SQL][ASA] Error 7200: AQE Error: State = HY000; NativeError =
> 5177; [Extended Systems][Advantage SQL][ASA] Error 5177: Trigger
> execution failed. Error 7200: AQE Error: State = 42000;
> NativeError = 2223; [Extended Systems][Advantage SQL Engine]The
> cursor is before first row or after last row. Referencing: newprojnum
> -- Location of error in the SQL statement is: 196 (line: 4 column:
> 102), SQL state HY000 in SQLExecute

you need to fetch a record after opening.

declare c cursor;
open c as select * from mytable;
if fetch c then
--access that record
end if;

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


Brent Nelson Posted on 2006-07-27 16:43:59.0Z
Date: Thu, 27 Jul 2006 10:43:59 -0600
From: Brent Nelson <bnelson@westernwats.com>
User-Agent: Thunderbird 1.5 (Windows/20051201)
MIME-Version: 1.0
Newsgroups: Advantage.Trigger
Subject: Re: Auto Increment Based on other indexes
References: <44c62e95@solutions.advantagedatabase.com> <886edc1516a8e8c87f6d46dcf2cb@devzone.advantagedatabase.com>
In-Reply-To: <886edc1516a8e8c87f6d46dcf2cb@devzone.advantagedatabase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 216.20.254.254
Message-ID: <44c8ec6e@solutions.advantagedatabase.com>
X-Trace: 27 Jul 2006 10:40:14 -0700, 216.20.254.254
Lines: 67
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!216.20.254.254
Xref: solutions.advantagedatabase.com Advantage.Trigger:264
Article PK: 1136330

This is an AfterInsert trigger.. For some reason it says the code isn't
valid...

I do need to ORDER it, thanks I forgot that.

I haven't look at this for a couple days, but I am sure I will need to
get back to it today.

Thanks again for the help.

Jeremy Mullin wrote:
> Hi Brent,
>
> Doesn't look "way off" to me. Looks like you are pretty close. What
> behavior are you getting? Is this an INSTEAD OF UPDATE trigger, or an
> AFTER UPDATE trigger?
>
> A few thoughts after quickly scanning the script. These may provide some
> direction:
>
> - Should your SELECT TOP 1 statement use an order by clause so you can
> get the largest ID? It will need to be descending too, so something like:
> @oldprojnum = ( SELECT TOP 1 proj_num FROM projects WHERE proj_cid =
> newprojnum.proj_cid AND proj_did = newprojnum.proj_did ORDER BY proj_num
> desc );
>
> - if this is an INSTEAD OF trigger, you need to provide all of the
> fields in the update statement, not just proj_num
>
> J.D. Mullin
> Advantage R&D
>
>> I am still new to triggers and can't seem to figure this out, I want
>> to write a trigger that auto increments a field value based on two
>> other fields in my table.
>>
>> For example, let's say I have a company id, a group id, and a task id.
>> I want to start the task id at zero and every time a record is added
>> with a company and a group id that matches previous entries I want to
>> select the last number and add one to it, if a number isn't there
>> (there aren't previous entries) I want to set it to zero.
>>
>> Here is what I tried:
>>
>> DECLARE newprojnum CURSOR;
>> DECLARE oldprojnum CURSOR;
>> OPEN newprojnum AS SELECT * FROM __new;
>> OPEN
>> TRY
>> @oldprojnum = ( SELECT TOP 1 proj_num FROM projects WHERE proj_cid
>> =
>> newprojnum.proj_cid AND proj_did = newprojnum.proj_did );
>> IF @oldprojnum > -1
>> UPDATE projects SET proj_num = oldprojnum + 1 WHERE proj_id =
>> newprojnum.proj_id;
>> ELSE
>> UPDATE projects SET proj_num = 0 WHERE proj_id =
>> newprojnum.proj_id;
>> END IF;
>> FINALLY
>> CLOSE newprojnum;
>> END TRY;
>> I know that this is way off, but it was just a start... I am very new
>> to this SQL scripting in ADS.
>>
>
>