Need help with trigger

malle86 · #1 (**permalink**) 12-10-10, 02:32

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

wilsonfv · #2 (**permalink**) 12-10-10, 02:49

check db2 manual, you miss a "referencing" option in you command.

malle86 · #3 (**permalink**) 12-10-10, 04:45

Quote:

Originally Posted by wilsonfv

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;

wilsonfv · #4 (**permalink**) 12-10-10, 05:27

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!

malle86 · #5 (**permalink**) 12-10-10, 05:46

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.