How would one implement this in MySQL:
CREATE TABLE employee ( employeemonthly DECIMAL(10,2), employeeyearly DECIMAL(10,2) DEFAULT employeemonthly*12 ); 
Add a comment |
3 Answers
use a insert trigger for that. Something like this
DELIMITER | CREATE TRIGGER default_yearly BEFORE INSERT ON employee FOR EACH ROW BEGIN SET NEW.employeeyearly = NEW.employeemonthly * 12; END; | DELIMITER ; 2 Comments
Nag Hammadi
Thanks for the example. Trying it now
juergen d
@NagHammadi: I updated my answer another time. I verified it and it works.
I would use a view:
CREATE VIEW vemployees AS SELECT e.employeemonthly, e.employeemonthly * 12 AS employeeyearly FROM EMPLOYEE e ...because there's little need to dedicate storage space for an easily calculated value. Otherwise, use a function or simply write the expression into whatever query/stored procedure you need.
What really depends is:
- How often you need to access this data
- How complex the operation is to get the result you need
I recommend starting with not storing the value. If performance gets to be a problem, then dedicate a column for the values storage -- not before. At that, a trigger is a bit overkill to me, when you can use (psuedocode):
INSERT INTO employee (...employeemonthly, employeeyearly, ...) VALUES (...@employeemonthly, @employeemonthly * 12, ... 2 Comments
Nag Hammadi
If I "dedicate a column for the values storage", do use juergen d's method?
OMG Ponies
@NagHammadi: Yes, a trigger is a good way to make sure the logic is applied to all interacting with the table.
Use a trigger for the insert event, access the new record data using NEW and set the appropiate values.
