DML Triggers are stored procedures which are automatically executed when a DML operation is performed on a table/view defined in a trigger. A good use of trigger is to audit table modifications. A trigger can be written to keep history of updates made to a table to an audit table which can answer questions like “Who modified the table?” or “What was value before update”.
If not used appropriately, triggers can have certain disadvantages as well.
Once I was asked to troubleshoot an insert statement which terminated with below error
The above error is usually returned when a sub query in a select statement returns more than 1 value where in the outer query expects only a single value to be returned. This was very early in my career and I seriously didn’t have a clue how to approach the problem.
I approached it by trying to insert a single row instead of a multiple rows. It worked. Then I tried inserting rows using SELECT INTO and as expected it terminated with error. I opened up profiler and executed the query to check what’s happening in background. The profiler trace returned only the insert query that I ran. I finally dropped and created the table. I was working on development server so I had this liberty. The insert statement succeeded. However, this wasn’t a workaround as I can’t drop and create a table on production; however this did give me a hint that the error might be because of some table dependent object. I then found dependent objects and located a trigger which caused this error.
So the disadvantages I see in having triggers are
– Difficult to locate unless proper documentation.
– they can’t be traced and are invisible to applications
– Triggers are fired whenever a modification is made to a table; they thus slow down the performance of DML statements.
– It’s difficult to track triggers logic.
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
