TSQL -> Example of UPDATE trigger

This trigger adds audit information: USER NAME and UPDATE TIME and prevents update on records with edit_status=1 for not authorized users:

CREATE TRIGGER EmpFacts_upd_trig ON dbo.EmployeeFacts
FOR UPDATE
AS
BEGIN
DECLARE @FactNo INT, @editstatus INT, @user Varchar (40)

/* if 0 rows affected, do nothing */
IF @@rowcount = 0 RETURN;

/* get user_name and record id information */
SELECT @FactNo = FactNo FROM inserted;
SET @user = CURRENT_USER;

/* "deleted" table stores copy of data befor update execution */
SELECT @editstatus = edit_status FROM deleted
WHERE FactNo = @FactNo;

IF @user = 'amper' OR @user = 'dbo'
  BEGIN
  /* add update remark to the record */
   UPDATE EmployeeFacts
   SET LastUpdate = GETDATE(), UPDBY = @user
   WHERE FactNo = @FactNo;
  END
ELSE
  IF @editstatus = 1 /* The record is protected from updates */
  BEGIN
   RAISERROR ('This Record can not be Edited',12,1);
   Rollback Transaction;
  END
  ELSE /* The record is not protected */
  BEGIN
   UPDATE EmployeeFacts
   SET LastUpdate = GETDATE(), UPDBY = @user
   WHERE FactNo = @FactNo;
  END

END


sqlexamples.info