Monday, January 30, 2006

Porting the EMPLOYEE database from Firebird 2.0 to MySQL 5.1 part 3

Ok, on with triggers, I'll start migrating the existing triggers then I'll look at triggers as a workaround for computed columns.
Firebird syntax for SAVE_SALARY_CHANGE trigger (active AFTER UPDATE) on table EMPLOYEE is:

...
BEGIN IF (old.salary <> new.salary) THEN
INSERT INTO salary_history
(emp_no, change_date, updater_id, old_salary, percent_change)
VALUES (
old.emp_no,
'NOW',
user,
old.salary,
(new.salary - old.salary) * 100 / old.salary);
END


Now in MySQL terms it becomes:

CREATE TRIGGER save_salary_change AFTER UPDATE ON employee
FOR EACH ROW BEGIN
IF (old.salary <> new.salary) THEN

INSERT INTO salary_history
(emp_no, change_date, updater_id, old_salary, percent_change)
VALUES ( old.emp_no,
now(),
user(),
old.salary,
(new.salary - old.salary) * 100 / old.salary);
END IF;
END $$


As you can see syntax is quite similar, the only differencies are highlighted in red.
Nice, isn't it?
In this specific case I see that the calculated column of table sales_history recomputes the new salary using the old one and the percent change just inserted by the procedure above, so, instead of implementing another trigger I could just modify this to insert the correct values, but I'll build the second trigger just to keep the cascading trigger sequence.

Now, not to be too picky on MySQL QueryBrowser, but it's lacking the appropriate "create trigger" option, so you'll have to create what follows by hand and it's also missing (al least in version 1.1.19) the appropriate infrastructure to graphically show triggers.

No comments: