Loading ...
Sorry, an error occurred while loading the content.
 

bde error caused by trigger

Expand Messages
  • Yves Glodt
    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
    Message 1 of 13 , Nov 18 3:11 AM
      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?

      best regards,
      Yves
    • Helen Borrie
      ... Point 1: This trigger won t fire because you created it INACTIVE. That means the same as not active , sleeping , ignore me . Point 2: Something
      Message 2 of 13 , Nov 18 3:29 AM
        At 12:11 PM 18/11/2003 +0100, you 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"

        Point 1: This trigger won't fire because you created it INACTIVE. That
        means the same as "not active", "sleeping", "ignore me".

        Point 2: Something unrelated is happening. A Before Insert trigger (if it
        were active, which it isn't) wouldn't have any effect on an update.

        >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

        [10259] isn't a Firebird exception.

        heLen
      • Yves Glodt
        ... ... of the app would change, and it changed. (my error for posting the script of the already desactivated trigger) ... The problem exists only on
        Message 3 of 13 , Nov 18 3:44 AM
          > At 12:11 PM 18/11/2003 +0100, you wrote:
          >
          >>Hello,

          <snip>

          >>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"
          >
          > Point 1: This trigger won't fire because you created it INACTIVE. That
          > means the same as "not active", "sleeping", "ignore me".

          :) well I created it active. I desactivated it to see if the bevaviour
          of the app would change, and it changed.
          (my error for posting the script of the already desactivated trigger)

          > Point 2: Something unrelated is happening. A Before Insert trigger (if
          > it were active, which it isn't) wouldn't have any effect on an update.

          The problem exists only on creating (inserting) records (as I wrote above)
          Updating works fine. Inserting also, as soon as I desactivate the trigger...

          >>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
          >
          > [10259] isn't a Firebird exception.

          yes I know, it's a bde error message (see subject;-) and it's only here FYI


          > heLen
          >
          >
          >
          >
          > To unsubscribe from this group, send an email to:
          > firebird-support-unsubscribe@yahoogroups.com
          >
          >
          >
          > Your use of Yahoo! Groups is subject to
          > http://docs.yahoo.com/info/terms/
        • Alan McDonald
          ... What is NEW.PERS_NUMBER? at first it s an intetger (to get an increment from the generator.. but later it s a varchar... Alan
          Message 4 of 13 , Nov 18 3:56 AM
            > 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
            >

            What is NEW.PERS_NUMBER?
            at first it's an intetger (to get an increment from the generator..
            but later it's a varchar...

            Alan
          • Yves Glodt
            ... In the table it s a varchar(20) field, but it contains only integers. That s why I pad it with zeros. (btw is there a nicer way to do this?) Note that this
            Message 5 of 13 , Nov 18 4:03 AM
              >> 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
              >>
              >
              > What is NEW.PERS_NUMBER?
              > at first it's an intetger (to get an increment from the generator.. but
              > later it's a varchar...

              In the table it's a varchar(20) field, but it contains only integers.
              That's why I pad it with zeros. (btw is there a nicer way to do this?)

              Note that this trigger works fine when I do manual inserts in isql!
              But it fails when the app wants to insert records.
              Any yes, it is activated (opposed to the script above)

              > Alan
              >
              >
              >
              > To unsubscribe from this group, send an email to:
              > firebird-support-unsubscribe@yahoogroups.com
              >
              >
              >
              > Your use of Yahoo! Groups is subject to
              > http://docs.yahoo.com/info/terms/
            • Lucas Franzen
              ... declare variable tmpnr VARCHAR(20); tmpnr = CAST ( NEW.PERS_NUMBER AS VARCHAR(20) ); WHILE ( TMPNR NOT LIKE ( ____________________ ) DO BEGIN /* the
              Message 6 of 13 , Nov 18 4:10 AM
                > In the table it's a varchar(20) field, but it contains only integers.
                > That's why I pad it with zeros. (btw is there a nicer way to do this?)

                declare variable tmpnr VARCHAR(20);

                tmpnr = CAST ( NEW.PERS_NUMBER AS VARCHAR(20) );

                WHILE ( TMPNR NOT LIKE ( '____________________' ) DO
                BEGIN
                /* the compariosn consists of 20 * '_' */
                /* --> if you always want to pad it to the length of 20 */
                tmpnr = '0' || tmpnr;
                END

                NEW.PERS_NUMBER = tmpnr;


                Luc.
              • Helen Borrie
                ... Then, I repeat, something else is going on. A Before Insert trigger fires *after* you post an INSERT statement. Until you post, the server (and
                Message 7 of 13 , Nov 18 4:16 AM
                  At 12:44 PM 18/11/2003 +0100, you wrote:
                  > > At 12:11 PM 18/11/2003 +0100, you wrote:
                  > >
                  > >>Hello,
                  >
                  ><snip>
                  >
                  > >>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"
                  > >
                  > > Point 1: This trigger won't fire because you created it INACTIVE. That
                  > > means the same as "not active", "sleeping", "ignore me".
                  >
                  >:) well I created it active. I desactivated it to see if the bevaviour
                  >of the app would change, and it changed.
                  >(my error for posting the script of the already desactivated trigger)
                  >
                  > > Point 2: Something unrelated is happening. A Before Insert trigger (if
                  > > it were active, which it isn't) wouldn't have any effect on an update.
                  >
                  >The problem exists only on creating (inserting) records (as I wrote above)
                  >Updating works fine. Inserting also, as soon as I desactivate the trigger...

                  Then, I repeat, something else is going on. A Before Insert trigger fires
                  *after* you post an INSERT statement. Until you post, the server (and
                  therefore, the trigger) has no knowledge of anything happening in the
                  application.

                  So the BDE error (if it is the correct error) must apply to something else
                  that is being *edited*, i.e. a row that is already known to the
                  server. It's more likely just the wrong error message and you'd need a
                  crystal ball to identify the real error.

                  If you are using the 16-bit BDE and the Delphi 1 SQLLinks InterBase driver
                  (which was for InterBase 4) then it is about eight years old. It would be
                  some kind of miracle if that driver would work with Firebird, considering
                  even the IB 6 driver had problems with **IB 6**.

                  heLen
                • Helen Borrie
                  ... ***************************************** ... set term ^; create trigger bi_WT_PERS for WT_PERS active before insert position 0 as begin if
                  Message 8 of 13 , Nov 18 4:21 AM
                    At 12:11 PM 18/11/2003 +0100, you 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);

                    *****************************************
                    Here's the original suggested code from last Sunday:

                    >
                    >How would such a trigger/stored proc look like?

                    set term ^;
                    create trigger bi_WT_PERS for WT_PERS
                    active before insert position 0 as
                    begin
                    if (NEW.PERS_NUMBER is null) then
                    NEW.PERS_NUMBER = cast(gen_id(gen_pers_number, 1) as varchar(20));
                    end ^
                    set term ;^

                    **************************
                    Fix up the data type mis-matching and at least *that* error will go away.

                    h.
                  • Yves Glodt
                    ... It s not a primary key, and unfortunately I have no access to the source, we re only a reseller. But as the app itself allows you to execute sql statements
                    Message 9 of 13 , Nov 18 5:02 AM
                      > On 18 Nov 2003 at 12:11, Yves Glodt wrote:
                      >
                      >>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"
                      >
                      > Try YourDataSet.UpdateMode := upWhereKeyOnly;
                      >
                      > If PERS_NUMBER is a primary key, you have to get its new value in
                      > OnNewRecord by another query.

                      It's not a primary key, and unfortunately I have no access to the source,
                      we're only a reseller.

                      But as the app itself allows you to execute sql statements on events
                      (as after create etc) I guess I will drop the trigger and use a stored proc
                      that updates PERS_NUMBER after a record has been inserted.

                      This bde sucks, my trigger works so nice in isq...
                      Thanks to everybody helping out,
                      Yves

                      > SY, Dimitry Sibiryakov.
                      >
                      >
                      >
                      >
                      > To unsubscribe from this group, send an email to:
                      > firebird-support-unsubscribe@yahoogroups.com
                      >
                      >
                      >
                      > Your use of Yahoo! Groups is subject to
                      > http://docs.yahoo.com/info/terms/
                    • Dimitry Sibiryakov
                      ... Try YourDataSet.UpdateMode := upWhereKeyOnly; If PERS_NUMBER is a primary key, you have to get its new value in OnNewRecord by another query. SY, Dimitry
                      Message 10 of 13 , Nov 18 5:30 AM
                        On 18 Nov 2003 at 12:11, Yves Glodt wrote:

                        >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"

                        Try YourDataSet.UpdateMode := upWhereKeyOnly;

                        If PERS_NUMBER is a primary key, you have to get its new value in
                        OnNewRecord by another query.

                        SY, Dimitry Sibiryakov.
                      • Yves Glodt
                        ... ... yes :-( 97K Jun 24 1995 gds.dll ... Well, Interbase6 is a supported DB for this app. I exclusively use FB1.03 and it works like a charm. Tests
                        Message 11 of 13 , Nov 18 10:01 AM
                          On Tuesday 18 November 2003 13:16, Helen Borrie wrote:
                          > At 12:44 PM 18/11/2003 +0100, you wrote:
                          > > > At 12:11 PM 18/11/2003 +0100, you wrote:
                          > > >>Hello,

                          <snip>

                          > If you are using the 16-bit BDE and the Delphi 1 SQLLinks InterBase
                          > driver (which was for InterBase 4) then it is about eight years old.

                          yes :-(
                          97K Jun 24 1995 gds.dll

                          > It would be some kind of miracle if that driver would work with
                          > Firebird, considering even the IB 6 driver had problems with **IB
                          > 6**.

                          Well, Interbase6 is a supported DB for this app. I exclusively use
                          FB1.03 and it works like a charm. Tests show that FB1.5 works also.
                          The app does only "basic" sql.
                          Main thing is it works and I can use FB.


                          > heLen
                          >
                          >
                          >
                          > ------------------------ Yahoo! Groups Sponsor
                          > ---------------------~--> Buy Ink Cartridges or Refill Kits for your
                          > HP, Epson, Canon or Lexmark Printer at MyInks.com. Free s/h on orders
                          > $50 or more to the US & Canada.
                          > http://www.c1tracking.com/l.asp?cid=5511
                          > http://us.click.yahoo.com/mOAaAA/3exGAA/qnsNAA/67folB/TM
                          > ---------------------------------------------------------------------
                          >~->
                          >
                          > To unsubscribe from this group, send an email to:
                          > firebird-support-unsubscribe@yahoogroups.com
                          >
                          >
                          >
                          > Your use of Yahoo! Groups is subject to
                          > http://docs.yahoo.com/info/terms/

                          --
                          Linux 2.4.22-1-k7 #1 Sat Sep 6 02:13:04 EST 2003 i686
                          18:55:40 up 24 min, 1 user, load average: 0.04, 0.18, 0.28
                        • Yves Glodt
                          ... Error? The code I posted works perfectly for me through isql. I need pers_numer as varchar, because I need the leading zeros, and because in the table
                          Message 12 of 13 , Nov 18 10:16 AM
                            On Tuesday 18 November 2003 13:21, Helen Borrie wrote:
                            > At 12:11 PM 18/11/2003 +0100, you 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);
                            >
                            > *****************************************
                            >
                            > Here's the original suggested code from last Sunday:
                            > >How would such a trigger/stored proc look like?
                            >
                            > set term ^;
                            > create trigger bi_WT_PERS for WT_PERS
                            > active before insert position 0 as
                            > begin
                            > if (NEW.PERS_NUMBER is null) then
                            > NEW.PERS_NUMBER = cast(gen_id(gen_pers_number, 1) as
                            > varchar(20)); end ^
                            > set term ;^
                            >
                            > **************************
                            > Fix up the data type mis-matching and at least *that* error will go
                            > away.

                            Error? The code I posted works perfectly for me through isql.
                            I need pers_numer as varchar, because I need the leading zeros, and
                            because in the table definition it's a varchar.
                            The initial problems seems to be a bde issue.

                            thank you

                            > h.
                            >
                            >
                            >
                            > ------------------------ Yahoo! Groups Sponsor
                            > ---------------------~--> Buy Ink Cartridges or Refill Kits for your
                            > HP, Epson, Canon or Lexmark Printer at MyInks.com. Free s/h on orders
                            > $50 or more to the US & Canada.
                            > http://www.c1tracking.com/l.asp?cid=5511
                            > http://us.click.yahoo.com/mOAaAA/3exGAA/qnsNAA/67folB/TM
                            > ---------------------------------------------------------------------
                            >~->
                            >
                            > To unsubscribe from this group, send an email to:
                            > firebird-support-unsubscribe@yahoogroups.com
                            >
                            >
                            >
                            > Your use of Yahoo! Groups is subject to
                            > http://docs.yahoo.com/info/terms/

                            --
                            Linux 2.4.22-1-k7 #1 Sat Sep 6 02:13:04 EST 2003 i686
                            19:03:01 up 32 min, 1 user, load average: 0.15, 0.09, 0.18
                          • Yves Glodt
                            ... 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*
                            Message 13 of 13 , Nov 18 10:18 AM
                              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
                            Your message has been successfully submitted and would be delivered to recipients shortly.