SqlSever Trigger For Insert/Delete/Update example.

gistfile1.sql

ALTER TRIGGER [dbo].[CM_ApprovalMatrix_Settings_Trigger]
   ON  [dbo].[CM_ApprovalMatrix_Settings]
   FOR INSERT,DELETE,UPDATE
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @del INT,@ins INT,@status NVARCHAR(50)
	SET @del = (SELECT COUNT(ID) FROM deleted)
	SET @ins = (SELECT COUNT(ID) FROM inserted)
	IF(@del!=0 AND @ins!=0)
		SET @status = 'UPDATE'
	ELSE IF(@del!=0 AND @ins=0)
		SET @status = 'DELETE'
	ELSE IF(@del=0 AND @ins!=0)
		SET @status = 'INSERT'
	
	IF EXISTS (SELECT ID FROM deleted)
		INSERT INTO CM_ApprovalMatrix_Settings_History
		SELECT d.*,@status FROM deleted d
	ELSE IF EXISTS (SELECT ID FROM inserted)
		INSERT INTO CM_ApprovalMatrix_Settings_History
		--(ID,RoleId,CategoryID,Amount,CreatedBy,CreatedTime,ModifiedBy,ModifiedTime,[Status],OperationType)
		SELECT I.*,@status FROM inserted I
END