What are Database Triggers ?
A Database trigger is an SQL code which is made to run just before or after a certain event. That event could be an INSERT, UPDATE or a DELETE query on a particular database table.
Thus a trigger is used to automate some of the events on your server/site/application.
Examples of some Database triggers
- Sync user details from one table to another when a user updates them
- Geocode users location and store them in a separate table
- Maintaing log of certain events e.g. a product addition, updation or deletion (In this case we wish to know who did the change)
When to use Database triggers
There are few pros and cons about using database triggers.
- Yes they can automate quite a lot of activities
- For things like maintaining logs if you are doing this through your code then most probably you need to add the piece of code in a number of files. e.g. If you are maintaing a log about article updates then there may be a number of files in your code related to updating articles. You will need to add the log related code in every file. But if this is done through trigger then you do not need to worry about updating your code.
- In the above example if the log table structure changes then you just need to update your trigger code and you are done.
- Triggers are very handy in case you have completely different systems on different platforms and coding is not really possible. This is mostly the case in large organisations.
- Since the trigger is not part of your normal code it may create lot of confusion later as to how certain things are happening especially if you are new to the system and things are not properly documented. Due to this lot of people prefer to write event driven procudures in their code instead of creating triggers as that way they are in full control of the application.
- If the business logic changes then the triggers can be difficult to handle/update. At that point of time it may happen that a certain event may not be possible through a trigger and you may need to revert back to your usual way to handling events through your code. This may further increase development time.
Based on the above pros and cons we can understand that using a trigger or not is not really a straightforward decision. It really depends on case by case basis.
- If you are coding in an object oriented manner then most probably you are writing all your events in structured manner and there is no duplication of code. In that case it becomes quite easy to add the event based procedures within your code.
- However in some cases the code is not object oriented as it may be quite an old application. In that case you can make a judgement call whether its the time to update the code or create triggers.
- In case 2 if the code is done by some other developer then it is likely that you would use triggers.
- For applications built on different platforms or different scripting languages sharing common resources triggers can be handy to achieve some form of integration.