title: what is the correct syntax for creating a database trigger for insert, modify and delete
body: <p>i have what seems like a basic scenario for a db trigger in SQL server and i am running into an issue.</p>
  
  <p>i have table  <strong>Users</strong> (id, name, phone, etc) and i have tables <strong>UsersHistory</strong> (id, user_id action, fields, timestamp)</p>
  
  <p>i want a database trigger where anytime inserts, updates or deletes into Users, i want a new record created in UsersHistory with the user id and the action that was done (insert new, updated fields, deleted id.  Basically an audit log table.</p>
  
  <p>this is how far i got, but i can't figure out how to:</p>
  
  <ol>
  <li>Get the id on modify and deletes and also</li>
  <li>How to get a list of fields that have changed and the action that was committed (insert, delete, update)</li>
  </ol>
  
  <hr>
  
  <pre><code>CREATE TRIGGER Update_Users_History 
     ON  Users
     AFTER INSERT,DELETE,UPDATE
   AS 
   BEGIN
  -- Insert statements for trigger here
  
  insert into UsersHistory (user_id, [action], [fields], timestamp)
  select  max(id) as user_id, {action ??},{fields??}  getdate() from Users)
  
  END
  GO
  </code></pre>
  
  <p>any suggestions?</p>