Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We are in need of some better auditing on some of our MySQL tables. Specifically, we'd like to store when the row was inserted and when it was last updated.

Originally, I thought DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP would be perfect, but unfortunately our current MySQL is below version 5.65, so it only supports doing this on one column and thus doesn't meet our needs.

Thus, my next thought was that triggers would be a good solution. However, when I ran this by our DBA, he said that triggers are normally a bad idea and that I should just update the values in the code every place there is an update or insert. This works, but I don't like the fact that it leaves an extra thing for everyone who creates/updates records to worry about, and if they forget then the values will be incorrect. Thus, to me this seems like exactly the case where triggers should be used.

Is this a bad use of a trigger and why?

share|improve this question
    
A trigger is not necessarily a bad thing. For a system that is not expected to see huge growth in the number of concurrent users, a trigger would serve the purpose well. Triggers generally do not scale well, and this may be the reason your DBA is saying no. –  Max Vernon Nov 12 '13 at 17:24

1 Answer 1

As far as I know, this is the reason Triggers were made. To handle an event related to a change running in the DB. I don't see any reason why you shouldn't be using a Trigger. P.S. please ask your DBA of why he doesn't like Triggers, and report back. Thank you.

share|improve this answer
    
Please note that triggers in general may have made for similar scenarios but in MySQL specifically, triggers are executed row by row (there are no statement level triggers like in SQL-Server and Oracle) so performance is not the best when there are many rows (inserted/updated/deleted.) –  ypercube Nov 12 '13 at 17:36
    
If I understand the question correctly, he needs to update each row. besides IIRC, the FOR EACH ROW isn't neceasary, he could update all the rows that matches the delete –  user2558461 Nov 12 '13 at 17:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.