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.

Record number in a trigger

5 posts in Trigger Last posting was on 2006-08-30 16:15:29.0Z
Lev Pinskiy Posted on 2006-08-08 17:42:26.0Z
Date: Tue, 08 Aug 2006 13:42:26 -0400
From: Lev Pinskiy <levp@rossusa.com>
Organization: Ross Computer Systems, Inc.
User-Agent: Thunderbird 1.5.0.5 (Windows/20060719)
MIME-Version: 1.0
Newsgroups: Advantage.Trigger
Subject: Record number in a trigger
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 209.187.43.69
Message-ID: <44d8cc20@solutions.advantagedatabase.com>
X-Trace: 8 Aug 2006 11:38:40 -0700, 209.187.43.69
Lines: 38
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!209.187.43.69
Xref: solutions.advantagedatabase.com Advantage.Trigger:271
Article PK: 1136336

I currently use DBF/CDX files and have the following triggers on one of
them:

CREATE TRIGGER tad_wk302006itm
ON WK302006ITM
AFTER
DELETE
BEGIN
delete from wkitm where date_=(select date_ from __old) and
item=(select item from __old);
END
PRIORITY 1;

CREATE TRIGGER tau_wk302006itm
ON WK302006ITM
AFTER
UPDATE
BEGIN
delete from wkitm where date_=(select date_ from __old) and
item=(select item from __old);
insert into wkitm select * from __new;
END
PRIORITY 1;

WKITM is a table where records are collected for backup purposes.

The trouble will start the first time the user will have the same ITEM
on the same DATE_ (both records will be deleted/updated, of course).

If setting up some kind of a primary key (thus forcing uniqueness) is
the only way, so be it, but I was wondering if there is any way to use
record numbers for this purpose.

I noticed that such record number is passed as a parameter to a function
if I was to use DLL as a container, but what about regular SQL scripts?

Thanks,
Lev Pinskiy


Jeremy Mullin Posted on 2006-08-16 14:01:56.0Z
Date: Wed, 16 Aug 2006 14:01:56 +0000 (UTC)
Message-ID: <886edc151781d8c88f2160f73ba2@devzone.advantagedatabase.com>
From: Jeremy Mullin <no@email.com>
Subject: Re: Record number in a trigger
Newsgroups: Advantage.Trigger
References: <44d8cc20@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: 16 Aug 2006 07:58:06 -0700, 10.24.38.116
Lines: 57
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.24.38.116
Xref: solutions.advantagedatabase.com Advantage.Trigger:272
Article PK: 1136338

Hi Lev,

The record number is not available in a script trigger, but I'm not sure
it would help here. You couldn't use it in your statements to locate the
record anyway. Also, the way you are handling updates I don't think the record
number in tau_wk302006itm would always match the same record in wkitm. A
unique key would really be the best solution here.

An alternative (although a messy one) would be to use more fields to try
to identify the row.

An unrelated performance suggestion: You might want to try using cursors
to get the values from the old and new tables, rather than executing separate
queries each time you read a value from the tables.

J.D. Mullin
Advantage R&D

> I currently use DBF/CDX files and have the following triggers on one
> of them:
>
> CREATE TRIGGER tad_wk302006itm
> ON WK302006ITM
> AFTER
> DELETE
> BEGIN
> delete from wkitm where date_=(select date_ from __old) and
> item=(select item from __old);
> END
> PRIORITY 1;
> CREATE TRIGGER tau_wk302006itm
> ON WK302006ITM
> AFTER
> UPDATE
> BEGIN
> delete from wkitm where date_=(select date_ from __old) and
> item=(select item from __old);
> insert into wkitm select * from __new;
> END
> PRIORITY 1;
> WKITM is a table where records are collected for backup purposes.
>
> The trouble will start the first time the user will have the same ITEM
> on the same DATE_ (both records will be deleted/updated, of course).
>
> If setting up some kind of a primary key (thus forcing uniqueness) is
> the only way, so be it, but I was wondering if there is any way to use
> record numbers for this purpose.
>
> I noticed that such record number is passed as a parameter to a
> function if I was to use DLL as a container, but what about regular
> SQL scripts?
>
> Thanks,
> Lev Pinskiy


Lev Pinskiy Posted on 2006-08-17 18:21:48.0Z
Date: Thu, 17 Aug 2006 14:21:48 -0400
From: Lev Pinskiy <levp@rossusa.com>
Organization: Ross Computer Systems, Inc.
User-Agent: Thunderbird 1.5.0.5 (Windows/20060719)
MIME-Version: 1.0
Newsgroups: Advantage.Trigger
Subject: Re: Record number in a trigger
References: <44d8cc20@solutions.advantagedatabase.com> <886edc151781d8c88f2160f73ba2@devzone.advantagedatabase.com>
In-Reply-To: <886edc151781d8c88f2160f73ba2@devzone.advantagedatabase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 209.187.43.69
Message-ID: <44e4b2d7@solutions.advantagedatabase.com>
X-Trace: 17 Aug 2006 12:17:59 -0700, 209.187.43.69
Lines: 31
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!209.187.43.69
Xref: solutions.advantagedatabase.com Advantage.Trigger:273
Article PK: 1136340

Thanks. No such thing as miracles, I guess...

Speaking of cursors, I see where __old table is accessed twice, and I'll
change that into a cursor. But __new table is used once:

insert into wkitm select * from __new;

Is cursor going to improve performance in this case?

--
Lev Pinskiy

Jeremy Mullin wrote:

> Hi Lev,
>
> The record number is not available in a script trigger, but I'm not sure
> it would help here. You couldn't use it in your statements to locate the
> record anyway. Also, the way you are handling updates I don't think the
> record number in tau_wk302006itm would always match the same record in
> wkitm. A unique key would really be the best solution here.
> An alternative (although a messy one) would be to use more fields to try
> to identify the row.
>
> An unrelated performance suggestion: You might want to try using cursors
> to get the values from the old and new tables, rather than executing
> separate queries each time you read a value from the tables.
>
> J.D. Mullin
> Advantage R&D


Jeremy Mullin Posted on 2006-08-21 14:27:13.0Z
Date: Mon, 21 Aug 2006 14:27:13 +0000 (UTC)
Message-ID: <886edc1517c308c89312bcc85787@devzone.advantagedatabase.com>
From: Jeremy Mullin <no@email.com>
Subject: Re: Record number in a trigger
Newsgroups: Advantage.Trigger
References: <44e4b2d7@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: 21 Aug 2006 08:23:22 -0700, 10.24.38.116
Lines: 41
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.24.38.116
Xref: solutions.advantagedatabase.com Advantage.Trigger:274
Article PK: 1136341


> Speaking of cursors, I see where __old table is accessed twice, and
> I'll change that into a cursor. But __new table is used once:
>
> insert into wkitm select * from __new;
>
> Is cursor going to improve performance in this case?

Nope.

J.D. Mullin
Advantage R&D


>
> --
> Lev Pinskiy
> Jeremy Mullin wrote:
>
>> Hi Lev,
>>
>> The record number is not available in a script trigger, but I'm not
>> sure
>> it would help here. You couldn't use it in your statements to locate
>> the
>> record anyway. Also, the way you are handling updates I don't think
>> the
>> record number in tau_wk302006itm would always match the same record
>> in
>> wkitm. A unique key would really be the best solution here.
>> An alternative (although a messy one) would be to use more fields to
>> try
>> to identify the row.
>> An unrelated performance suggestion: You might want to try using
>> cursors to get the values from the old and new tables, rather than
>> executing separate queries each time you read a value from the
>> tables.
>>
>> J.D. Mullin
>> Advantage R&D


Jeremy Mullin Posted on 2006-08-30 16:15:29.0Z
Date: Wed, 30 Aug 2006 16:15:29 +0000 (UTC)
Message-ID: <886edc1518eda8c89a345aa9dc63@devzone.advantagedatabase.com>
From: Jeremy Mullin <no@email.com>
Subject: Re: Record number in a trigger
Newsgroups: Advantage.Trigger
References: <44d8cc20@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: 30 Aug 2006 10:11:36 -0700, 10.24.38.116
Lines: 52
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.24.38.116
Xref: solutions.advantagedatabase.com Advantage.Trigger:277
Article PK: 1136344

Just a note for this thread, in case others find it later when searching
for this functionality.

In 8.1 you can access the trigger record number using the statement variable
::stmt.TrigRecNo

::stmt.TrigRecNo -- This variable is only meaningful when accessed inside
a trigger script. It returns the record number of the row that is being affected
and caused the trigger to fire.

J.D. Mullin
Advantage R&D

> I currently use DBF/CDX files and have the following triggers on one
> of them:
>
> CREATE TRIGGER tad_wk302006itm
> ON WK302006ITM
> AFTER
> DELETE
> BEGIN
> delete from wkitm where date_=(select date_ from __old) and
> item=(select item from __old);
> END
> PRIORITY 1;
> CREATE TRIGGER tau_wk302006itm
> ON WK302006ITM
> AFTER
> UPDATE
> BEGIN
> delete from wkitm where date_=(select date_ from __old) and
> item=(select item from __old);
> insert into wkitm select * from __new;
> END
> PRIORITY 1;
> WKITM is a table where records are collected for backup purposes.
>
> The trouble will start the first time the user will have the same ITEM
> on the same DATE_ (both records will be deleted/updated, of course).
>
> If setting up some kind of a primary key (thus forcing uniqueness) is
> the only way, so be it, but I was wondering if there is any way to use
> record numbers for this purpose.
>
> I noticed that such record number is passed as a parameter to a
> function if I was to use DLL as a container, but what about regular
> SQL scripts?
>
> Thanks,
> Lev Pinskiy