Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2010
    Posts
    3

    Unanswered: Need help with trigger

    I am a newbie with db2 and would appreciate some help to get my trigger to work.

    Code written as follows:
    Code:
    CREATE TRIGGER update_sal
    	BEFORE INSERT ON Work
    	FOR EACH ROW
    	BEGIN
    	UPDATE Staff SET salary = salary * 1.02 WHERE Work.sno = Staff.sno;
    END;
    Level DB2 v9.7.200.358

    Thank you in advance

  2. #2
    Join Date
    Apr 2009
    Posts
    42
    check db2 manual, you miss a "referencing" option in you command.

  3. #3
    Join Date
    Dec 2010
    Posts
    3
    Quote Originally Posted by wilsonfv View Post
    check db2 manual, you miss a "referencing" option in you command.
    Thank you. So I added the referencing but still can't seem to get it to work.

    Right now my code looks as following:

    Code:
    CREATE TRIGGER update_sal
    	BEFORE INSERT ON Work
    	REFERENCING NEW ROW AS new OLD ROW AS old
    	FOR EACH ROW
    	BEGIN
    	UPDATE Staff SET new.salary = old.salary * 1.02 WHERE new.Work.sno = old.Staff.sno;
    	END;

  4. #4
    Join Date
    Apr 2009
    Posts
    42
    what do you mean by it doesnt work. any error occurred?
    besides, I believe your sql wont even execute successfully. cause you are building a trigger on the table "Work", but in the trigger you update another table!

  5. #5
    Join Date
    Dec 2010
    Posts
    3
    Yes, the error occurs at the end, unexpected end-of-statement.

    This is a school assignment which I can't seem to solve, I am supposed to update the other table.

    Any suggestions on how to get around this problem? I have to solve it by trigger.

    Edit: sno is staff number, when a staff member gets another "work" (project), his salary will be increased by 2%. Salary is saved in Staff table.
    Last edited by malle86; 12-10-10 at 06:50.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Make it an AFTER trigger, not before. And get rid of the BEGIN and END lines.

    Andy

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    also, you need to change delimeter to something else for the END

    UPDATE Staff SET new.salary = old.salary * 1.02 WHERE new.Work.sno = old.Staff.sno;
    END~
    Dave

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by dav1mo View Post
    also, you need to change delimeter to something else for the END


    Dave
    If you remove the BEGIN and END lines you do not have to do this. There is only one statement in the BEGIN-END so it is unnecessary.

    Andy

Posting Permissions

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