On Tuesday 18 November 2003 12:11, Yves Glodt wrote:

> Hello,
>
> I have this trigger (thanks to heLen for her help):
>
> CREATE TRIGGER CREATE_PERS_NUMBER FOR WT_PERS
> INACTIVE BEFORE INSERT POSITION 0
> as
> DECLARE VARIABLE prefix varchar(5);
> begin
> /* if (NEW.PERS_NUMBER is null) then NEW.PERS_NUMBER =
> gen_id(gen_pers_number, 1); */
> NEW.PERS_NUMBER = gen_id(gen_pers_number, 1);
> if ((NEW.PERS_NUMBER >= 0) and (NEW.PERS_NUMBER < 10)) then prefix =
> '0000'; if ((NEW.PERS_NUMBER >= 10) and (NEW.PERS_NUMBER < 100)) then
> prefix = '000';
> if ((NEW.PERS_NUMBER >= 100) and (NEW.PERS_NUMBER < 1000)) then
> prefix = '00';
> if ((NEW.PERS_NUMBER >= 1000) and (NEW.PERS_NUMBER < 10000)) then
> prefix = '0';
> NEW.PERS_NUMBER = prefix || cast(NEW.PERS_NUMBER as varchar(20));
> end
>
>
> Now when I create a new record via the application, I get tis
> message: "[10259] Couldn't perform the edit because another user
> changed the record"
>
> I'm not sure there is a way to work around this... The bde is a
> dependency of the appcation (it's the old 16 bit bde 2.52).
> Bde settings:
> SQLPASSTHRUMODE = SHARED NOAUTOCOMMIT
> SQLQRYMODE = SERVER
>
>
> Any pointers?

I got the following answer privately, and it describes what happens with
the bde. The app seems to make a select after the insert (which
actually *gets* done, but in the after-insert-logic the initial error
occurs)
I post it here for info and for the archives:


Yves,

2 things, firstly I would change your logic in trigger to incorporate if
then else, less to evaluate, easier to read and less chance of bugs.

The problem you have is that the BDE dataset is either using this filed
or a combination of fields that include this field so it can re-select
the inserted row to allow for triggers that may change the posted data. 
When it does its select, it gets 0 rows back as you have changed its
perceived PK.

I call a SP to get the next ID and then assign that client side, post
the record so your Trigger becomes:

create trigger CREATE_PERS_NUMBER for WT_PERS
before insert position 0
as
declare variable prefix varchar(5);
begin
if (new.PERS_NUMBER is null) then
  new.PERS_NUMBER = gen_id(gen_pers_number, 1);
if (new.PERS_NUMBER >= 10000) then
 do something here, exception maybe
else if (new.PERS_NUMBER >= 1000) then
 prefix= '0'
else if (new.PERS_NUMBER >= 100)  then
prefix ='00'
else if (new.PERS_NUMBER >= 10) then
prefix = '000'
else if (new.PERS_NUMBER >= 0) then
prefix = '0000'
else
  do something like excpetions as < 0
new.PERS_NUMBER = prefix || cast(new.PERS_NUMBER as varchar(20));
end


--
Linux 2.4.22-1-k7 #1 Sat Sep 6 02:13:04 EST 2003 i686
19:12:08 up 41 min, 1 user, load average: 0.20, 0.14, 0.16