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.

trigger problem

5 posts in Trigger Last posting was on 2007-02-20 15:21:27.0Z
Randall H. Carpenter Posted on 2007-02-10 22:07:05.0Z
From: "Randall H. Carpenter" <rhcarpenter@ncgm.com>
Newsgroups: Advantage.Trigger
Subject: trigger problem
Date: Sat, 10 Feb 2007 17:07:05 -0500
Lines: 47
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
NNTP-Posting-Host: 162.39.125.18
Message-ID: <45ce4103@solutions.advantagedatabase.com>
X-Trace: 10 Feb 2007 15:02:43 -0700, 162.39.125.18
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!162.39.125.18
Xref: solutions.advantagedatabase.com Advantage.Trigger:311
Article PK: 1136377

Just got back from the Advantage Technical Summit. This was our first and
it was excellent. Gleaned alot of great information we plan to use
immediately to make our application more functional, stable, and secure.
Thanks for the opportunity. The Advantage team is very courteous and
professional. Thanks again.

I have a question about triggers.

I am using Advantage 8.0 (will update to 8.1 probably this week) with Delphi
7.1.

Opened ARC planning to implement a trigger on a specific table (tablename:
POLICY). Started writing the script but then ran into a problem. Hopefully
its not a problem but just my lack of SQL knowledge.

The trigger type was AFTER and the event type was DELETE. After I delete a
POLICY record I want to automatically delete all the LOCATION records
associated with that POLICY record. But my key fields in each table are of
the same name. Will the following work? It verifies OK:

delete from LOCATION where company = company and
policy = policy and
sequence = sequence

I started to use the following:

delete from LOCATION where location.company = policy.company and
location.policy = policy.policy and
location.sequence = policy.sequence

but the verifier came back with "Table or alias not found: policy". How
does the parser understand the which table is which in the condition test?

Since the verifier OKed my code I decided to test it.

I opened the POLICY table and deleted a record which I knew had eight
related records in the LOCATION table. It came back with the following
error:

Error 7007: Maximum number of locks exceeded

Any help to all these questions will be appreciated. Thanks.

Randall H. Carpenter


Joachim Duerr (ADS) Posted on 2007-02-12 08:55:00.0Z
From: "Joachim Duerr (ADS)" <jojo.duerr@gmx.de>
Subject: Re: trigger problem
Newsgroups: Advantage.Trigger
References: <45ce4103@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.118
Message-ID: <45d02b64@solutions.advantagedatabase.com>
Date: 12 Feb 2007 01:55:00 -0700
X-Trace: 12 Feb 2007 01:55:00 -0700, 10.56.66.118
Lines: 43
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.56.66.118
Xref: solutions.advantagedatabase.com Advantage.Trigger:313
Article PK: 1136378

Randall H. Carpenter wrote in
<45ce4103@solutions.advantagedatabase.com>:

> delete from LOCATION where company = company and
> policy = policy and
> sequence = sequence
>
> I started to use the following:
>
> delete from LOCATION where location.company = policy.company and
> location.policy = policy.policy and
> location.sequence = policy.sequence
>
> but the verifier came back with "Table or alias not found: policy".
> How does the parser understand the which table is which in the
> condition test?

this trigger will delete all the records in the table. You should refer
the __old record.

delete from location
where company= (select company from __old)
and policy= (select policy from __old)
and sequence=(select sequence from __old);


> Since the verifier OKed my code I decided to test it.
>
> I opened the POLICY table and deleted a record which I knew had eight
> related records in the LOCATION table. It came back with the
> following error:
>
> Error 7007: Maximum number of locks exceeded

the trigger is being executed inside a transaction (during the
transaction the records are being kept locked)...and since you deleted
that much records, you consumed too much locks.

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


Randall H. Carpenter Posted on 2007-02-12 17:20:41.0Z
From: "Randall H. Carpenter" <rhcarpenter@ncgm.com>
Newsgroups: Advantage.Trigger
References: <45ce4103@solutions.advantagedatabase.com> <45d02b64@solutions.advantagedatabase.com>
Subject: Re: trigger problem
Date: Mon, 12 Feb 2007 12:20:41 -0500
Lines: 51
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: 162.39.125.18
Message-ID: <45d0a0d1@solutions.advantagedatabase.com>
X-Trace: 12 Feb 2007 10:16:01 -0700, 162.39.125.18
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!162.39.125.18
Xref: solutions.advantagedatabase.com Advantage.Trigger:314
Article PK: 1136379

Well that makes sense. Duh! Thanks for your help.

Randall

"Joachim Duerr (ADS)" <jojo.duerr@gmx.de> wrote in message
news:45d02b64@solutions.advantagedatabase.com...
> Randall H. Carpenter wrote in
> <45ce4103@solutions.advantagedatabase.com>:
>
>> delete from LOCATION where company = company and
>> policy = policy and
>> sequence = sequence
>>
>> I started to use the following:
>>
>> delete from LOCATION where location.company = policy.company and
>> location.policy = policy.policy and
>> location.sequence = policy.sequence
>>
>> but the verifier came back with "Table or alias not found: policy".
>> How does the parser understand the which table is which in the
>> condition test?
>
> this trigger will delete all the records in the table. You should refer
> the __old record.
>
> delete from location
> where company= (select company from __old)
> and policy= (select policy from __old)
> and sequence=(select sequence from __old);
>
>
>> Since the verifier OKed my code I decided to test it.
>>
>> I opened the POLICY table and deleted a record which I knew had eight
>> related records in the LOCATION table. It came back with the
>> following error:
>>
>> Error 7007: Maximum number of locks exceeded
>
> the trigger is being executed inside a transaction (during the
> transaction the records are being kept locked)...and since you deleted
> that much records, you consumed too much locks.
>
> --
> Joachim Duerr
> System Consultant (Advantage Database Server)
> Sybase iAnywhere
> advantagesupport[AT]ianywhere.com


Jeremy Mullin Posted on 2007-02-20 15:21:27.0Z
Date: Tue, 20 Feb 2007 15:21:27 +0000 (UTC)
Message-ID: <886edc152432e8c922df407b83da@devzone.advantagedatabase.com>
From: Jeremy Mullin <no@email.com>
Subject: Re: trigger problem
Newsgroups: Advantage.Trigger
References: <45d02b64@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.124
X-Trace: 20 Feb 2007 08:17:02 -0700, 10.24.38.124
Lines: 55
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.24.38.124
Xref: solutions.advantagedatabase.com Advantage.Trigger:320
Article PK: 1136385


> delete from location
> where company= (select company from __old)
> and policy= (select policy from __old)
> and sequence=(select sequence from __old);

This has to execute 3 extra statements, you may consider using a cursor to
open the __old table, so you only incur the overhead of 1 additional sql
execution:

declare old cursor as select * from __old;
open old;
fetch old;
delete from location
where company= old.company
and policy= old.policy
and sequence= old.sequence;

J.D. Mullin
Advantage R&D

> Randall H. Carpenter wrote in
> <45ce4103@solutions.advantagedatabase.com>:
>> delete from LOCATION where company = company and
>> policy = policy and
>> sequence = sequence
>> I started to use the following:
>>
>> delete from LOCATION where location.company = policy.company and
>> location.policy = policy.policy and
>> location.sequence = policy.sequence
>> but the verifier came back with "Table or alias not found: policy".
>> How does the parser understand the which table is which in the
>> condition test?
>>
> this trigger will delete all the records in the table. You should
> refer the __old record.
>
> delete from location
> where company= (select company from __old)
> and policy= (select policy from __old)
> and sequence=(select sequence from __old);
>> Since the verifier OKed my code I decided to test it.
>>
>> I opened the POLICY table and deleted a record which I knew had eight
>> related records in the LOCATION table. It came back with the
>> following error:
>>
>> Error 7007: Maximum number of locks exceeded
>>
> the trigger is being executed inside a transaction (during the
> transaction the records are being kept locked)...and since you deleted
> that much records, you consumed too much locks.
>


GreenStar Posted on 2007-02-12 03:31:51.0Z
From: "GreenStar" <sobincor@inbox.ru>
Newsgroups: Advantage.Trigger
References: <45ce4103@solutions.advantagedatabase.com>
Subject: Re: trigger problem
Date: Mon, 12 Feb 2007 08:31:51 +0500
Lines: 90
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: 82.198.22.2
Message-ID: <45cfdedb@solutions.advantagedatabase.com>
X-Trace: 11 Feb 2007 20:28:27 -0700, 82.198.22.2
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!82.198.22.2
Xref: solutions.advantagedatabase.com Advantage.Trigger:312
Article PK: 1136380

The best way, it's using Referential integrity in Your case. Please see RI
objects in ARC.
About using Triggers. The sample script:
Declare tbNew Cursor as Select * from __new;
Declare tbWHOper Cursor as Select * from WHOperations Where WHOperations.ID
= tbNew.IDDoc;
Declare tbWHSetup Cursor as Select * from WHSetups;
Declare dvDate Date;
Declare tbWHRests Cursor as Select * from WHRests Where ( WHRests.RestDate =
dvDate ) And
( WHRests.Product = tbNew.Product );
Open tbNew;
Open tbWHSetup;
Fetch tbNew;
Fetch tbWHSetup;
Open tbWHOper;
Fetch tbWHOper;
dvDate = tbWHOper.OperDate;

Update WHIncDetails Set Summa = Price * Amount
Where ID = NCursor.ID;
if not Fetch tbWHRests then
Insert into WHRests( RestDate, Product ) Values( tbWHSetup.OperDate,
tbNew.Product );
endif;
Update WHRests Set RestAmount = RestAmount + tbNew.Amount,
Summa = Summa + tbNew.Summa
Where RestDate = dvDate and Product = tbNew.Product;

Close tbNew;
Close tbWHOper;
Close tbWHSetup;
Close tbWHRests;

In addition you have to read the ADS documentation. Esspesially about using
system __new and __old cursors.
With best regards...


"Randall H. Carpenter" <rhcarpenter@ncgm.com> ???????/???????? ? ????????
?????????: news:45ce4103@solutions.advantagedatabase.com...

> Just got back from the Advantage Technical Summit. This was our first and
> it was excellent. Gleaned alot of great information we plan to use
> immediately to make our application more functional, stable, and secure.
> Thanks for the opportunity. The Advantage team is very courteous and
> professional. Thanks again.
>
> I have a question about triggers.
>
> I am using Advantage 8.0 (will update to 8.1 probably this week) with
> Delphi 7.1.
>
> Opened ARC planning to implement a trigger on a specific table (tablename:
> POLICY). Started writing the script but then ran into a problem.
> Hopefully its not a problem but just my lack of SQL knowledge.
>
> The trigger type was AFTER and the event type was DELETE. After I delete
> a POLICY record I want to automatically delete all the LOCATION records
> associated with that POLICY record. But my key fields in each table are
> of the same name. Will the following work? It verifies OK:
>
> delete from LOCATION where company = company and
> policy = policy and
> sequence = sequence
>
> I started to use the following:
>
> delete from LOCATION where location.company = policy.company and
> location.policy = policy.policy and
> location.sequence = policy.sequence
>
> but the verifier came back with "Table or alias not found: policy". How
> does the parser understand the which table is which in the condition test?
>
> Since the verifier OKed my code I decided to test it.
>
> I opened the POLICY table and deleted a record which I knew had eight
> related records in the LOCATION table. It came back with the following
> error:
>
> Error 7007: Maximum number of locks exceeded
>
> Any help to all these questions will be appreciated. Thanks.
>
> Randall H. Carpenter
>
>
>