locked
Need help with first trigger please! RRS feed

  • Question

  • I need help creating my first trigger for a database. I have the following (example) structured table.

    Name Type Length
    TableID int  
    Name varchar 50
    Created datetime  
    CreatedBy varchar max
    Modified datetime  
    ModifiedBy varchar max
    Deleted datetime  
    DeletedBy varchar max

    I want to create three different triggers that will do the following.

    1. Insert - Upon inserting a record with just the "Name" field, the Created and CreatedBy fields will be auto-populated by the trigger for the records that were inserted with the the datetime they were inserted (slightly after for the after trigger) and who inserted them as per the SUser_SName() function.
    2. Update - Upon updating one or more records, populate the Modified and Modified By fields with the same auto-populated information as the insert (#1 above), except it would be the datetime they were updated.
    3. Deleted - Upon deleting one or more records, populate Deleted and DeletedBy fields with the same auto-populated information as the insert (#1 above), except it would stop the deletion from taking place and it would be the datetime the attempted to delete them took place.

    As I mentioned, this is my first trigger and I'm sure I could write it, but I want to be sure that I'm accomodating all possibilities and don't introduce any problems. I'm just concerned that I would cause a problem where triggers are triggered endlessly or the deletion isn't actually canceled or something else that I just wouldn't think of. Any help with this would be greatly appreciated!


    Nathon Dalton .NET Software Developer
    Monday, January 4, 2010 10:42 PM

Answers

  • 1. The Inserted and Deleted tables are per transaction, in other words, per the statement that caused the trigger to fire (DELETE statement or UPDATE statement or INSERT statement).

    2. Yes, the records in the Inserted and Deleted tables are the exact copies of records being affected and they include all columns from the table (all columns, not only updated columns).

    You can determine which fields were updated with COLUMNS_UPDATED() function or check if the particular field was updated with UPDATE() function.

    See also http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/MSSQLServer/best-practice-coding-sql-server-triggers for triggers best practices.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by Nathon Dalton Tuesday, January 5, 2010 3:41 PM
    Tuesday, January 5, 2010 1:10 AM
  • You 'should not' use a trigger for the INSERT. Use column defaults instead.

    ...
    Created     datetime       DEFAULT getdate(),
    CreatedBy varchar(100) DEFAULT system_user,
    ...

    Often data auditing is accomplished best with 'shadow' tables. On UPDATE or DELETE move the last copy of the row into the 'shadow table'. The production table will have the current values, including the last modified who/date, and the 'shadow table' will contain the entire prior history. In this fashion, the production table is not burdened with deleted data, and you can track multiple modifications.


    "You cannot do a kindness too soon because you never know how soon it will be too late." -Ralph Waldo Emerson
    • Proposed as answer by Naomi N Tuesday, January 5, 2010 5:05 AM
    • Marked as answer by Nathon Dalton Tuesday, January 5, 2010 3:41 PM
    Tuesday, January 5, 2010 5:03 AM
  • You can have also only one trigger to make Insert/update/delete

     

    IF EXISTS (SELECT 1 FROM INSERTED)

         BEGIN

             IF EXISTS(SELECT 1 FROM DELETED)

                   BEGIN

                       -- Update

                   END

              ELSE

                   BEGIN

                       --Insert

                   END

         END

    ELSE

         BEGIN

            --Delete

         END

    Thanks,

    Sandeep

     

    • Marked as answer by Nathon Dalton Tuesday, January 5, 2010 5:14 PM
    Tuesday, January 5, 2010 3:47 PM
  • Hi Nathon,

    I believe your implementation is correct and this is probably the way I would implement it. In general I prefer to use smaller units for the task rather than trying to incorporate the logic into one procedure / trigger code.

    I'd like to hear other people opinions, but I think you need to use update and delete triggers separately and the default values for insert operation.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by Nathon Dalton Tuesday, January 5, 2010 5:38 PM
    Tuesday, January 5, 2010 5:26 PM

All replies

  • I'd have two triggers (one for insert/update and one for delete)... the following will work but bear in mind I've only put it together in a few minutes...

    CREATE TRIGGER TR_MyTable_Delete ON MyTable
    INSTEAD OF DELETE
    AS
    BEGIN
    UPDATE MT
    SET Deleted = GETUTCDATE(), DeletedBy = SUSER_SNAME()
    FROM MyTable MT
    JOIN DELETED D ON MT.TableID = D.TableID
    END
    GO
    
    CREATE TRIGGER TR_MyTrigger ON MyTable
    FOR INSERT, UPDATE
    AS
    BEGIN
    
    IF EXISTS(SELECT 1 FROM INSERTED)
    BEGIN
    	IF EXISTS(SELECT 1 FROM DELETED)
    	BEGIN
    		UPDATE MT
    		SET Modified = GETUTCDATE(), ModifiedBy = SUSER_SNAME()
    		FROM MyTable MT
    		JOIN INSERTED I ON MT.TableID = I.TableID
    		END
    	ELSE
    	BEGIN
    		UPDATE MT
    		SET Created = GETUTCDATE(), CreatedBy = SUSER_SNAME()		
    		FROM MyTable MT
    		JOIN INSERTED I ON MT.TableID = I.TableID
    	END
    END
    
    END
    


    Dave
    • Proposed as answer by Naomi N Tuesday, January 5, 2010 1:06 AM
    Monday, January 4, 2010 11:36 PM
  • Awesome! So, I understand the Deleted one perfectly. I have a question on the insert/update one though.

    1. Are the inserted and deleted tables per transaction? In otherwords, will selecting 1 from inserted or from deleted return a value if other inserts are taking place at the same time?
    2. Are the records in the inserted and deleted tables exact copies of the records being affected including all columns from the table? In otherwords all values will be present, or just those columns being updated?

    Thanks for your help!


    Nathon Dalton .NET Software Developer
    Tuesday, January 5, 2010 12:03 AM
  • 1. The Inserted and Deleted tables are per transaction, in other words, per the statement that caused the trigger to fire (DELETE statement or UPDATE statement or INSERT statement).

    2. Yes, the records in the Inserted and Deleted tables are the exact copies of records being affected and they include all columns from the table (all columns, not only updated columns).

    You can determine which fields were updated with COLUMNS_UPDATED() function or check if the particular field was updated with UPDATE() function.

    See also http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/MSSQLServer/best-practice-coding-sql-server-triggers for triggers best practices.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by Nathon Dalton Tuesday, January 5, 2010 3:41 PM
    Tuesday, January 5, 2010 1:10 AM
  • You 'should not' use a trigger for the INSERT. Use column defaults instead.

    ...
    Created     datetime       DEFAULT getdate(),
    CreatedBy varchar(100) DEFAULT system_user,
    ...

    Often data auditing is accomplished best with 'shadow' tables. On UPDATE or DELETE move the last copy of the row into the 'shadow table'. The production table will have the current values, including the last modified who/date, and the 'shadow table' will contain the entire prior history. In this fashion, the production table is not burdened with deleted data, and you can track multiple modifications.


    "You cannot do a kindness too soon because you never know how soon it will be too late." -Ralph Waldo Emerson
    • Proposed as answer by Naomi N Tuesday, January 5, 2010 5:05 AM
    • Marked as answer by Nathon Dalton Tuesday, January 5, 2010 3:41 PM
    Tuesday, January 5, 2010 5:03 AM
  • Thanks Arnie! That's a great idea. And Naom, thanks for answering my questions! I think between everyones answers, I've got the trigger issue (and when not to use them) dealt with. Thanks again!
    Nathon Dalton .NET Software Developer
    Tuesday, January 5, 2010 3:41 PM
  • You can have also only one trigger to make Insert/update/delete

     

    IF EXISTS (SELECT 1 FROM INSERTED)

         BEGIN

             IF EXISTS(SELECT 1 FROM DELETED)

                   BEGIN

                       -- Update

                   END

              ELSE

                   BEGIN

                       --Insert

                   END

         END

    ELSE

         BEGIN

            --Delete

         END

    Thanks,

    Sandeep

     

    • Marked as answer by Nathon Dalton Tuesday, January 5, 2010 5:14 PM
    Tuesday, January 5, 2010 3:47 PM
  • So, if there's at least one record in both the inserted and deleted tables then it's updating one or more records? I assume this is because it's performing a delete and insert operation to do the update?
    Nathon Dalton .NET Software Developer
    Tuesday, January 5, 2010 4:18 PM
  • Yes, basically you can use one trigger for all operations. If you have records in both Inserted and Deleted, then it's an UPDATE operation, if only in Inserted, it's an Insert, if only in Deleted, it's a Delete operation.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, January 5, 2010 4:25 PM
  • However, this won't work if I'm trying to cancel the delete operation, right? I have to use an instead of trigger for the delete portion? What I want to do on deletes is just mark the deleted date and who did it and leave the record.
    Nathon Dalton .NET Software Developer
    Tuesday, January 5, 2010 4:42 PM
  • You can simply put ROLLBACK in the trigger code and then delete would not happen.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, January 5, 2010 4:56 PM
  • Right now I'm using three different solutions. Please let me know if you think this is worth going back and removing all of this or if what I'm doing is a more 'elegent' solution or if it's too cumbersome.

    1. Create - Using default constraint to automatically insert getdate and system_user for the created and createdby fields upon insert.
    2. Update - Using a trigger to populate the modified and modifiedby fields.
    3. Delete - Using an instead of trigger to cancel the delete operation and instead populate the deleted and deletedby fields.

    I greatly appreciate your professional opinions on this as I can see several ways to go about this. I do like the idea of using one trigger for everything, but at this point I've already started implementing the other solutions and only have to put in the update triggers now. Would you think this 'all-in-one' solution of one trigger that Naom is suggesting is worth going back and changing what I'm doing or is the way I'm going about it best? I do like the idea of one trigger for everything though, if it will cover all my bases.

    Thanks NAOM for all your suggestions!!!
    Nathon Dalton .NET Software Developer
    Tuesday, January 5, 2010 5:13 PM
  • Hi Nathon,

    I believe your implementation is correct and this is probably the way I would implement it. In general I prefer to use smaller units for the task rather than trying to incorporate the logic into one procedure / trigger code.

    I'd like to hear other people opinions, but I think you need to use update and delete triggers separately and the default values for insert operation.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by Nathon Dalton Tuesday, January 5, 2010 5:38 PM
    Tuesday, January 5, 2010 5:26 PM
  • Okay, I figured that was the best implementation, but I couldn't shake the idea of one trigger for everything being so convenient. I am also very interested in hearing from others as well.
    Tuesday, January 5, 2010 5:37 PM
  • Actually, using the INSTEAD OF trigger is the best option for the DELETE operation. A FOR DELETE trigger fires after the row has been deleted, and then has to do a rowback. If you do not wish the row to be deleted, don't allow the deletion/trigger/rollback cycle. Totally unnecessary performance hits.

    So, your idea of

    INSERT -use DEFAULTS
    UPDATE -FOR UPDATE trigger
    DELETE -INSTEAD OF trigger

    will be the most efficient.

    Also, handling the condition logic in a single trigger to determine if it is an update or delete unnecessarily costs extra processing cycles on every operation.


    "You cannot do a kindness too soon because you never know how soon it will be too late." -Ralph Waldo Emerson
    Tuesday, January 5, 2010 10:52 PM
  • I have implemented it exactly like what you were describing above. However, I just realized that the default constraint for the insert works, but allows something that I don't want. With a default constraint, it only happens if you don't specify a value when inserting. I don't want someone to be able to specify another value. Which, I believe means I'll have to go to using a trigger for this to enforce it? Do you have a better idea? For each of the created, modified and deleted set of fields, I don't want people to be able to place values in them whether from the app or SQL.
    Nathon Dalton
    Software Developer
    Systems Administrator
    Network Administrator
    Blog: http://nathondalton.wordpress.com
    Friday, January 8, 2010 9:01 PM
  • You should not allow the application to supply values for the created who/date. And I certainly hope that end users are not using a SQL management tool to manipulate data.

    Either use a stored procedure, allowing only the correct field values, or do not allow the client applications to directly use the tables -use a view instead.

    In either case, the end users should not be allowed to supply values for created who/date.


    "You cannot do a kindness too soon because you never know how soon it will be too late." -Ralph Waldo Emerson
    Saturday, January 9, 2010 2:28 AM
  • Yea, in your first post you recommended that I use default values for the values being put into the Created and CreatedBy fields upon insert and triggers for the deleted and modified fields. However, if someone were to pass values for the created fields upon insert, it would accept those over the default values (unless I'm missing something). So, I decided to move all of it into a trigger so that it's forced.

    The bit about using SQL management studio referred to me or other DBAs creating/modifying/deleting records. I want this functionality regardless of who's using it. Now I know that DBAs can get around it via other means. However, this should cover most normal use.


    Nathon Dalton
    Software Developer
    Systems Administrator
    Network Administrator
    Blog: http://nathondalton.wordpress.com
    Monday, January 11, 2010 5:03 PM
  • The triggers could been dropped or disabled.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, January 11, 2010 5:15 PM
  • I know, but is there any method that could not be disabled, dropped, etc. by someone with privelages? I'm just going for the best option for "NORMAL USE".
    Nathon Dalton
    Software Developer
    Systems Administrator
    Network Administrator
    Blog: http://nathondalton.wordpress.com
    Monday, January 11, 2010 5:53 PM
  • I think using DEFAULT values along with the trigger is as good as you can get. It is still easy to bypass by sending values by application (and thus overwriting the defaults) and dropping the triggers.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, January 11, 2010 6:08 PM