One of my team member asked me should I use triggers or stored procedure. Both of them has its usage and needs. I just basically told him few issues with triggers. This is small note about our discussion.
Disadvantages(Problems) of Triggers
- It is easy to view table relationships , constraints, indexes, stored procedure in database but triggers are difficult to view.
- Triggers execute invisible to client-application application. They are not visible or can be traced in debugging code.
- It is hard to follow their logic as it they can be fired before or after the database insert/update happens.
- It is easy to forget about triggers and if there is no documentation it will be difficult to figure out for new developers for their existence.
- Triggers run every time when the database fields are updated and it is overhead on system. It makes system run slower.
I do not use triggers. In my whole career I am able to get my work done using Stored Procedures instead of Triggers. (Implementation and Architecture changes are required if either of is to be used to support business logic).
Reference : Pinal Dave (https://blog.sqlauthority.com)
56 Comments. Leave new
i wanna know about DATA MASSAGING…Can anyone xplain this to me?
I was asked in an interview as to how you will perform auditing without use of trigger.
We used trigger to save the old data with timestamp in another table called audit
Is there a way to not use trigger and still have audit data?
These was my idea avoiding trigger/stored procedure until now I am developing a cloud-base web application where network traffic is one key issues. I have to revisit dealing some business requirement/constraint/rule that can be applied and executed on server side.
New way to handle trigger/stored procedure effectively and maintainable is implementing unit test while modeling database. The DB Unit Test is the same concept and practice as JUnit which implement Test Scenario, Test Case, Expectation, Result and Assertion. The difference is using SQL instead of Java.
The how-to is while modeling data , you must build sample data to test your logic. Some requirement / constraint/ rule can be made by trigger/strored procedure at this stage. A best practice in dealing with creating table, trigger, stored procedure is not to use graphical modeling,but simply write SQL file containing DDL/DML script with full description and comment.
You must create script of sample data to create sample data for both programmer and you as a DBA to make DB Unit Test. We then write DB Unit Test script to test against these sample data. After the test we will have test log to represent every test case have been test and passed. This help ensure table structure , trigger, stored procedure have been made right and work properly. This practice could help in maintenance when come new more requirement or change.
Thank you….
Hi Dave,
We want to save history (audit trail) for some of our tables (on update). The structure of the table in which we want to save the history data is –
Pk, TableName, ColumnName, OldValue, NewValue, TablePk, ModifiedBy, ModifiedOn
We thought of using triggers to save the history in this table. Can you please suggest what is the best approch to save history data in this table without using trigger.
Also, in some of the tables we are doing mass update, Audit trailing is required for mass update too. User may upload a file to update multiple records in one go. The process we followd for bulk update is –
1. Read the data from the file.
2. Bulk copy to a temporary table.
3. Update the original table from the tempory table (using a single stored proc).
4. Flush the temp table.
How can we do the audit trailing in this situation?
Hi Pinal,
We can implement after update trigger logic in stored procedure using output clause.
am i correct…?
Hi.. I started working with with Sql Server now (worked 25 years only with Oracle).
Triggers always were a “silver bullet” for my systems. A rich resource that solved a lot of problems.
I saw your arguments about triggers and I realized that most of they are related to the developer, not to the resource.
1) Triggers are dificult to view: This is a lack of knowlege about catalog.
2) They cannot be traced: yes, they can. Trigger is a programming resource. With a little of creativity you can trace it
3) It is hard to follow the logic: I agree that its logic is not for anyone, but a good developer (which knows about triggers) will get it so fast
4) Is easy to forget if there is no documentatio: This is a problem of documentation, not of triggers. Anyway, database has catalog, you can get all documentation you need from catalog (if you know how to use the wide documentation resorces of RDBMS)
5) Trigger are slow: Depends. Are you comparing a DML on one table with trigger versus one table without trigger? You must compare business rule execution not DML execution. Doing that you will realize that they are not slow.
One thing is true: Trigger is not for amateurs. Is a rich resource that requires a wide vision about database and system
For any programming resource, when bad used becomes a bad resource. The problem is not on the resource but on the user