T-SQL Insert Update Trigger Sample

This sample presents a T-SQL DML Trigger.

A trigger is actually just a T-SQL script.

http://msdn.microsoft.com/en-us/library/ms189799.aspx

It makes use of the special 'inserted' table.

http://msdn.microsoft.com/en-us/library/ms191300.aspx

It is important to make use of exception catching to avoid chain failure.

The following sample create an insert and update DML trigger on the table called 'tbl_user_profile'.

Whenever a single record is being inserted or updated to 'tbl_user_profile', this script will be triggered.

The trigger will first check out the latest data from 'inserted' table, then it will attempt to update the destination table called 'temp_table_1'. If update success, it means existing record exists, otherwise, it will attempt to insert new record into 'temp_table_1'.

IF OBJECT_ID ('tr_sync_table','TR') IS NOT NULL
   DROP TRIGGER tr_sync_table;
GO

CREATE TRIGGER tr_sync_table
on tbl_user_profile
After insert,update
AS

BEGIN TRY

SET NOCOUNT ON

declare @successCount as bigint,@errCount as bigint
declare @cUserID as varchar(10)
declare @USER_Name as varchar(80)

SELECT @cUserID=i.USER_ID,@USER_Name=i.USER_NAME FROM inserted i 

if @@rowcount>=1
BEGIN

 BEGIN TRY
            UPDATE [temp_table_1]
            SET cUsername=@USER_Name
            WHERE cUserID=@cUserID
            
            if @@ROWCOUNT >=1 
               BEGIN
                 
                  SELECT @successCount = @successCount+1
                 
               END
            ELSE
               BEGIN
               -- INSERT -----
                 BEGIN TRY
                   INSERT INTO [temp_table_1] (cUserID,cUsername) 
                   VALUES (@cUserID,@USER_Name)
                   
                   SELECT @successCount = @successCount+1
                   
                 END TRY
                 BEGIN CATCH
                  
                   SELECT @errCount = @errCount+1
                  
                 END CATCH
            END
        END TRY
        BEGIN CATCH
           
           SELECT @errCount = @errCount+1
          
        END CATCH
        
END

END TRY

BEGIN CATCH
   --RAISERROR ('tr_sync_table', 16, 1);
END CATCH
 
Go