Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2014
    Posts
    2

    Unanswered: Trigger fires when not necessary

    Hello,

    I have a problem with a trigger.
    I have 2 tables - Salaries and CrimesLog. When a user tries to update the bonus field in the Salary table with a value greater than 0.1*the salary in the row, then the illegal value is stored in the column ILLEGAL_VALUE in the same table (the initial value of the ILLEGAL_VALUE column is NULL) and the bonus is limited to 0.1* the salary in the row. After the ILLEGAL_VALUE column is updated, an after trigger fires and its action is to insert data in the CrimesLog table.
    The problem is that when a user updates the bonus field with a legal value, the after trigger fires too and a new record is inserted in the CrimesLog table. I've been wondering for a few weeks what the problem might be but I couldn't find a solution.
    The sql for the tables and the trigger follows:
    Code:
    CREATE TABLE SALARIES
    (EMPID CHAR(5),
    SALARY INTEGER,
    BONUS SMALLINT,
    ILLEGAL_BONUS SMALLINT);
     
    INSERT INTO SALARIES VALUES ('mn-20', 25000, 50, NULL);
    INSERT INTO SALARIES VALUES ('mn-28', 26300, 35, NULL);
     
    CREATE TABLE CRIMESLOG
    (TIME_STAMP TIMESTAMP,
    USER_ID VARCHAR(128),
    DESCRIPTION_OF_CRIME VARCHAR(128),
    AMOUNT INTEGER);
     
    CREATE TRIGGER DANI.BONUS_BEFORE NO CASCADE
    BEFORE  UPDATE OF BONUS ON DANI.SALARIES 
    REFERENCING  OLD AS OLD  NEW AS NEW 
    FOR EACH ROW 
    MODE DB2SQL
    WHEN ( NEW.BONUS > 0.1 * OLD.SALARY )
    BEGIN ATOMIC
    SET NEW.ILLEGAL_BONUS = NEW.BONUS;
    SET NEW.BONUS = 0.1 * OLD.SALARY;
    END
     
    CREATE TRIGGER DANI.BONUS_AFTER
    AFTER  UPDATE OF ILLEGAL_BONUS ON DANI.SALARIES 
    REFERENCING  OLD AS OLD  NEW AS NEW 
    FOR EACH ROW 
    MODE DB2SQL
    WHEN ( NEW.ILLEGAL_BONUS IS NOT NULL)
    BEGIN ATOMIC
    INSERT INTO CRIMESLOG (TIME_STAMP, USER_ID, DESCRIPTION_OF_CRIME, AMOUNT)
    VALUES (CURRENT TIMESTAMP, USER,
    'An attempt to set the bonus above the set level.', NEW.ILLEGAL_BONUS);
    END
    Maybe the problem with the trigger is that when a user tries to update the bonus field with a legal value for the same employee the after trigger is fired because the illegal_bonus field contains the old illegal value.
    Can somebody tell me what the problem with my trigger is and how to solve it?
    Thank you in advance.
    Last edited by yordanka.toteva; 01-25-14 at 13:11. Reason: Mistake in the topic heading

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Replace both triggers with a check constraint.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    In the BEFORE trigger, remove the WHEN and put an IF ... ELSE:
    IF illegal bonus THEN SET NEW.ILLEGAL_BONUS = NEW.BONUS
    ELSE SET NEW.ILLEGAL_BONUS = NULL
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  4. #4
    Join Date
    Jan 2014
    Posts
    2

    The triggers still don't work correctly

    The two triggers still don't work as expected.
    I don't understand why the after trigger activates when the before trigger doesn't execute - when a user updates the bonus field with a value that is less than the one in the when condition. The after trigger has to fire only when the illegal_bonus field is updated and this doesn't happen when we update the bonus field with a legal value. Can somebody tell what the problem is?

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    do you have some process that is doing an update to the illegal column? What is the actual update statement being used? What about the insert, is it possible they did not insert a null?

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... What is the actual update statement being used? What about the insert, ...
    This was the what I want to know.
    What statement(s) fired the triggar?
    And, how worked the triggars?
    (What were the data before the update/insert statement(s),
    What should be the data after the update/insert statement(s)?
    )

    You showed the DDLs and triggars. It is good to share the issue with you and other persons.
    But, if considering more,
    it might be better to show sanple data and the required result after update/insert from the sample data.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •