Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2007
    Posts
    30

    Unanswered: Creating a Trigger

    I have 3 tables on my db, Projects, ProljectAllocationLog and Users

    Project consists of Projectid(PK), ProjectName, UserID
    ProjectAllocationLog consists of ProjectAllocationID(PK), Projectid, UserID,Date
    Users consists of UserID (PK), Fullname

    Over the course of time the user allocated to a project may change. The db has been set up to always show the most current user in the UserID of the Projects table,

    I want to create a log that will record everytime that user has changed. (ProjectAllocationLog)

    Having read through some examples posted on Forums, I believe that I can do this with a trigger, but I am not sure if I am doing it right, the trigger I have written is

    Create Trigger tr_UpdateAllocationLog
    ON Projects
    AFTER Update
    AS
    If NOT UPDATE (Userid)

    DECLARE @PROJECTID
    DECLARE @NEWUSER
    DECLARE @PREVIOUSUSER

    SET @PROJECTID= (SELECT projected FROM Inserted)
    SET @NEWUSER = (SELECT UserID from Inserted)
    SET @ PREVIOUSUSER = (SELECT UserID from Deleted)

    If @NEWUSER <> @PREVIOUSUSER

    INSERT INTO ProjectAllocationLog (ProjectID, UserID, Date) VALUES (@PROJECTID, @NEWUSER, GETDATE())

    Go

    I would appreciate any comments

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    something like this is a little more graceful and handles multiple updates...

    Code:
    CREATE TRIGGER dbo.trg_ProjectStatusHistory
    ON dbo.Project
    FOR INSERT, UPDATE
    AS
    INSERT INTO ProjectStatusHistory(ProjectID,StatusID,SystemStatusID,DateChanged,UpdatedByUIDKey) 
    SELECT i.ProjectId,i.StatusId,i.SystemStatusId,GETDATE(),i.UpdatedByUIDKey
    FROM inserted i
    LEFT JOIN deleted d
    ON i.ProjectId = d.ProjectID
    WHERE i.StatusId <> d.StatusId OR i.SystemStatusId <> d.SystemStatusId
    OR (i.StatusId is not null and d.Statusid is null)
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Oct 2007
    Posts
    30
    Briliant, thank you very much, I will do it the way you suggested, however, just out of curiosty, would my way have worked even though it was a very clumsey way of doing it?

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    yours would not have worked for transactions that updated multiple rows and my memory is a little cloudy on this because I do not code a lot of triggers but I think I had a problem once calling records from one of the virtual tables and then calling records from another like you did here...

    Code:
    SET @NEWUSER = (SELECT UserID from Inserted)
    SET @ PREVIOUSUSER = (SELECT UserID from Deleted)
    This might not work but I am not sure why. It was a while ago and if something happened more 3 days ago it might as well have never happened at all.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That will only pull the first ID from inserted, and the first ID from deleted, and there is no guarantee that the two virtual tables will even be sorted identically.

    You MUST use set-based operations in triggers.
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Oct 2007
    Posts
    30
    I dont understand what you mean by set based operations, can you explain please

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    do not mind the blind dude. it's time for his medication.

    the trigger in my first post is set based. he did not see it.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    Oct 2007
    Posts
    30
    but what does set based mean exactly? (Sorry to sound such a thicko)

  9. #9
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    google "set based thinking"

    here's one of many links:

    http://weblogs.sqlteam.com/jeffs/arc.../30/60192.aspx

    -- This is all just a Figment of my Imagination --

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    This is funny

    I just had a request to build a "Project" app

    I was so disdaned by the fact that I billed them a boatload and told them to buy MS Project and to send the users to class
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    and I bet nobody blinked twice.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Thrasymachus
    do not mind the blind dude. it's time for his medication.

    the trigger in my first post is set based. he did not see it.
    I was referring to this:
    Code:
    SET @NEWUSER = (SELECT UserID from Inserted)
    SET @ PREVIOUSUSER = (SELECT UserID from Deleted)
    And somebody better be minding the blind dude. Who's turn is it today, anyway? 'Cause nobody made me breakfast.
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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