SQL Server Trigger Template

This a template for creating an After/Instead Of trigger in SQL Server 2005/2008:

CREATE TRIGGER <schema>.<tablename>$[InsteadOf]<actions>[<purpose>]Trigger
ON <schema>.<tablename>
[AFTER|INSTEAD OF] <comma delimited actions> AS
BEGIN

 DECLARE @rowsAffected INT,  --stores the number of rows affected
         @msg VARCHAR(2000)  --used to hold error message
 SET @rowsAffected = @@ROWCOUNT
 
 IF @rowsAffected = 0 RETURN
 
 SET NOCOUNT ON --to avoid the rowcount messages
 SET ROWCOUNT 0 --in case client has modified the rowcount
 
 BEGIN TRY
  --[validation section]
  
  --[modification section]

  --[perform action] --for INSTEAD OF trigger
 END TRY
 BEGIN CATCH
  IF @@trancount > 0
   ROLLBACK TRANSACTION
  
  --log the error
  EXECUTE utility.ErrorLog$insert  --this is only one example to do logging
  
  DECLARE @ERROR_MESSAGE NVARCHAR(4000)
  SET @ERROR_MESSAGE = ERROR_MESSAGE()
  RAISERROR (@ERROR_MESSAGE, 16, 1)
 END CATCH
END

Below is an example of using a table and a procedure to do error logging:

CREATE TABLE utility.ErrorLog(
 ERROR_NUMBER int NOT NULL,
 ERROR_LOCATION sysname NOT NULL,
 ERROR_MESSAGE varchar(4000),
 ERROR_DATE datetime NULL
  CONSTRAINT dfltErrorLog_error_date DEFAULT (getdate()),
 ERROR_USER sysname NOT NULL
  --use original login to capture the user name of the actual user
  --not a user that has been impersonated
  CONSTRAINT dfltErrorLog_error_user_name DEFAULT (original_login())
)
GO

CREATE PROCEDURE utility.ErrorLog$insert
(
 @ERROR_NUMBER int = NULL,
 @ERROR_LOCATION sysname = NULL,
 @ERROR_MESSAGE varchar(4000) = NULL
) AS
BEGIN
 BEGIN TRY
  INSERT INTO utility.ErrorLog(ERROR_NUMBER, ERROR_LOCATION, ERROR_MESSAGE)
  SELECT ISNULL(@ERROR_NUMBER, ERROR_NUMBER()),
      ISNULL(@ERROR_LOCATION, ERROR_MESSAGE()),
      ISNULL(@ERROR_MESSAGE, ERROR_MESSAGE())
 END TRY
 BEGIN CATCH
  INSERT INTO utility.ErrorLog(ERROR_NUMBER, ERROR_LOCATION, ERROR_MESSAGE)
  VALUES (-100, 'utility.ErrorLog$insert',
      'An invalid call was made to the error log procedure')
 END CATCH
END

Reference:
Pro SQL Server 2008 Relational Database Design and Implementation - Louis Davidson