Update Trigger to modify two tables

• bpmosullivan

  Mr or Mrs. 500

  Points: 538

  More actions

  September 25, 2018 at 8:30 pm

  #2007038

  i just want the after trigger to record data in one record instead of two etc.

  ID | OLDVALUE | NEWVALUE

  It is showing the following 2 records at present
  ID | OLDVALUE | NEWVALUE (This is correct)
  ID | NEWVALUE | NEWVALUE (Dont want to show this)

• Joe Torre

  SSChampion

  Points: 10248

  More actions

  September 26, 2018 at 10:56 am

  #2007124

  Try:
  ALTER TRIGGER [dbo].[InfoTableUpdateTrigger]
ON [dbo].[InfoTable]
WITH SCHEMABINDING
FOR AFTER UPDATE
AS
INSERT INTO dbo.InfoTable_AuditTable   ( ID_COUNT_
   , TYPE
   , OLDType)
SELECT i.ID_COUNT_
     , i.Type_
     , d.Type_
FROM Inserted i
INNER JOIN Deleted d
ON i.ID_COUNT_ = d.ID_COUNT_;
WITH t AS
       (
       SELECT it.ID_COUNT_
             , SUSER_SNAME() LastUpdatedBy
             , GetDate()     TimeRecorded
       FROM dbo.InfoTable it
       JOIN inserted i
       ON it.ID_COUNT_ = i.ID_COUNT_
       GROUP BY
                it.ID_COUNT_
              , SUSER_SNAME()
             , GetDate()
       )
  UPDATE it
  SET LastUpdatedBy = SUSER_SNAME()
    , TimeRecorded  = GetDate()
  FROM t
  JOIN inserted i ON t.ID_COUNT_=i.ID_COUNT_;
  GO
  

• Scott Coleman

  One Orange Chip

  Points: 27452

  More actions

  September 26, 2018 at 2:01 pm

  #2007156

  You may be getting two archive INSERTs because of the UPDATE inside your trigger.

  You could surround the INSERT with an IF to avoid auditing when the Type_ column was not part of the UPDATE statement.
  IF UPDATE(Type_) BEGIN
    INSERT INTO dbo.InfoTable_AuditTable ...
END