Tuesday, January 31, 2006

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

Now the next trigger, it will be an AFTER INSERT trigger, so to fire after the previous one has inserted a row, obviously an after insert trigger can reference old and new data but can't modify them.
So, on with the code:

-- DROP TRIGGER new_salary $$

CREATE TRIGGER new_salary AFTER INSERT ON salary_history
UPDATE salary_history SET new_salary = (new.old_salary + new.old_salary * new.percent_change / 100) WHERE emp_no = new.emp_no;
END $$

The other trigger "POST_NEW_ORDER" posts an event named 'new_order', events are used by Firebird (and Interbase) server to notify clients about changes in database states (more on events in this whitepaper) as this feature is not present in MySQL and it's closely related to the application layer I won't even try to reproduce it.
So, right now I've reproduced table structures (whenever possible), views, triggers (whenever possible), in the next part I'll add foreign keys to the tables and start looking at stored procedures.

No comments: