CREATE TRIGGER [dbo].[tr_NewRecord] ON [dbo].[TriggerTest]
    AFTER INSERT
AS

BEGIN
SET NOCOUNT ON;

DECLARE @ErrNo	varchar(15), @ErrMsg varchar(2000)
DECLARE @Subject varchar(500), @Body varchar(500)
DECLARE @To varchar(150), @Bcc varchar(150)

    BEGIN TRY
        INSERT INTO dbo.TriggerTestAudit (ID, FName, LName)
			SELECT ID, FName, LName FROM INSERTED
    END TRY

	BEGIN CATCH
		SELECT
			@ErrNo = ERROR_NUMBER(),
			@ErrMsg = ERROR_MESSAGE()
		SET @ErrMsg = ' :: ERROR :: ' + @ErrNo + ' <<Add Custom Msg here>>'
		SET @Subject = CAST(@@SERVERNAME AS varchar) + @ErrMsg
		SET @Body = '----------------------------------------------------------------------------------------------------'
			+ CHAR(13) + @ErrMsg + CHAR(13)
			+ '----------------------------------------------------------------------------------------------------'
			+ CHAR(13) + '<<Enter additional your blurb here>>' + CHAR(13) + CHAR(13)
			+ 'The DBA''s have been notified about the cause of this error and can assure you that the public flogging of the developer responsible for this error will be severe.'
		EXEC msdb.dbo.sp_send_dbmail @recipients = @To, @blind_copy_recipients = @Bcc, @subject = @Subject, @body = @Body, @importance = 'High'	
	END CATCH
END

CREATE TRIGGER [dbo].[tr_RecordUpdated] ON [dbo].[TriggerTest]
    AFTER UPDATE
AS

BEGIN
SET NOCOUNT ON;

DECLARE @ErrNo	varchar(15), @ErrMsg varchar(2000)
DECLARE @Subject varchar(500), @Body varchar(500)
DECLARE @To varchar(150), @Bcc varchar(150)

	BEGIN TRY
        UPDATE A
        SET ID = X.ID, 
			FName = X.FName, 
			LName = X.LName
        FROM dbo.TriggerTestAudit A
        INNER JOIN INSERTED X ON a.ID = X.ID
        WHERE A.ID = X.ID
    END TRY

	BEGIN CATCH
		SELECT
			@ErrNo = ERROR_NUMBER(),
			@ErrMsg = ERROR_MESSAGE()
		SET @ErrMsg = ' :: ERROR :: ' + @ErrNo + ' <<Add Custom Msg here>>'
		SET @Subject = CAST(@@SERVERNAME AS varchar) + @ErrMsg
		SET @Body = '----------------------------------------------------------------------------------------------------'
			+ CHAR(13) + @ErrMsg + CHAR(13)
			+ '----------------------------------------------------------------------------------------------------'
			+ CHAR(13) + '<<Enter additional your blurb here>>' + CHAR(13) + CHAR(13)
			+ 'The DBA''s have been notified about the cause of this error and can assure you that the public flogging of the developer responsible for this error will be severe.'
		EXEC msdb.dbo.sp_send_dbmail @recipients = @To, @blind_copy_recipients = @Bcc, @subject = @Subject, @body = @Body, @importance = 'High'	
	END CATCH
END

CREATE TABLE Main
    (ID INT IDENTITY PRIMARY KEY
    ,C1     DATETIME
    ,C2     VARCHAR(10)
    ,C3     INT
    ,UserID VARCHAR(20))  -- Last user who updated the record
GO
CREATE TABLE Audit
    (TableName      VARCHAR(20)
    ,ColumnName     VARCHAR(20)
    ,RecordID       INT
    ,UserID         VARCHAR(20)
    ,UpdatedDate    VARCHAR(10)
    ,OldValue       VARCHAR(100)
    ,NewValue       VARCHAR(100))
GO
CREATE TRIGGER [dbo].[tr_Audit] ON Main
    AFTER INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
    INSERT INTO Audit
    SELECT '#Main', ColName, CASE WHEN i.ID IS NULL THEN d.ID ELSE i.ID END
        ,CASE WHEN i.UserID IS NULL THEN d.UserID ELSE i.UserID END
        ,GETDATE(), OldValue, NewValue
    FROM INSERTED i
    FULL OUTER JOIN DELETED d ON i.ID = d.ID
    CROSS APPLY (
        VALUES ('C1', CONVERT(VARCHAR(27), d.C1, 113), CONVERT(VARCHAR(27), i.C1, 113))
            ,('C2', d.C2, i.C2)
            ,('C3', CAST(d.C3 AS VARCHAR(20)), CAST(i.C3 AS VARCHAR(20)))
            ) a(ColName, OldValue, NewValue)
    -- You may want to refine this WHERE just a bit.
    WHERE ISNULL(OldValue,'') <> ISNULL(NewValue,'')
END
GO

INSERT INTO Main
SELECT GETDATE()-1, 'A VALUE', 42, 'Dwain.C'
UNION ALL SELECT GETDATE()-2, 'B VALUE', 40, 'Dwain.C'
UNION ALL SELECT GETDATE()-2, 'C VALUE', 40, 'Dwain.C'
UNION ALL SELECT GETDATE()-2, 'D VALUE', 40, 'Dwain.C'

UPDATE a
SET C2='X VALUE', UserID='MyDoggieJessie'
FROM Main a
WHERE ID IN (2, 3)

DELETE FROM Main 
WHERE ID = 4
-- Add an update to the audit trail on any DELETE
-- to capture the deleting user
UPDATE Audit SET UserID = 'MyCatHobbsy'
WHERE RecordID = 4 AND NewValue IS NULL

SELECT * FROM Main
SELECT * FROM Audit

DROP TRIGGER [dbo].[tr_Audit]
DROP TABLE Main
DROP TABLE Audit