SQL SERVER – Disadvantages (Problems) of Triggers

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 (http://blog.SQLAuthority.com)

56 thoughts on “SQL SERVER – Disadvantages (Problems) of Triggers

  1. Hello,

    I agree with your list… I find Triggers very annoying in a Database. You have no clear view of whats going on under the hood. But i think the last point of triggers is the only supporting argument, and not one against Triggers.

    If you need to ensure that something happens EVERYTIME a field is updated, then you should use a trigger! Thats there only “purpose in live”. So you either need to close down your DB so noone can access the tabels directly, or you need to implement a trigger. Thats the only way you can verify that a certain rule will be obeyed. The performance argument is still valid. You need to make sure that your trigger only fiers when the field is actually updated (Hint: update foo set bar = 1 where bar = 1 is an update on foo.bar… so compare the values).

    Like

  2. Hi,

    One of my tables has UPDATE trigger with thousands lines of statements and using “IF UPDATE(column)” to bypass unnecessary running of the Trigger for certain field updates. What I experience is that even though the trigger bypasses the lines when it encounters updates to those specified fields, the response is taking long which I presume is caused by trigger compilation. If I disable the trigger, the response is fast.

    Can you tell me is it a good practice for a trigger to contain tousands of codes. Or what do I do in order to make updates on the specified columns to run fast?

    Thanks in advance,
    Tet

    Like

  3. Hi,

    I have created one After Update Trigger in Sql server 2005.
    But My Problem is When I am Going to Update more then one column at a time Trigger given me error message while this trigger is working fine for one record updation

    Trigger is Follow.

    I have create trigger for when I update Test5 table at the same time entry will be done in Test1 table.

    Create trigger [trTest1Update] on [Test5]
    After Update
    as

    DECLARE @OldValue VARCHAR(50)
    DECLARE @NewValue VARCHAR(50)

    IF NOT UPDATE(Old_Value) AND NOT UPDATE(New_Value)
    BEGIN
    RETURN
    END

    SELECT @OldValue = (SELECT Old_Value + ‘ ‘ + New_Value FROM deleted)
    SELECT @NewValue = (SELECT Old_Value + ‘ ‘ + New_Value FROM inserted)

    insert into Test1(Old_Value,New_Value)
    values(@OldValue,@NewValue)

    Can U Help Out Me.
    Its Argent..
    THanks in Advance.

    Like

  4. Triggers can now be selectively disabled giving the developer more control over how the triggers are used. Triggers can be disabled for a single user, all database users, a single trigger or for a single table. Changing the state of a trigger will take place immediately and in most cases will be persisted until explicitly changed. The exception to this is when disabling triggers for a specific user, in this case the disabled setting will only apply until the user disconnects.

    This functionality can be very useful when doing bulk INSERT, UPDATE or DELETE operations where triggers could potentially affect performance. This may also be used for the ADSSYS or other system users to avoid audit type triggers during automated data changes.

    Like

  5. Create trigger [trTest1Update] on [Test5]
    After Update
    as

    insert into Test1(Old_Value,New_Value)
    select d.Col1, i.Col1
    FROM Inserted as i
    inner join deleted as d on d.pkCol = i.pkCol
    where i.col1 d.col1

    Like

  6. Such all think is ok with Trigger but according to me the maser drow back of Trigger ( or all the data base object ) is when we swich over one data base to another data base than it need to re-create. If such all the logic is written in front hand than there is no need to write Trigger logic in other data base.

    EX:- Suppose current my project is running with ASP.net and using Oracle as Back end if i some of other client need that project in Sql Server as back end than all the DB object must be re-write in Sql server.

    Like

  7. I used triggers in one of my project to maintain the logs.. I can keep track when and who has updated/inserted the values in tables. Secondly what values are updated to what in cases of updated triggers can be easily traced.

    Like

  8. Hi people,

    I have a problem implementing two levels of a hierarchy on SQL Server 2005. In my data base, I have a table called PRODUCT and its soons (about 14), specifying the kind of product (see diagram).

    ___________
    | PRODUTO |
    |__________|
    |
    ( d )
    / \ … \
    __/_ _\__ \__
    | A | | B | | N |
    |___| |___| |__|

    I have to ensure the totality of the relationship, i.e. each product may have just one specialty associated. A product can not be A and B in the same time.

    To solve this problem I am trying to implement a trigger in SQL, because SQL Server 2005 do not implement ASSERTION statement.

    Do you kwon another solution for this problem? I have understood the problems related to trigger using, but I have another better way to solve the problem?

    Thanks,
    Leandro

    Like

  9. Hi
    Mr Pinal Dave
    I Have problem in SQL Server 2005
    I Have 3 tables for Example tblA , tblB And tblUser
    tblA have Relation with tblB and he is perent for tblB
    and tblUser have relation with 2 table tblA and tblB
    and he is parent for tblA and tblB
    when I want to create Relation SQL Server 2005 trow
    error with this comment :

    Introducing FOREIGN KEY constraint ‘FK_tblB_tblUser’ on table ‘tblB’ may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
    Could not create constraint. See previous errors.

    if i Set ‘Cascade Rule’ to ‘NoAction’ and Set ‘Enforce Foriegn Constraint’ to ‘No’ then for this reason i have 2 solve
    1- use transaction to update
    2- use Trigger to Update

    please tel me wich one is better
    tblA and tblB grow 1 million record per year

    Thank U
    plz send your idea to my mail
    my Email : Ali.Salamat2@Gmail.com

    Like

  10. @Vidit

    There are two kind of Triggers in SQL Server 2005 and Higher…
    1. DML – Data Manipulation Language – INSERT, UPDATE, DELETE
    2. DDL – Data Definition Language – CREATE, ALTER, DROP…. many more…

    In SQL Server 2000 and lower, we had only one type of trigger, DML Triggers.

    Look at these links

    For DML Triggers :

    http://www.sql-server-performance.com/articles/dev/triggers_2000_p1.aspx

    For DDL Triggers :

    http://www.sql-server-performance.com/articles/audit/ddl_triggers_p1.aspx

    Google on key word : DDL Triggers in SQL Server or DML Triggers in SQL Server, you will get plenty of useful links to visit.

    Take time to understand the logic, rather than remembering the syntax.

    ~ IM.

    Like

  11. I have create trigger for when I update tbl_personal table at the same time entry will be done in tbl_general table.

    CREATE TRIGGER trig_personals
    ON tbl_personal
    FOR UPDATE
    AS
    DECLARE @Flag VARCHAR(100)
    DECLARE @Reg numeric(9)
    IF NOT UPDATE(Flag)
    BEGIN
    Update tbl_general set Flag=’A’ where Reg=@Reg
    END

    Can u help Me.
    It’s very urgent
    Thanks in Advance

    Like

    • @Bhadri.

      Trigger is very powerful TSQL Command and should be used very carefully.

      You cant implement Parent-Child relation with foreign key constraint, if you have your parent table in one database and your child table in other database. In this case, you can use triggers to implement foreign key relation ship.

      You cant have a check constraint on a table, if you want to check a value that refers to two other tables. You can implement the same with Triggers. (You can also use a combination of Check Constraint and a function to implement the same)

      You can actually start a Job inside a Trigger. If any event occurs, and you want to set a response to that event, you can set it through a trigger.

      There are many more advantages of Triggers.

      Starting from SQL Server 2005, We also DDL Triggers, there are NO if and No Buts to not use them… they are very very helpful… just take a look or google (DDL Triggers in SQL Server 2005) on web to know more about them.

      ~ IM.

      Like

  12. In the event where a primary needs to be generated on the fly, I can’t see how can you do it without a trigger, unless you reove the primary key and run a stored rpc every time you insert anything, but it leaves us with the same scenario. I would think the greatest disadvantage is the difficulty to track or not “forget” that there is a little detail running behind the scenes…

    Like

  13. sir plz tel me

    what is the difference between after triggers and insted of triggers. and also in which situation we use triggers…plz tel me

    Like

    • Hi Subramanyam,

      These two are related to DML triggers.

      After triggers are executed after executing the DML events i.e.,the trigger adn the events both are executed.These triggers can be executed only on tables.

      Instead of triggers are executed instead of executing the DML events i.e., the trigger is executed without executing the DML event. These triggers can be executed on tables and views.

      Like

  14. Hello Subramanyam,

    As the name imply the code of AFTER trigger executes after completing the trigger firing statement while code of INSTEAD of tigger executes instead of firing statement.
    AFTER triger is used mostly for implementing business logic and auditing while INSTEAD of trigger is used on updatable views.

    Regards,
    Pinal Dave

    Like

  15. madhusudan sir,
    If MSSQL server has any disadvantges that may cause ant error in compiling microsoft surely couldont produce a new version 2008

    are U understand me sir?

    Like

  16. use of instead of and after trigger..
    after trigger u know already..
    but instead of
    u can check constraint,suppose foreign key is not their then u can use.
    this is old way.

    exp:

    alter trigger trg1 on emp1 instead of insert
    as
    declare @empid int;
    select @empid=isnumeric(empid) from inserted;
    if(@empid=1)
    print(‘numeric’);

    else
    print(‘not numeric’);

    Rajneesh Hajela Gwalior (M.P.)

    Like

  17. Pinal,

    I totally agree with you on this.
    To add to your thoughts, triggers are misused most of the times thereby affecting db performance. Also, all app logic should be part of the application process flow to keep things simple and visible like you said, which makes debugging easier as well.
    I have trouble explaining this concept so some people.

    Thanks for putting it in writing!

    Like

  18. Dave,

    Thanks for the heads up – it sounds like people are using Triggers for things which they probably ought not to… for example cascading foreign key relationships.

    I work in an environment where we do lots of bulk imports into clusters of child/parent tables, and if we had triggers going off each time we did anything we would have 1) a very slow environment, and 2) many many erroneous table links.

    On the other hand – we also rely extensively on non-clustered indexes in order to make our reporting work in a realistic amount of time.

    Is it appropriate to use a trigger to disable all table indexes before committing an UPDATE or INSERT statement, and then to rebuild the indexes AFTER the statement processes?

    Thanks

    Forrest

    Like

    • It seems that Triggers are being used extensively EXACTLY for foreign key maintenance.

      I still think there is wisdom in manually processing table links; especially if you are pruning dupes from the parent table, and don’t want to loose referential integrity with the child tables.

      Like

  19. Hello,

    How can we replace a trigger by a stored procedure?

    A trigger fires on UPDATE / INSERT or DELETE statement, how can we execute the stored procedure after an insert?

    Thank you.

    Like

  20. Hello,
    How can I we get the values of the records being updated on the Table and not get updated due to Where Condition
    CREATE TABLE [dbo].[Test](
    [id] [bigint] NULL,
    [description] [varchar](50) NULL
    )
    GO
    INSERT INTO [dbo].[Test]([id],[ description])
    Values (1,’SQL2005’)
    GO
    Update [Test] Set [description] = ‘SQL2008’ where [ID] = 3
    In above case I need to write a trigger where I can find the Value of Field Description Which is SQL2008,
    Instead of Trigger gives the values but only if the Records are updated

    Like

  21. Hi Pinal,

    Just wanted to know about logging data modification.

    I have seen to ways of tracking data change(DML).
    1. Using Triggers
    2. Keeping columns in same table for Added Date, Added By, Modified Date, Modified By.

    Using approach(1), I can write trigger for Insert/Delete/Update on each table to log changes and hence can apply Foreign key relationship and other constraints like Unique key constraints on all the tables as per requirement.

    But I didn’t understand how it is possible to apply various constrains using approach(2).
    Since I have to make composite unique key and have to consider many more columns.

    Is there any design issues in database tables. What is the suggested way for approach(2) to log data.

    Which approach is better.

    Also I have come to know from some of my collegues that Triggers do no fires on bulk insert queries is it true.

    Like

  22. Pingback: SQL SERVER – Significance of Various Kinds of Triggers- Quiz – Puzzle – 2 of 31 « SQL Server Journey with SQL Authority

  23. hi
    hard to say…

    kindly eradicate the application in the below content …

    Triggers execute invisible to client-application application. They are not visible or can be traced in debugging code.

    Dont mistake me sir
    Munirajan.T

    Like

  24. Nice article, your articles always manage to stay current throughout the test of time!

    There is one thing I use triggers for and that is when I need exact audit records of every change made to each record in a table – although I hasten to add that what I do within the trigger is get the batch of records which have been changed and call a stored procedure from that record set so as much of my code as possible is easily visible under procedures.

    Is there a better way for me to capture audit records or is a DML trigger the right thing to do here? I implemented it in my dev version of a program (as we have a lot of people here who would have access to the DB so I can’t rely on application-side auditing, sadly!) but if I should have taken another approach then I would prefer to implement it before I move to live!!

    Thank you once again for yet another great article,

    Rose

    Like

  25. your Last Statement is wrong
    “Triggers run every time when the database fields are updated and it is overhead on system. It makes system run slower.”

    ————————————————————-
    i have “emp” table that have id,name columns

    i created a trigger that is below-
    ————————————-
    alter trigger trgEmpInsert
    on emp after insert
    as begin
    declare @a int
    select @a=id from inserted
    insert into emp1(id) values(@a)

    end
    ——————————————-
    Here Emp1 is another table that have the same schema as emp

    i add a new column in emp table as below-
    ——————————————————–
    alter table emp
    add country1 varchar(33)
    —————————————-
    when i checked the emp1 table after this we does’t get any row f inserted from trigger .
    ————————————————-
    Before Answering any concept please practical it in SQL server .

    let me know if i am wrong .

    ——————————————————————-
    all Query which i am used to test the above concept
    ——————————————————————-

    CREATE TABLE emp(
    id int (10) ,
    name nvarchar(50) ,

    )

    CREATE TABLE emp1(
    id int (10) ,
    name nvarchar(50) ,

    )

    insert into emp(id,name) values(1,’ram’)
    go 100

    alter trigger abc1
    on emp after insert
    as begin
    declare @a int
    select @a=id from inserted
    insert into emp1(id) values(@a)
    end

    alter table emp
    add country1 varchar(33)

    Like

    • 1. your trigger run when a row is inserted into emp, not when you alter table structure.

      2. your trigger doesn’t support bulk insert, to support bulk insert you can use this :

      alter trigger abc1
      on emp after insert as
      begin
      insert into emp1(id)
      select id from inserted
      end

      Like

  26. Pingback: SQL SERVER – Weekly Series – Memory Lane – #030 | SQL Server Journey with SQL Authority

  27. 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?

    Like

  28. 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.

    Like

  29. 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?

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s