Results 1 to 14 of 14

Thread: Triggers!

  1. #1
    Join Date
    Oct 2003
    Posts
    58

    Smile Unanswered: Triggers!

    Hi all,

    I am a DBA on a sql2000 sever which contains many tables.

    I have built a trigger on each table which, after an update or deletion from a specific table creates a new entry in a ghost copy of the table (in a seperate DB0 and includes the username the change type (update or delete) and the time at which the trigger was fired. This enables us to keep a audit on the changes each user makes so that we can see if mistakes have been made.

    My code at present is this:

    CREATE TRIGGER [trigger_name_here]
    ON [table_name-here]
    AFTER UPDATE
    AS

    INSERT INTO [new_db].[table_name-here]
    (ID, Field1, Field2, Field3, Field4, Field5,
    ChangeType, ChangeDate, UserName)

    SELECT ID, Field1, Field2, Field3, Field4, Field5,
    'Update', getdate(), user

    FROM inserted


    This code works great however...

    What is the difference if i use a "FOR" statement at the start of the trigger?

    If i write an "INSERT" trigger using the "FOR" statement it seems to cause problems after the insert of a new record has been made...

    Is there any variations on the last statement "FROM inserted"?

    The real problem i am having is that the trigger inserts all the fields from the table (which is fine) however after an update change to a particular field, i can see when, who, and what change was made bit i cannot tell exactly which field has been changed... is there a way of inserting the data in the field (before the change) into a new field in the ghost DB so that i can compare the before and after.

    Or get the trigger to only insert the field that has been changed?

    Sorry about the number of questions..

    Jnr.

  2. #2
    Join Date
    Jan 2004
    Posts
    49

  3. #3
    Join Date
    Oct 2003
    Posts
    58

    Wink

    I have read this stuff before and it sets out the rules but it is very limited... thanks anyway.

    jnr

  4. #4
    Join Date
    Jan 2004
    Posts
    49
    old values - tbl deleted
    new vals - tbl inserted
    ????

  5. #5
    Join Date
    Oct 2003
    Posts
    58

    Thumbs up

    Originally posted by buser
    old values - tbl deleted
    new vals - tbl inserted
    ????
    Ill give it a go...

    thanks.

    Jnr

  6. #6
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73
    Here is a trigger that I use that does almost exactly what your looking at. Whenever this table is updated the trigger checks to see if the field I'm interested is changed. If so then it inserts a record into my history table.

    Hope it helps,

    DECLARE @OSpecID AS int
    DECLARE @NSpecID AS int
    SET @OSpecID =(SELECT SpecialistID from Deleted)
    SET @NSpecID =(SELECT SpecialistID from Inserted)
    IF UPDATE (SpecialistID)
    IF @NSpecID = @OSpecID
    BEGIN
    UPDATE tlnkFamilySpecialist
    SET tlnkFamilySpecialist.LastUpdated = Deleted.LastUpdated
    FROM tlnkFamilySpecialist JOIN Deleted ON tlnkFamilySpecialist.FamilySpecialistID = Deleted.FamilySpecialistID JOIN Inserted ON Deleted.FamilySpecialistID = Inserted.FamilySpecialistID
    END
    ELSE
    BEGIN
    UPDATE tlnkFamilySpecialist
    SET tlnkFamilySpecialist.LastUpdated = GETDATE()
    FROM tlnkFamilySpecialist JOIN Inserted
    ON tlnkFamilySpecialist.FamilySpecialistID = Inserted.FamilySpecialistID
    INSERT INTO tlnkFamilySpecialistHist (FamilyID, OldSpecialistID, NewSpecialistID, DateStamp, operator)
    SELECT Inserted.FamilyID, @OSpecID, @NSpecID, GETDATE(), suser_sname()
    FROM Inserted
    END

  7. #7
    Join Date
    Oct 2003
    Posts
    58

    Smile

    Originally posted by baolive
    Here is a trigger that I use that does almost exactly what your looking at. Whenever this table is updated the trigger checks to see if the field I'm interested is changed. If so then it inserts a record into my history table.

    Hope it helps,

    DECLARE @OSpecID AS int
    DECLARE @NSpecID AS int
    SET @OSpecID =(SELECT SpecialistID from Deleted)
    SET @NSpecID =(SELECT SpecialistID from Inserted)
    IF UPDATE (SpecialistID)
    IF @NSpecID = @OSpecID
    BEGIN
    UPDATE tlnkFamilySpecialist
    SET tlnkFamilySpecialist.LastUpdated = Deleted.LastUpdated
    FROM tlnkFamilySpecialist JOIN Deleted ON tlnkFamilySpecialist.FamilySpecialistID = Deleted.FamilySpecialistID JOIN Inserted ON Deleted.FamilySpecialistID = Inserted.FamilySpecialistID
    END
    ELSE
    BEGIN
    UPDATE tlnkFamilySpecialist
    SET tlnkFamilySpecialist.LastUpdated = GETDATE()
    FROM tlnkFamilySpecialist JOIN Inserted
    ON tlnkFamilySpecialist.FamilySpecialistID = Inserted.FamilySpecialistID
    INSERT INTO tlnkFamilySpecialistHist (FamilyID, OldSpecialistID, NewSpecialistID, DateStamp, operator)
    SELECT Inserted.FamilyID, @OSpecID, @NSpecID, GETDATE(), suser_sname()
    FROM Inserted
    END
    thanks,

    I think i can use some of this in the new version i am building,

    Jnr.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    baolive, what happens if more than one record is updated at a time?

    130213, if the UPDATED functionality of SQL triggers is not appropriate, then I think you will need to use buser's idea comparing inserted values to deleted values. You could potentially build a string of:

    isnull(Nullif(deleted.column1, inserted.column1), '')
    + isnull(Nullif(deleted.column2, inserted.column2), '')
    + ...etc.

    The result would be a single field with all the old values.

    Be prepared for a performance hit.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73
    This table can only be updated (by end users) through a form that cn view only one record at a time, so that was not an issue. Valid point though.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That doesn't mean some admin or power user won't run an ad-hoc update against multiple records, or that later functionality might require group record updates. I strongly recommend that whatever solution you implement be able to handle any type of update to the table. I'd bet dollars to doughnut holes that it will come back to bite you if you don't.

    Think about it. That logic could potentially inserted hundreds of bad records (mixing data from one inserted record into data from all the inserted records) with a single statement, and it might be a long time before you would find out that it happened.
    Last edited by blindman; 02-02-04 at 13:41.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Oct 2003
    Posts
    58

    Cool

    Originally posted by blindman
    Think about it. That logic could potentially inserted hundreds of bad records (mixing data from one inserted record into data from all the inserted records) with a single statement, and it might be a long time before you would find out that it happened.
    Yep, this does happen. We often run updates on tables creating hundreds/thousands new records at a time, however with some forward planning we trun the insert or update triggers off for that run then back on again so as not to compromise the data.

    Thanks all for your input, as i am a noobe to this role any help is a plus!

    jnr.

  12. #12
    Join Date
    Oct 2003
    Posts
    58

    Question

    But can anyone tell me the differece between "FOR" and "AFTER" in the trigger definition????


    jnr.

  13. #13
    Join Date
    Jan 2004
    Posts
    49
    BOL!!!!!!!!!!

    AFTER

    Specifies that the trigger is fired only when all operations specified in the triggering SQL statement have executed successfully. All referential cascade actions and constraint checks also must succeed before this trigger executes.

    AFTER is the default, if FOR is the only keyword specified.

    AFTER triggers cannot be defined on views.

    INSTEAD OF

    Specifies that the trigger is executed instead of the triggering SQL statement, thus overriding the actions of the triggering statements.

    At most, one INSTEAD OF trigger per INSERT, UPDATE, or DELETE statement can be defined on a table or view. However, it is possible to define views on views where each view has its own INSTEAD OF trigger.

    INSTEAD OF triggers are not allowed on updateable views WITH CHECK OPTION. SQL Server will raise an error if an INSTEAD OF trigger is added to an updateable view WITH CHECK OPTION specified. The user must remove that option using ALTER VIEW before defining the INSTEAD OF trigger.

  14. #14
    Join Date
    Oct 2003
    Posts
    58

    Thumbs up

    Cheers!

    Jnr.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •