Announcement

Collapse
No announcement yet.

Time, date & user stamping a modified record?

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Time, date & user stamping a modified record?

    Hi all,

    I'm rather new to SQL so please excuse my knowledge & terminology, I'm trying to learn by creating myself a database using SQL 2008 R2...

    I've managed to create 2 fields that auto populate:
    TS_CREATED with datatype datetime2(3) default binding (sysdatetime())
    &
    USR_CREATED with datatype varchar(15) default binding (suser_sname())

    These both work as I hoped, logging the username, time and date of when a record was created. I'm really stuck on how to create the same thing but for when a record is modified.... Is it even possible?

    Thanks

    Dave

  • #2
    Re: Time, date & user stamping a modified record?

    You should be able to do it with a trigger - after update would be the preferred one, IMHO

    Examples for the timestamp, but username is similar:
    https://www.google.co.uk/search?q=sq...-GB:IE-Address
    Tom Jones
    MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
    PhD, MSc, FIAP, MIITT
    IT Trainer / Consultant
    Ossian Ltd
    Scotland

    ** Remember to give credit where credit is due and leave reputation points where appropriate **

    Comment


    • #3
      Re: Time, date & user stamping a modified record?

      Thanks for the pointer,

      I can get the modified date working ok :

      Create TRIGGER dbo.SetTimeDateUpdatedInEmployee
      ON dbo.Employee
      AFTER UPDATE
      AS
      BEGIN
      IF NOT UPDATE(TS_Modified)
      BEGIN
      UPDATE t
      SET t.TS_Modified = CURRENT_TIMESTAMP
      FROM dbo.Employee AS t
      INNER JOIN inserted AS i
      ON t.EID = i.EID;
      END
      END
      GO

      Thought it would be easy to modify the trigger to do the username like so:

      Create TRIGGER dbo.SetUserUpdatedInEmployee
      ON dbo.Employee
      AFTER UPDATE
      AS
      BEGIN
      IF NOT UPDATE(USR_Modified)
      BEGIN
      UPDATE t
      SET t.USR_Modified = CURRENT_USER
      FROM dbo.Employee AS t
      INNER JOIN inserted AS i
      ON t.EID = i.EID;
      END
      END
      GO

      But I get a 'Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)' error. Not too sure what this means, have I misunderstood something??

      Thanks

      Dave

      Comment


      • #4
        Re: Time, date & user stamping a modified record?

        As a guess, you have two separate update triggers
        An external modification updates the record, and calls
        Trigger A, which updates the time stamp, so modifies the record
        Also Trigger B updates the user name, modifying the record and calling trigger A again, then B, then A....

        After 32 calls, SQL falls over!

        Basically, have one trigger which does both modifications
        Tom Jones
        MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
        PhD, MSc, FIAP, MIITT
        IT Trainer / Consultant
        Ossian Ltd
        Scotland

        ** Remember to give credit where credit is due and leave reputation points where appropriate **

        Comment


        • #5
          Re: Time, date & user stamping a modified record?

          Doh! Yup that makes perfect sense....

          Create TRIGGER dbo.SetLastUpdatedEmployee
          ON dbo.Employee
          AFTER UPDATE
          AS
          BEGIN
          IF NOT UPDATE(TS_Modified)
          BEGIN
          UPDATE t
          SET t.TS_Modified = CURRENT_TIMESTAMP
          FROM dbo.Employee AS t
          INNER JOIN inserted AS i
          ON t.EID = i.EID;
          END
          IF NOT UPDATE(USR_Modified)
          BEGIN
          UPDATE t
          SET t.USR_Modified = CURRENT_USER
          FROM dbo.Employee AS t
          INNER JOIN inserted AS i
          ON t.EID = i.EID;
          END
          END
          GO

          Put both together in the same trigger and it looks like its working just fine

          Thank you!!
          Last edited by QuattroDave; 8th January 2014, 21:54. Reason: TYPO!

          Comment


          • #6
            Re: Time, date & user stamping a modified record?

            Glad to help!
            Tom Jones
            MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
            PhD, MSc, FIAP, MIITT
            IT Trainer / Consultant
            Ossian Ltd
            Scotland

            ** Remember to give credit where credit is due and leave reputation points where appropriate **

            Comment

            Working...
            X