title: Log record changes in SQL server in an audit table
body: <p>The table :</p>
  
  <pre><code>    CREATE TABLE GUESTS (
        GUEST_ID int IDENTITY(1,1) PRIMARY KEY, 
        GUEST_NAME VARCHAR(50), 
        GUEST_SURNAME VARCHAR(50), 
        ADRESS VARCHAR(100), 
        CITY VARCHAR(50), 
        CITY_CODE VARCHAR(10), 
        COUNTRY VARCHAR(50), 
        STATUS VARCHAR(20), 
        COMMENT nvarchar(max);
  </code></pre>
  
  <p>For the logging :</p>
  
  <pre><code>CREATE TABLE AUDIT_GUESTS (
    ID int IDENTITY(1,1) PRIMARY KEY, 
    GUEST_ID int,
    OLD_GUEST_NAME VARCHAR(50), 
    NEW_GUEST_NAME VARCHAR(50), 
    OLD_GUEST_SURNAME VARCHAR(50), 
    NEW_GUEST_SURNAME VARCHAR(50),
    OLD_ADRESS VARCHAR(100), 
    NEW_ADRESS VARCHAR(100),
    OLD_CITY VARCHAR(50), 
    NEW_CITY VARCHAR(50),
    OLD_CITY_CODE VARCHAR(10), 
    NEW_CITY_CODE VARCHAR(10), 
    OLD_COUNTRY VARCHAR(50), 
    NEW_COUNTRY VARCHAR(50), 
    OLD_STATUS VARCHAR(20), 
    NEW_STATUS VARCHAR(20), 
    OLD_COMMENT nvarchar(max), 
    NEW_COMMENT nvarchar(max), 
    AUDIT_ACTION varchar(100),
    AUDIT_TIMESTAMP datetime);
  </code></pre>
  
  <p>I would like to create a trigger on my <code>GUESTS</code> table to log all changes in my <code>AUDIT_GUESTS</code> table. How can I do that in SQL Server 2014 Express ?</p>
  
  <p>I tried :</p>
  
  <pre><code>create TRIGGER trgAfterUpdate ON [dbo].[GUESTS] 
  FOR UPDATE
  AS
      declare @GUEST_ID int;
      declare @GUEST_NAME varchar(50);
      declare @GUEST_SURNAME VARCHAR(50);
      declare @ADRESS VARCHAR(100); 
      declare @CITY VARCHAR(50);
      declare @CITY_CODE VARCHAR(10); 
      declare @COUNTRY VARCHAR(50);
      declare @STATUS VARCHAR(20);
      declare @COMMENT nvarchar(max);
      declare @AUDIT_ACTION varchar(100);
      declare @AUDIT_TIMESTAMP datetime;
  
      select @GUEST_ID=i.GUEST_ID from inserted i;            
      select @GUEST_NAME=i.GUEST_NAME from inserted i;    
      select @GUEST_SURNAME=i.GUEST_SURNAME from inserted i;
      select @ADRESS=i.ADRESS from inserted i;
      select @CITY=i.CITY from inserted i;
      select @CITY_CODE=i.CITY_CODE from inserted i;
      select @COUNTRY=i.COUNTRY from inserted i;
      select @STATUS=i.STATUS from inserted i;
      select @COMMENT=i.COMMENT from inserted i;
  
          if update(GUEST_NAME)
          set @audit_action='Updated Record -- After Update Trigger.';
  
          if update(GUEST_SURNAME)
          set @audit_action='Updated Record -- After Update Trigger.';
  
          if update(ADRESS)
          set @audit_action='Updated Record -- After Update Trigger.';
  
          if update(CITY)
          set @audit_action='Updated Record -- After Update Trigger.';
  
          if update(CITY_CODE)
          set @audit_action='Updated Record -- After Update Trigger.';
  
          if update(COUNTRY)
          set @audit_action='Updated Record -- After Update Trigger.';
  
          if update(STATUS)
          set @audit_action='Updated Record -- After Update Trigger.';
  
          if update(COMMENT)
          set @audit_action='Updated Record -- After Update Trigger.';
  
          insert into AUDIT_GUESTS
             (GUEST_ID,GUEST_NAME,GUEST_SURNAME,ADRESS,CITY,CITY_CODE,COUNTRY,STATUS,COMMENT,audit_action,AUDIT_TIMESTAMP) 
      values(@GUEST_ID,@GUEST_NAME,@GUEST_SURNAME,@ADRESS,@CITY,@CITY_CODE,@COUNTRY,@STATUS,@COMMENT,@audit_action,getdate());
      GO
  </code></pre>
  
  <p>Works kind of ok but I would like to see old-new values.</p>
  
  <p>In SQLite I had :</p>
  
  <pre><code>CREATE TRIGGER [LOG_UPDATE]
  AFTER UPDATE OF [GUEST_NAME], [GUEST_SURNAME], [ADRESS], [CITY], [CITY_CODE], [COUNTRY], [STATUS], [COMMENT]
  ON [GUESTS]
  BEGIN
  INSERT INTO GUESTS_LOG
   ( GUEST_ID,
     NAME_OLD,NAME_NEW,
     SURNAME_OLD,SURNAME_NEW,
     ADRESS_OLD,ADRESS_NEW,
     CITY_OLD,CITY_NEW,
     CITY_CODE_OLD,CITY_CODE_NEW,
     COUNTRY_OLD,COUNTRY_NEW,
     STATUS_OLD,STATUS_NEW,   
     COMMENT_OLD,COMMENT_NEW,sqlAction,DATE_TIME)   
  
     VALUES   
  
   (OLD.GUEST_ID,
    OLD.GUEST_NAME,NEW.GUEST_NAME, 
    OLD.GUEST_SURNAME,NEW.GUEST_SURNAME,
    OLD.ADRESS,NEW.ADRESS,
    OLD.CITY,NEW.CITY,
    OLD.CITY_CODE,NEW.CITY_CODE,
    OLD.COUNTRY,NEW.COUNTRY,  
    OLD.STATUS,NEW.STATUS,
    OLD.COMMENT,NEW.COMMENT,'record changed',datetime('now','localtime'));  
  
  END
  </code></pre>
  
  <p>and it worked OK. Just dont know how to pass this to SQL server. Just begun learning it.</p>