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.

Triggers & Exclusive

8 posts in Trigger Last posting was on 2007-02-16 23:54:02.0Z
"Mike Wilcock" <mike.wilcock<nospam> Posted on 2007-02-08 19:22:01.0Z
From: "Mike Wilcock" <mike.wilcock<nospam>@stanfordtec.co.uk>
Newsgroups: Advantage.Trigger
Subject: Triggers & Exclusive
Date: Thu, 8 Feb 2007 19:22:01 -0000
Lines: 14
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6000.16386
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6000.16386
NNTP-Posting-Host: 91.84.8.228
Message-ID: <45cb7654@solutions.advantagedatabase.com>
X-Trace: 8 Feb 2007 12:13:24 -0700, 91.84.8.228
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!91.84.8.228
Xref: solutions.advantagedatabase.com Advantage.Trigger:305
Article PK: 1136369

Hi,

Just a quick question really. If I create a trigger on a table which
inserts data into the same table (a timestamp after any update for example)
and then open the table exclusively from my client app the trigger execution
fails with a 5177 error.

Is this expected behavior?

Thanks,


Mike


Edgar Sherman Posted on 2007-02-08 20:47:10.0Z
Date: Thu, 08 Feb 2007 13:47:10 -0700
From: Edgar Sherman <no@email.com>
User-Agent: Thunderbird 1.5.0.9 (Windows/20061207)
MIME-Version: 1.0
Newsgroups: Advantage.Trigger
Subject: Re: Triggers & Exclusive
References: <45cb7654@solutions.advantagedatabase.com>
In-Reply-To: <45cb7654@solutions.advantagedatabase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 10.24.34.115
Message-ID: <45cb8b4c@solutions.advantagedatabase.com>
X-Trace: 8 Feb 2007 13:42:52 -0700, 10.24.34.115
Lines: 25
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.24.34.115
Xref: solutions.advantagedatabase.com Advantage.Trigger:306
Article PK: 1136372


Mike Wilcock <mike.wilcock wrote:
> Hi,
>
> Just a quick question really. If I create a trigger on a table which
> inserts data into the same table (a timestamp after any update for
> example) and then open the table exclusively from my client app the
> trigger execution fails with a 5177 error.
>
> Is this expected behavior?
>
> Thanks,
>
>
> Mike

Mike,
The 5177 error should have been returned with an additional error. Do
you know what the error was?

Also, what type of trigger are you using (i.e. Before insert, after
insert...)? And finally what version of the ADS Server are you using?

Edgar Sherman
Advantage Support


"Mike Wilcock" <mike.wilcock<nospam> Posted on 2007-02-09 12:42:54.0Z
From: "Mike Wilcock" <mike.wilcock<nospam>@stanfordtec.co.uk>
Newsgroups: Advantage.Trigger
References: <45cb7654@solutions.advantagedatabase.com> <45cb8b4c@solutions.advantagedatabase.com>
In-Reply-To: <45cb8b4c@solutions.advantagedatabase.com>
Subject: Re: Triggers & Exclusive
Date: Fri, 9 Feb 2007 12:42:54 -0000
Lines: 52
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6000.16386
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6000.16386
NNTP-Posting-Host: 91.84.24.61
Message-ID: <45cc6a4b@solutions.advantagedatabase.com>
X-Trace: 9 Feb 2007 05:34:19 -0700, 91.84.24.61
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!91.84.24.61
Xref: solutions.advantagedatabase.com Advantage.Trigger:308
Article PK: 1136374

Hi Edgar,

Thanks for getting back to me. I've done some further testing this morning
and it's definately to do with the Exclusive opening of the file. If I open
SHARED the triggers work fine, also if I disable the triggers I can open
EXCLUSIVE and do whatever I want.

> Mike,
> The 5177 error should have been returned with an additional error. Do you
> know what the error was?

Here's the error text.

??????????????????????????????????????????????????????
? Error BASE/0 ?
? Description : ?
? Operation : DbCloseArea ?
? Thread ID : 1 ?
? Error 5177: Trigger execution failed. Error ?
? 7200: AQE Error: State = HY000 ?
? NativeError = ?
? 7008 ?
? [Extended Systems][Advantage SQL][ASA] ?
? Error 7008: The specified table, memo file, or ?
? index file was unable to be opened. Table name: ?
? CPFILE ?
? ?
? Cancel Exit with LOG file Ignore ?
??????????????????????????????????????????????????????

> Also, what type of trigger are you using (i.e. Before insert, after
> insert...)? And finally what version of the ADS Server are you using?

I'm using ADS 8.1 and Xbase++ 1.9 to connect.

I have three triggers on the table (insert, update & delete) and they are
all after triggers. Here's the trigger code.

UPDATE CPFILE
SET "DATETIME" = EpsiTimeStamp(), "APPID" = EpsiAppID()
WHERE CPFILE."SALES_NO" = ( SELECT "SALES_NO" FROM __new ) AND
CPFILE."S_CODE" = ( SELECT "S_CODE" FROM __new );

EpsiTimeStamp() and EpsiAppID are two functions that I have created to
format the timestamp and applicationID() charachter strings respectively.

Thanks,


Mike


Edgar Sherman Posted on 2007-02-09 16:30:06.0Z
Date: Fri, 09 Feb 2007 09:30:06 -0700
From: Edgar Sherman <no@email.com>
User-Agent: Thunderbird 1.5.0.9 (Windows/20061207)
MIME-Version: 1.0
Newsgroups: Advantage.Trigger
Subject: Re: Triggers & Exclusive
References: <45cb7654@solutions.advantagedatabase.com> <45cb8b4c@solutions.advantagedatabase.com> <45cc6a4b@solutions.advantagedatabase.com>
In-Reply-To: <45cc6a4b@solutions.advantagedatabase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 10.24.34.115
Message-ID: <45cca08b@solutions.advantagedatabase.com>
X-Trace: 9 Feb 2007 09:25:47 -0700, 10.24.34.115
Lines: 67
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.24.34.115
Xref: solutions.advantagedatabase.com Advantage.Trigger:309
Article PK: 1136375


Mike Wilcock <mike.wilcock wrote:
> Hi Edgar,
>
> Thanks for getting back to me. I've done some further testing this
> morning and it's definately to do with the Exclusive opening of the
> file. If I open SHARED the triggers work fine, also if I disable the
> triggers I can open EXCLUSIVE and do whatever I want.
>
>> Mike,
>> The 5177 error should have been returned with an additional error. Do
>> you
>> know what the error was?
>
> Here's the error text.
>
> ??????????????????????????????????????????????????????
> ? Error BASE/0 ?
> ? Description : ?
> ? Operation : DbCloseArea ?
> ? Thread ID : 1 ?
> ? Error 5177: Trigger execution failed. Error ?
> ? 7200: AQE Error: State = HY000 ?
> ? NativeError = ?
> ? 7008 ?
> ? [Extended Systems][Advantage SQL][ASA] ?
> ? Error 7008: The specified table, memo file, or ?
> ? index file was unable to be opened. Table name: ?
> ? CPFILE ?
> ? ?
> ? Cancel Exit with LOG file Ignore ?
> ??????????????????????????????????????????????????????
>
>> Also, what type of trigger are you using (i.e. Before insert, after
>> insert...)? And finally what version of the ADS Server are you using?
>
> I'm using ADS 8.1 and Xbase++ 1.9 to connect.
>
> I have three triggers on the table (insert, update & delete) and they are
> all after triggers. Here's the trigger code.
>
> UPDATE CPFILE
> SET "DATETIME" = EpsiTimeStamp(), "APPID" = EpsiAppID()
> WHERE CPFILE."SALES_NO" = ( SELECT "SALES_NO" FROM __new ) AND
> CPFILE."S_CODE" = ( SELECT "S_CODE" FROM __new );
>
> EpsiTimeStamp() and EpsiAppID are two functions that I have created to
> format the timestamp and applicationID() charachter strings respectively.
>
> Thanks,
>
>
> Mike
>
>

Mike,

I think I will have to defer and ask JD to chime in with his opinion.

Since you are using after triggers, this should take care of record
locking issues, however, I don't know if it will deal properly with
exclusivity on the table.

I am torn between stating that this is expected or not (I could easily
argue for either). In this case I think JD could give us a better answer.

Edgar


"Mike Wilcock" <mike.wilcock<nospam> Posted on 2007-02-09 19:21:31.0Z
From: "Mike Wilcock" <mike.wilcock<nospam>@stanfordtec.co.uk>
Newsgroups: Advantage.Trigger
References: <45cb7654@solutions.advantagedatabase.com> <45cb8b4c@solutions.advantagedatabase.com> <45cc6a4b@solutions.advantagedatabase.com> <45cca08b@solutions.advantagedatabase.com>
In-Reply-To: <45cca08b@solutions.advantagedatabase.com>
Subject: Re: Triggers & Exclusive
Date: Fri, 9 Feb 2007 19:21:31 -0000
Lines: 89
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6000.16386
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6000.16386
NNTP-Posting-Host: 91.84.8.228
Message-ID: <45ccc7b7@solutions.advantagedatabase.com>
X-Trace: 9 Feb 2007 12:12:55 -0700, 91.84.8.228
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!91.84.8.228
Xref: solutions.advantagedatabase.com Advantage.Trigger:310
Article PK: 1136376

Hi Edgar,

Thanks for your input. I'll wait to hear what Joachim thinks. I've just
started porting our Xbase++ app to ADS and Ideally I'd like to have these
triggers on each table so that we can track who did what and when. It will
also make things easier when we implement replication.

Only thing is there are still a few (not many) places where we do open
certain files Exclusively at certain times, such as month end etc. to stop
users on other stations posting transactions to the ledger. If the triggers
wont fire on an exclusively opened table I guess a redesign of these
portions of the code will be required.

Anyway thanks again and I'll wait to here Joachim's thoughts...


Mike

"Edgar Sherman" <no@email.com> wrote in message
news:45cca08b@solutions.advantagedatabase.com...
> Mike Wilcock <mike.wilcock wrote:
>> Hi Edgar,
>>
>> Thanks for getting back to me. I've done some further testing this
>> morning and it's definately to do with the Exclusive opening of the file.
>> If I open SHARED the triggers work fine, also if I disable the triggers I
>> can open EXCLUSIVE and do whatever I want.
>>
>>> Mike,
>>> The 5177 error should have been returned with an additional error. Do
>>> you
>>> know what the error was?
>>
>> Here's the error text.
>>
>> ??????????????????????????????????????????????????????
>> ? Error BASE/0 ?
>> ? Description : ?
>> ? Operation : DbCloseArea ?
>> ? Thread ID : 1 ?
>> ? Error 5177: Trigger execution failed. Error ?
>> ? 7200: AQE Error: State = HY000 ?
>> ? NativeError = ?
>> ? 7008 ?
>> ? [Extended Systems][Advantage SQL][ASA] ?
>> ? Error 7008: The specified table, memo file, or ?
>> ? index file was unable to be opened. Table name: ?
>> ? CPFILE ?
>> ? ?
>> ? Cancel Exit with LOG file Ignore ?
>> ??????????????????????????????????????????????????????
>>
>>> Also, what type of trigger are you using (i.e. Before insert, after
>>> insert...)? And finally what version of the ADS Server are you using?
>>
>> I'm using ADS 8.1 and Xbase++ 1.9 to connect.
>>
>> I have three triggers on the table (insert, update & delete) and they are
>> all after triggers. Here's the trigger code.
>>
>> UPDATE CPFILE
>> SET "DATETIME" = EpsiTimeStamp(), "APPID" = EpsiAppID()
>> WHERE CPFILE."SALES_NO" = ( SELECT "SALES_NO" FROM __new ) AND
>> CPFILE."S_CODE" = ( SELECT "S_CODE" FROM __new );
>>
>> EpsiTimeStamp() and EpsiAppID are two functions that I have created to
>> format the timestamp and applicationID() charachter strings respectively.
>>
>> Thanks,
>>
>>
>> Mike
>>
>>
>
> Mike,
>
> I think I will have to defer and ask JD to chime in with his opinion.
>
> Since you are using after triggers, this should take care of record
> locking issues, however, I don't know if it will deal properly with
> exclusivity on the table.
>
> I am torn between stating that this is expected or not (I could easily
> argue for either). In this case I think JD could give us a better answer.
>
> Edgar


Edgar Sherman Posted on 2007-02-13 17:18:24.0Z
Date: Tue, 13 Feb 2007 10:18:24 -0700
From: Edgar Sherman <no@email.com>
User-Agent: Thunderbird 1.5.0.9 (Windows/20061207)
MIME-Version: 1.0
Newsgroups: Advantage.Trigger
Subject: Re: Triggers & Exclusive
References: <45cb7654@solutions.advantagedatabase.com> <45cb8b4c@solutions.advantagedatabase.com> <45cc6a4b@solutions.advantagedatabase.com> <45cca08b@solutions.advantagedatabase.com> <45ccc7b7@solutions.advantagedatabase.com>
In-Reply-To: <45ccc7b7@solutions.advantagedatabase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 10.24.34.115
Message-ID: <45d1f1d8@solutions.advantagedatabase.com>
X-Trace: 13 Feb 2007 10:14:00 -0700, 10.24.34.115
Lines: 107
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.24.34.115
Xref: solutions.advantagedatabase.com Advantage.Trigger:315
Article PK: 1136381


Mike Wilcock <mike.wilcock wrote:
> Hi Edgar,
>
> Thanks for your input. I'll wait to hear what Joachim thinks. I've
> just started porting our Xbase++ app to ADS and Ideally I'd like to have
> these triggers on each table so that we can track who did what and
> when. It will also make things easier when we implement replication.
>
> Only thing is there are still a few (not many) places where we do open
> certain files Exclusively at certain times, such as month end etc. to
> stop users on other stations posting transactions to the ledger. If the
> triggers wont fire on an exclusively opened table I guess a redesign of
> these portions of the code will be required.
>
> Anyway thanks again and I'll wait to here Joachim's thoughts...
>
>
> Mike
>
>
> "Edgar Sherman" <no@email.com> wrote in message
> news:45cca08b@solutions.advantagedatabase.com...
>> Mike Wilcock <mike.wilcock wrote:
>>> Hi Edgar,
>>>
>>> Thanks for getting back to me. I've done some further testing this
>>> morning and it's definately to do with the Exclusive opening of the
>>> file. If I open SHARED the triggers work fine, also if I disable the
>>> triggers I can open EXCLUSIVE and do whatever I want.
>>>
>>>> Mike,
>>>> The 5177 error should have been returned with an additional error.
>>>> Do you
>>>> know what the error was?
>>>
>>> Here's the error text.
>>>
>>> ??????????????????????????????????????????????????????
>>> ? Error BASE/0 ?
>>> ? Description : ?
>>> ? Operation : DbCloseArea ?
>>> ? Thread ID : 1 ?
>>> ? Error 5177: Trigger execution failed. Error ?
>>> ? 7200: AQE Error: State = HY000 ?
>>> ? NativeError = ?
>>> ? 7008 ?
>>> ? [Extended Systems][Advantage SQL][ASA] ?
>>> ? Error 7008: The specified table, memo file, or ?
>>> ? index file was unable to be opened. Table name: ?
>>> ? CPFILE ?
>>> ? ?
>>> ? Cancel Exit with LOG file Ignore ?
>>> ??????????????????????????????????????????????????????
>>>
>>>> Also, what type of trigger are you using (i.e. Before insert, after
>>>> insert...)? And finally what version of the ADS Server are you using?
>>>
>>> I'm using ADS 8.1 and Xbase++ 1.9 to connect.
>>>
>>> I have three triggers on the table (insert, update & delete) and they
>>> are
>>> all after triggers. Here's the trigger code.
>>>
>>> UPDATE CPFILE
>>> SET "DATETIME" = EpsiTimeStamp(), "APPID" = EpsiAppID()
>>> WHERE CPFILE."SALES_NO" = ( SELECT "SALES_NO" FROM __new ) AND
>>> CPFILE."S_CODE" = ( SELECT "S_CODE" FROM __new );
>>>
>>> EpsiTimeStamp() and EpsiAppID are two functions that I have created to
>>> format the timestamp and applicationID() charachter strings
>>> respectively.
>>>
>>> Thanks,
>>>
>>>
>>> Mike
>>>
>>>
>>
>> Mike,
>>
>> I think I will have to defer and ask JD to chime in with his opinion.
>>
>> Since you are using after triggers, this should take care of record
>> locking issues, however, I don't know if it will deal properly with
>> exclusivity on the table.
>>
>> I am torn between stating that this is expected or not (I could easily
>> argue for either). In this case I think JD could give us a better
>> answer.
>>
>> Edgar
>

Mike,

I had a chance to discuss this issue with R&D. Most likely what you
have found is a bug, however I need to confirm this.

I have a small re-creation to give to R&D and I will let you know when I
get an update on this.

BTW, You only get the error if you have an index on the table. Not
really a workaround, but thought I would mention it.

Edgar Sherman


"Mike Wilcock" <mike.wilcock<nospam> Posted on 2007-02-14 11:25:20.0Z
From: "Mike Wilcock" <mike.wilcock<nospam>@stanfordtec.co.uk>
Newsgroups: Advantage.Trigger
References: <45cb7654@solutions.advantagedatabase.com> <45cb8b4c@solutions.advantagedatabase.com> <45cc6a4b@solutions.advantagedatabase.com> <45cca08b@solutions.advantagedatabase.com> <45ccc7b7@solutions.advantagedatabase.com> <45d1f1d8@solutions.advantagedatabase.com>
In-Reply-To: <45d1f1d8@solutions.advantagedatabase.com>
Subject: Re: Triggers & Exclusive
Date: Wed, 14 Feb 2007 11:25:20 -0000
Lines: 22
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6000.16386
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6000.16386
NNTP-Posting-Host: 91.84.24.61
Message-ID: <45d2ef98@solutions.advantagedatabase.com>
X-Trace: 14 Feb 2007 04:16:40 -0700, 91.84.24.61
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!91.84.24.61
Xref: solutions.advantagedatabase.com Advantage.Trigger:316
Article PK: 1136383

Hi Edgar,

Thanks for keeping me updated, I'll wait to hear what R&D come back with.


Thanks,


Mike

> Mike,
>
> I had a chance to discuss this issue with R&D. Most likely what you
> have found is a bug, however I need to confirm this.
>
> I have a small re-creation to give to R&D and I will let you know when I
> get an update on this.
>
> BTW, You only get the error if you have an index on the table. Not
> really a workaround, but thought I would mention it.
>
> Edgar Sherman


Edgar Sherman Posted on 2007-02-16 23:54:02.0Z
Date: Fri, 16 Feb 2007 16:54:02 -0700
From: Edgar Sherman <no@email.com>
User-Agent: Thunderbird 1.5.0.9 (Windows/20061207)
MIME-Version: 1.0
Newsgroups: Advantage.Trigger
Subject: Re: Triggers & Exclusive
References: <45cb7654@solutions.advantagedatabase.com> <45cb8b4c@solutions.advantagedatabase.com> <45cc6a4b@solutions.advantagedatabase.com> <45cca08b@solutions.advantagedatabase.com> <45ccc7b7@solutions.advantagedatabase.com> <45d1f1d8@solutions.advantagedatabase.com> <45d2ef98@solutions.advantagedatabase.com>
In-Reply-To: <45d2ef98@solutions.advantagedatabase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 10.24.34.109
Message-ID: <45d6431a@solutions.advantagedatabase.com>
X-Trace: 16 Feb 2007 16:49:46 -0700, 10.24.34.109
Lines: 33
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.24.34.109
Xref: solutions.advantagedatabase.com Advantage.Trigger:319
Article PK: 1136386

Mike,

R&D did confirm it was a bug. They will have the fix in the next
service update of ADS 8.1. There is no official date for when the next
service release will be available but I would image possibly sometime in
April, but don't quote me on that:).

Edgar Sherman
Advantage Support

Mike Wilcock <mike.wilcock wrote:
> Hi Edgar,
>
> Thanks for keeping me updated, I'll wait to hear what R&D come back with.
>
>
> Thanks,
>
>
> Mike
>
>> Mike,
>>
>> I had a chance to discuss this issue with R&D. Most likely what you
>> have found is a bug, however I need to confirm this.
>>
>> I have a small re-creation to give to R&D and I will let you know when
>> I get an update on this.
>>
>> BTW, You only get the error if you have an index on the table. Not
>> really a workaround, but thought I would mention it.
>>
>> Edgar Sherman