title: SQL Server trigger - modify the id column
body: <p>After hours of googling and search here , I am able to create this trigger; This trigger works well, and keeps the two tables synced in 2 different tables. (I am not worried about batched operations, that wouldn't happen). The only issue is that If the USER_ID is modified (that is primary key), it does not gets updated in 2nd table); I am not able to get the 'UPDATE' part right. What am I doing wrong?</p>
  
  <pre><code>ALTER TRIGGER [u].[SyncUsers] 
     ON  [u].[USERS] 
     AFTER INSERT,DELETE,UPDATE
  AS 
  
  BEGIN
  
      IF (@@rowcount = 0) RETURN;
      SET NOCOUNT ON;
  
      -- deleted data
      IF NOT EXISTS (SELECT * FROM inserted) 
          BEGIN
              DELETE FROM ERP_DB.u.USERS WHERE USER_ID = 
              (SELECT USER_ID FROM deleted);
              RETURN;
          END
      ELSE 
          BEGIN
  
              -- inserted data
              IF NOT EXISTS (SELECT * FROM deleted)
                  BEGIN           
                      INSERT INTO ERP_DB.u.USERS(USER_ID, USER_NAME , DISPLAY_NAME , EMP_ID , DEPARTMENT_ID)  SELECT USER_ID, USER_NAME , DISPLAY_NAME , EMP_ID , DEPARTMENT_ID FROM inserted;
                      RETURN;
                  END
  
              -- updated data
              ELSE
                  BEGIN
                      UPDATE UU
                          SET UU.USER_ID = i.USER_ID,
                              UU.USER_NAME = i.USER_NAME ,
                              UU.DISPLAY_NAME = i.DISPLAY_NAME ,
                              UU.DEPARTMENT_ID = i.DEPARTMENT_ID ,
                              UU.EMP_ID = i.EMP_ID 
  
                              FROM ERP_DB.u.USERS AS UU
                  INNER JOIN Inserted AS i ON UU.USER_ID = i.USER_ID
  
              RETURN;
          END 
      END -- updated data
      END
  END
  </code></pre>