title: Creating a trigger on SQL Server 2008 R2 to catch a record update
body: <p>Using Great Plains here and one of our users keeps screwing up customer data so we want to put a trigger on the customer table and so we can find out who it is.</p>
  
  <p>Anyway, I created a table called audit_RM00101 as follows:</p>
  
  <pre><code>DATE        nchar(10)   
  CUSTNMBR    char(15)    
  CUSTNAME    char(65)    
  UPSZONE     char(3) 
  SALSTERR    char(15)    
  USERID      nchar(100)
  </code></pre>
  
  <p>I want to capture those same fields from the table I want to audit so I wrote the trigger as follows:</p>
  
  <pre><code>CREATE TRIGGER CatchCustomerRegionUpdate
  ON RM00101
  FOR UPDATE
  AS
  DECLARE @UserID VARCHAR(128)
  SELECT @UserID = system_user
  INSERT INTO audit_RM00101 
  SELECT DATE, CUSTNMBR, CUSTNAME, UPSZONE, SALSTERR, @UserID FROM UPDATED
  </code></pre>
  
  <p>The trigger gets created just fine but when I try to test it by updating a customer record in Great Plains, Great Plains throws up an ugly error and the trigger doesn't get fired.</p>
  
  <p>What am I doing wrong here?</p>
  
  <p>Thanks.</p>