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

possible trigger problem under 1.5.4.4910

Expand Messages
  • sjlukacs
    hello one and all, i am having some weird intermittent problem with the triggers under 1.5.4.4910. basically, a detail table updates an accrual field in the
    Message 1 of 10 , Aug 1, 2007
    • 0 Attachment
      hello one and all,

      i am having some weird intermittent problem with the triggers under
      1.5.4.4910. basically, a detail table updates an accrual field in the
      master table. essentially, the accrual fields are not being updated
      properly by the triggers of the detail table. i rewrote the triggers
      to ensure that nulls were not entering into the problem. the code for
      these triggers have existed for years and i have never had the problem
      until i upgraded to 4910. my biggest problem is that i am having such
      a hard time reproducing the bug because it is intermittent and at this
      point i can't seem to see why it is doing it. the only way that i
      know it exists is that the accrual field doesn't add to the sum of the
      detail, but again this is in the minority.

      i do remember a couple years back when a 1.5 release didn't run the
      triggers at all. this bug was fixed very fast back then. however,
      this one is very intermittent and has me stumped. any ideas?

      lucas
    • Helen Borrie
      ... Check that you don t have any AFTER triggers that are trying to change the NEW values; nor any DELETE triggers trying to refer to NEW values; nor any
      Message 2 of 10 , Aug 1, 2007
      • 0 Attachment
        At 10:51 PM 1/08/2007, you wrote:
        >hello one and all,
        >
        >i am having some weird intermittent problem with the triggers under
        >1.5.4.4910. basically, a detail table updates an accrual field in the
        >master table. essentially, the accrual fields are not being updated
        >properly by the triggers of the detail table. i rewrote the triggers
        >to ensure that nulls were not entering into the problem. the code for
        >these triggers have existed for years and i have never had the problem
        >until i upgraded to 4910. my biggest problem is that i am having such
        >a hard time reproducing the bug because it is intermittent and at this
        >point i can't seem to see why it is doing it. the only way that i
        >know it exists is that the accrual field doesn't add to the sum of the
        >detail, but again this is in the minority.
        >
        >i do remember a couple years back when a 1.5 release didn't run the
        >triggers at all. this bug was fixed very fast back then. however,
        >this one is very intermittent and has me stumped. any ideas?

        Check that you don't have any AFTER triggers that are trying to
        change the NEW values; nor any DELETE triggers trying to refer to
        NEW values; nor any INSERT triggers that are trying to refer to OLD values.

        ./heLen



        >lucas
        >
        >
        >
        >++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
        >
        >Visit http://www.firebirdsql.org and click the Resources item
        >on the main (top) menu. Try Knowledgebase and FAQ links !
        >
        >Also search the knowledgebases at http://www.ibphoenix.com
        >
        >++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
        >
        >Yahoo! Groups Links
        >
        >
        >
      • sjlukacs
        hello helen, i did check that awhile back and none of those are the case. it is all strict. before insert or update apply NEWs with no reference to OLDs,
        Message 3 of 10 , Aug 1, 2007
        • 0 Attachment
          hello helen,

          i did check that awhile back and none of those are the case. it is
          all strict. before insert or update apply NEWs with no reference to
          OLDs, after insert or update update other tables, which is where the
          accrual field update is, before or after delete only uses OLDs, and
          the after delete also updates the accrual field. restated just to
          double check my thinking on that point.

          hey, thanx for the quick response.

          lucas

          --- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:
          >
          > At 10:51 PM 1/08/2007, you wrote:
          > >hello one and all,
          > >
          > >i am having some weird intermittent problem with the triggers under
          > >1.5.4.4910. basically, a detail table updates an accrual field in the
          > >master table. essentially, the accrual fields are not being updated
          > >properly by the triggers of the detail table. i rewrote the triggers
          > >to ensure that nulls were not entering into the problem. the code for
          > >these triggers have existed for years and i have never had the problem
          > >until i upgraded to 4910. my biggest problem is that i am having such
          > >a hard time reproducing the bug because it is intermittent and at this
          > >point i can't seem to see why it is doing it. the only way that i
          > >know it exists is that the accrual field doesn't add to the sum of the
          > >detail, but again this is in the minority.
          > >
          > >i do remember a couple years back when a 1.5 release didn't run the
          > >triggers at all. this bug was fixed very fast back then. however,
          > >this one is very intermittent and has me stumped. any ideas?
          >
          > Check that you don't have any AFTER triggers that are trying to
          > change the NEW values; nor any DELETE triggers trying to refer to
          > NEW values; nor any INSERT triggers that are trying to refer to OLD
          values.
          >
          > ./heLen
          >
          >
          >
          > >lucas
          > >
          > >
          > >
          > >++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
          > >
          > >Visit http://www.firebirdsql.org and click the Resources item
          > >on the main (top) menu. Try Knowledgebase and FAQ links !
          > >
          > >Also search the knowledgebases at http://www.ibphoenix.com
          > >
          > >++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
          > >
          > >Yahoo! Groups Links
          > >
          > >
          > >
          >
        • Aage Johansen
          ... Maybe I misunderstand (or don t understand accrual ), but when you say no reference to OLDs I must ask: Don t you use something like new.AMOUNT =
          Message 4 of 10 , Aug 1, 2007
          • 0 Attachment
            sjlukacs wrote:
            > i did check that awhile back and none of those are the case. it is
            > all strict. before insert or update apply NEWs with no reference to
            > OLDs, ...

            Maybe I misunderstand (or don't understand 'accrual'), but when you
            say "no reference to OLDs" I must ask: Don't you use something like
            new.AMOUNT = old.AMOUNT + ...
            in the BeforeUpdate trigger ?

            1. Maybe you could post the "interesting" part of the trigger
            2. You might consider logging something to an external table (just to
            ascertain the the trigger is indeed triggered).


            --
            Aage J.
          • sjlukacs
            hello one and all, ok, so i added some code to the troublsome triggers to log entries in a separate generic VARS table that i created to track the firing of
            Message 5 of 10 , Aug 11, 2007
            • 0 Attachment
              hello one and all,

              ok, so i added some code to the troublsome triggers to log entries in
              a separate generic VARS table that i created to track the firing of
              the suspect triggers. it took a bit of time to collect the data.
              here is the code of one of the suspect triggers:

              CREATE TRIGGER ROMECH_AI FOR ROMECH AFTER INSERT
              AS
              DECLARE VARIABLE adate CHAR(1);
              DECLARE VARIABLE idate DATE;
              DECLARE VARIABLE fdate DATE;
              DECLARE VARIABLE ctotal NUMERIC(15,3);
              DECLARE VARIABLE rtotal NUMERIC(15,3);
              DECLARE VARIABLE id DATE;
              DECLARE VARIABLE fd DATE;
              BEGIN
              INSERT INTO VARS (EVENT,SI1,SI2,SI3,SI4,RF1,RF2) VALUES ("BEGIN
              ROMECH_AI",NEW.IDIV,NEW.IRO,NEW.IROVMRS,NEW.IMECHANIC,NEW.CTOTAL,NEW.RTOTAL);
              SELECT R.IDATE, R.FDATE, R.CTOTAL_MECH, R.RTOTAL_MECH FROM RO R WHERE
              (R.IDIV = NEW.IDIV) AND (R.IC_RO = NEW.IRO) INTO :idate, :fdate,
              :ctotal, :rtotal;
              id = :idate;
              fd = :fdate;
              IF (:ctotal IS NULL) THEN
              ctotal = 0.00;
              IF (:rtotal IS NULL) THEN
              rtotal = 0.00;
              SELECT D.IRO_AUTO_DATE FROM DIVISION D WHERE (D.IC_DIVISION =
              NEW.IDIV) INTO :adate;
              IF (:adate = "T") THEN BEGIN
              IF (NEW.IDATE < :idate) THEN
              id = NEW.IDATE;
              IF (NEW.IDATE > :fdate) THEN
              fd = NEW.IDATE;
              UPDATE RO R SET R.IDATE = :id, R.FDATE = :fd, R.CTOTAL_MECH = :ctotal
              + NEW.CTOTAL, R.RTOTAL_MECH = :rtotal + NEW.RTOTAL WHERE (R.IDIV =
              NEW.IDIV) AND (R.IC_RO = NEW.IRO);
              END
              ELSE
              UPDATE RO R SET R.CTOTAL_MECH = :ctotal + NEW.CTOTAL, R.RTOTAL_MECH =
              :rtotal + NEW.RTOTAL WHERE (R.IDIV = NEW.IDIV) AND (R.IC_RO = NEW.IRO);
              INSERT INTO VARS (EVENT,SI1,SI2,SI3,SI4,RF1,RF2,RF3,RF4) VALUES ("END
              ROMECH_AI",NEW.IDIV,NEW.IRO,NEW.IROVMRS,NEW.IMECHANIC,NEW.CTOTAL,NEW.RTOTAL,:ctotal,:rtotal);
              END~

              as you can see, i insert into VARS at the beginning and end of the
              trigger. this is working flawlessly. the only thing that i can
              ascertain at this point is that sometimes the "UPDATE RO R..." is just
              not adding the, for instance, ":ctotal + NEW.CTOTAL", or the update
              line is just not running. because, simply, it is just not adding.
              the values are there to be added and they are NOT NULL but both are a
              valid zero or number. the WHERE statement of the "UPDATE RO R..." is
              also fine and valid because it is working at least 75% of the time
              with other detail records for the same master record. the RO table is
              the master and the ROMECH is the detail.

              so any suggestions why the "UPDATE RO R..." line does not seem to be
              properly adding the two values or is not updating the master RO table
              properly some 5 to 25% of the time? especially when the same code is
              properly executing most of the time? perhaps my approach or syntax is
              off. always willing to learn new stuff. thanx for the help and talk
              to you soon.

              lucas
            • Helen Borrie
              ... Unpredictable logic seems to be the source of your problems here. You are assuming that the variables idate and fdate will be non-null. However, if the
              Message 6 of 10 , Aug 11, 2007
              • 0 Attachment
                At 11:01 PM 11/08/2007, you wrote:
                >hello one and all,
                >
                >ok, so i added some code to the troublsome triggers to log entries in
                >a separate generic VARS table that i created to track the firing of
                >the suspect triggers. it took a bit of time to collect the data.
                >here is the code of one of the suspect triggers:
                >
                >CREATE TRIGGER ROMECH_AI FOR ROMECH AFTER INSERT
                >AS
                > DECLARE VARIABLE adate CHAR(1);
                > DECLARE VARIABLE idate DATE;
                > DECLARE VARIABLE fdate DATE;
                > DECLARE VARIABLE ctotal NUMERIC(15,3);
                > DECLARE VARIABLE rtotal NUMERIC(15,3);
                > DECLARE VARIABLE id DATE;
                > DECLARE VARIABLE fd DATE;
                >BEGIN
                >INSERT INTO VARS (EVENT,SI1,SI2,SI3,SI4,RF1,RF2) VALUES ("BEGIN
                >ROMECH_AI",NEW.IDIV,NEW.IRO,NEW.IROVMRS,NEW.IMECHANIC,NEW.CTOTAL,NEW.RTOTAL);
                >SELECT R.IDATE, R.FDATE, R.CTOTAL_MECH, R.RTOTAL_MECH FROM RO R WHERE
                >(R.IDIV = NEW.IDIV) AND (R.IC_RO = NEW.IRO) INTO :idate, :fdate,
                >:ctotal, :rtotal;
                >id = :idate;
                >fd = :fdate;
                >IF (:ctotal IS NULL) THEN
                > ctotal = 0.00;
                >IF (:rtotal IS NULL) THEN
                > rtotal = 0.00;
                >SELECT D.IRO_AUTO_DATE FROM DIVISION D WHERE (D.IC_DIVISION =
                >NEW.IDIV) INTO :adate;
                >IF (:adate = "T") THEN BEGIN
                > IF (NEW.IDATE < :idate) THEN
                > id = NEW.IDATE;
                > IF (NEW.IDATE > :fdate) THEN
                > fd = NEW.IDATE;
                > UPDATE RO R SET R.IDATE = :id, R.FDATE = :fd, R.CTOTAL_MECH = :ctotal
                >+ NEW.CTOTAL, R.RTOTAL_MECH = :rtotal + NEW.RTOTAL WHERE (R.IDIV =
                >NEW.IDIV) AND (R.IC_RO = NEW.IRO);
                > END
                >ELSE
                > UPDATE RO R SET R.CTOTAL_MECH = :ctotal + NEW.CTOTAL, R.RTOTAL_MECH =
                >:rtotal + NEW.RTOTAL WHERE (R.IDIV = NEW.IDIV) AND (R.IC_RO = NEW.IRO);
                >INSERT INTO VARS (EVENT,SI1,SI2,SI3,SI4,RF1,RF2,RF3,RF4) VALUES ("END
                >ROMECH_AI",NEW.IDIV,NEW.IRO,NEW.IROVMRS,NEW.IMECHANIC,NEW.CTOTAL,NEW.RTOTAL,:ctotal,:rtotal);
                >END~
                >
                >as you can see, i insert into VARS at the beginning and end of the
                >trigger. this is working flawlessly. the only thing that i can
                >ascertain at this point is that sometimes the "UPDATE RO R..." is just
                >not adding the, for instance, ":ctotal + NEW.CTOTAL", or the update
                >line is just not running. because, simply, it is just not adding.
                >the values are there to be added and they are NOT NULL but both are a
                >valid zero or number. the WHERE statement of the "UPDATE RO R..." is
                >also fine and valid because it is working at least 75% of the time
                >with other detail records for the same master record. the RO table is
                >the master and the ROMECH is the detail.
                >
                >so any suggestions why the "UPDATE RO R..." line does not seem to be
                >properly adding the two values or is not updating the master RO table
                >properly some 5 to 25% of the time? especially when the same code is
                >properly executing most of the time? perhaps my approach or syntax is
                >off. always willing to learn new stuff. thanx for the help and talk
                >to you soon.

                Unpredictable logic seems to be the source of your problems
                here. You are assuming that the variables idate and fdate will be
                non-null. However, if the SELECT statement does not return any
                result (because there is no match), these variables will be null and
                your "IF" tests will not return the results you expect. You might
                like to visit the Documentation Index at the main Firebird website
                and download the Null Guide to understand why. Or, if you already
                understand the logic of nulls, add some code to cover the null cases.

                I suggest also that you use the proper syntax for quoting
                strings: single-quotes. Double quotes have a different syntactic
                function in SQL.

                ./heLen
              • sjlukacs
                ok, i know that this one is hanging out there a while, but i have had a hard time reproducing this bug. at this point, i am not sure if it is my delphi 7.1
                Message 7 of 10 , Oct 18, 2007
                • 0 Attachment
                  ok, i know that this one is hanging out there a while, but i have had
                  a hard time reproducing this bug. at this point, i am not sure if it
                  is my delphi 7.1 with interbase express 7.08 application or firebird
                  itself. so perhaps i can explain the problem as best as i can and we
                  can explore the possibilities together.

                  as stated in a previous post, a detail table is updating a value in
                  the master table. the field happens to be an accrual or summation
                  floating-point field. if i insert a new record in the detail table,
                  its triggers will automatically update the accrual value in the field
                  of the master table. using the VARS table, i have been able to find
                  out that when the detail table has a new insert, it is posted, and
                  then hard-committed, then say an unrelated field is changed and posted
                  in the master table, everything works fine. the times that the
                  accrual field is not properly updated is when the hard-commit is not
                  performed before changing the unrelated field in the master table. if
                  i simply post the insert in the detail table, then immediately change
                  the unrelated field in the master, then the accrual field is not
                  updated appropriately. upon inspection of the VARS table, which
                  receives messages from the triggers, it is apparent that the triggers
                  are firing just fine. however, the when the hard-commit is not
                  performed the NEW value in the master table reverts to the previous
                  instead of the current value. i hope i didn't explain that in a
                  confusing way. just guessing, but perhaps in the detail trigger
                  reading the current value from the master table using a SELECT is
                  reading only the committed values instead of the value in the current
                  transaction. it is only by doing the hard-commit prior to changing
                  the unrelated field in the master will the proper value be read
                  because the hard-commit causes it to read the value off the hard-drive.

                  ok, that said, i hope that made sense and can anyone offer any
                  suggestions. perhaps i need to change some settings in the trigger
                  configuration.

                  thank you in advance.

                  lucas
                • Helen Borrie
                  ... The trigger (on the detail record) fires once when THAT record is inserted and updates the master at that point (creates a new record version for master).
                  Message 8 of 10 , Oct 18, 2007
                  • 0 Attachment
                    At 05:34 AM 19/10/2007, you wrote:
                    > delphi 7.1 with interbase express 7.08 application or firebird
                    >itself.
                    >a detail table is updating a value in
                    >the master table. the field happens to be an accrual or summation
                    >floating-point field. if i insert a new record in the detail table,
                    >its triggers will automatically update the accrual value in the field
                    >of the master table. using the VARS table, i have been able to find
                    >out that when the detail table has a new insert, it is posted, and
                    >then hard-committed, then say an unrelated field is changed and posted
                    >in the master table, everything works fine.


                    >the times that the
                    >accrual field is not properly updated is when the hard-commit is not
                    >performed before changing the unrelated field in the master table. if
                    >i simply post the insert in the detail table, then immediately change
                    >the unrelated field in the master, then the accrual field is not
                    >updated appropriately.

                    >however, the when the hard-commit is not
                    >performed the NEW value in the master table reverts to the previous
                    >instead of the current value.
                    The trigger (on the detail record) fires once
                    when THAT record is inserted and updates the
                    master at that point (creates a new record
                    version for master). Previous record version is
                    "parked", no longer accessible by your transaction.

                    Meanwhile, you make another update to the master
                    "to an unrelated field", as you put it. In your
                    Delphi application, you are looking at a copy of
                    the extinct record version (the one that has
                    already been replaced by the detail's trigger,
                    i.e. contains the old summary value).

                    In Fb/IB, there is no field-level updating: as
                    soon as you call Post from your Delphi app, your
                    Delphi component sends a statement that updates
                    all the fields using the current values in the
                    Delphi dataset's buffer - including this old
                    summary field value. Another new record version
                    is created on disk and the recversion created by
                    the detail's trigger gets moved out. That's how
                    the value written by the detail's trigger gets blitzed.

                    >upon inspection of the VARS table, which
                    >receives messages from the triggers, it is apparent that the triggers
                    >are firing just fine.

                    > just guessing, but perhaps in the detail trigger
                    >reading the current value from the master table using a SELECT is
                    >reading only the committed values instead of the value in the current
                    >transaction. it is only by doing the hard-commit prior to changing
                    >the unrelated field in the master will the proper value be read
                    >because the hard-commit causes it to read the value off the hard-drive.

                    Don't be confused about "commit". Commit
                    (whether "soft" or "hard") causes your record
                    versions (till now seen only by your transaction)
                    to be made permanent and thus visible to other
                    transactions that start after that commit (or, in
                    the case of other transactions that are in Read
                    Committed isolation, visible next time those transactions touch that record).

                    "Soft" commit is CommitRetaining, that is widely
                    used in Delphi (and usually in the wrong way, causing problems).

                    "Hard" commit is a clean COMMIT call.

                    Commit is a transaction-level operation.

                    The Post method of Delphi writes the values of
                    the current row buffer to an entire new record
                    version on disk (as described earlier) EVERY TIME IT IS CALLED.

                    >ok, that said, i hope that made sense and can anyone offer any
                    >suggestions. perhaps i need to change some settings in the trigger
                    >configuration.

                    If your application workflow has to allow users
                    to update the master record after operations have
                    been posted from the detail records then take
                    that update right out of the detail record's insert trigger.

                    Review your design. When you build in hard
                    dependencies like this, overlapping transactions
                    can walk over one another's changes if
                    transaction management is either not clearly
                    understood by the programmer or not well
                    supported by the data access layer. What's more,
                    you can't prevent someone using some utility to
                    totally corrupt the value that *you* intended to
                    be maintained only by summing the details.

                    If you absolutely MUST accrue the detail values
                    into the master as hard data (not recommended!)
                    then do it with an After Update trigger on the
                    master. Don't take that as a solution to
                    the problem of database consistency: it merely
                    addresses the problem you are complaining
                    about. Don't let that summary field be updatable
                    from any client application - make it read-only
                    and write custom UpdateSQL to prevent the
                    application trying to update it. Just be aware
                    that, if your system depends on this summary
                    total anywhere, then you have a design flaw that
                    will not pass any external software quality assurance consistency tests.

                    Did you know you can define a COMPUTED BY field
                    on your master table using a correlated subquery
                    on the related detail records? If you have to
                    have this field at all, at least it will be
                    consistent on the database side. (Firebird
                    throws an exception if a request attempts to
                    update a COMPUTED BY field.) It won't solve the
                    consistency problems inherent on the client side
                    where the application workflow is not responsive
                    to its own changes....understand well that IBX
                    does not provide any mechanism to refresh a
                    master record in the buffer when your detail
                    record's triggers modify the real record at the
                    server. You have to take care of that yourself by
                    refreshing, i.e., closing the master dataset and re-opening it.

                    This isn't a Delphi forum. If you need to
                    understand more about how IBX works, various
                    Borland Team B members have published how-tos
                    around the web. You could try googling "Marco Cantù" as a start....

                    ^ heLen
                  • sjlukacs
                    ... i like the latter suggestion. can you pass me along a sample ALTER TABLE with an ADD ... COMPUTED BY ... so that i can see the syntax. i tried a few
                    Message 9 of 10 , Oct 26, 2007
                    • 0 Attachment
                      >
                      > Did you know you can define a COMPUTED BY field
                      > on your master table using a correlated subquery
                      > on the related detail records? If you have to
                      > have this field at all, at least it will be
                      > consistent on the database side. (Firebird
                      > throws an exception if a request attempts to
                      > update a COMPUTED BY field.) It won't solve the
                      > consistency problems inherent on the client side
                      > where the application workflow is not responsive
                      > to its own changes....understand well that IBX
                      > does not provide any mechanism to refresh a
                      > master record in the buffer when your detail
                      > record's triggers modify the real record at the
                      > server. You have to take care of that yourself by
                      > refreshing, i.e., closing the master dataset and re-opening it.
                      >
                      i like the latter suggestion. can you pass me along a sample ALTER
                      TABLE with an ADD ... COMPUTED BY ... so that i can see the syntax. i
                      tried a few variations but no luck.

                      thanx in advance helen and have a great weekend.

                      lucas
                    • Helen Borrie
                      ... alter table master add summarytotal computed by ( (select sum(linetotal) from child where child.masterid = master.masterid) ) ./heLen
                      Message 10 of 10 , Oct 26, 2007
                      • 0 Attachment
                        At 06:10 AM 27/10/2007, you wrote:
                        >can you pass me along a sample ALTER
                        >TABLE with an ADD ... COMPUTED BY ... so that i can see the syntax. i
                        >tried a few variations but no luck.

                        alter table master
                        add summarytotal computed by
                        (
                        (select sum(linetotal) from child
                        where child.masterid = master.masterid)
                        )

                        ./heLen
                      Your message has been successfully submitted and would be delivered to recipients shortly.