-- Create your table
Create table CT_MASTER_T (
[ID] int not null identity(1,1) primary key clustered,
[Parent_change_number] varchar(10) not null,
[Notes] varchar(4000) not null,
)

go
-- Drop Audit Table, if it exists

if objectproperty(object_id('[dbo].[CT_MASTER_T_AUDIT]'),'IsUserTable') = 1
	begin drop table [dbo].[CT_MASTER_T_AUDIT] end
go

-- Create Audit Table for table [dbo].[CT_MASTER_T]
go
create table [dbo].[CT_MASTER_T_AUDIT] (

--	Start of standard audit columns

--  Audit Table Identity Primary Key
[CT_MASTER_T_AUDIT_ID]		int	not null 
	identity(1,1) 
	constraint [PK_CT_MASTER_T_AUDIT]
	primary key clustered ( [CT_MASTER_T_AUDIT_ID] ) ,
--  Date and time of the transaction
[AUDIT_DATETIME]		datetime	not null 
	constraint [DF__CT_MASTER_T_AUDIT__AUDIT_DATETIME]
	default ( CURRENT_TIMESTAMP ) ,
--  Login name performing the transaction
[AUDIT_LOGIN]		sysname not null 
	constraint [DF__CT_MASTER_T_AUDIT__AUDIT_LOGIN]
	default ( SYSTEM_USER ) ,
--  Host name performing the transaction
[AUDIT_HOST_NAME]		nvarchar (256) not null 
	constraint [DF__CT_MASTER_T_AUDIT__AUDIT_HOST_NAME]
	default ( HOST_NAME() ) ,
--  Application performing the transaction
[AUDIT_APPLICATION_NAME]		nvarchar (128)	not null 
	constraint [DF__CT_MASTER_T_AUDIT__AUDIT_APPLICATION_NAME]
	default ( APP_NAME() ) ,
--  Unique Identifier for transaction
[AUDIT_UNIQUE_ID]		uniqueidentifier	not null, 
--  If source is INSERTED table, then 1, else 0
[AUDIT_INSERTED_INDICATOR]		bit	not null ,
--  If source is DELETED table, then 1, else 0
[AUDIT_DELETED_INDICATOR]		bit	not null ,

--	Start of columns from [dbo].[CT_MASTER_T]

[ID]		int		not null ,
[Parent_change_number]		varchar ( 10 )		not null ,
[Notes]		varchar ( 4000 )		not null ,
)
go

-- Drop Audit Trigger, if it exists

if objectproperty(object_id('[dbo].[TR_AUDIT__CT_MASTER_T]'),'IsTrigger') = 1
	begin drop trigger [dbo].[TR_AUDIT__CT_MASTER_T] end
go
create trigger [dbo].[TR_AUDIT__CT_MASTER_T]
on 
	[dbo].[CT_MASTER_T]
after
	delete, insert, update
as

set nocount on

declare @error			int
declare @rowcount		int
declare @ErrorMessage		varchar(400)
declare @AUDIT_UNIQUE_ID	uniqueidentifier

select	@error			= 0
select  @rowcount		= 0
select	@ErrorMessage		= ''
select	@AUDIT_UNIQUE_ID	= newid()


insert into [dbo].[CT_MASTER_T_AUDIT]
	(
	[AUDIT_UNIQUE_ID],
	[AUDIT_INSERTED_INDICATOR],
	[AUDIT_DELETED_INDICATOR],
	[ID],
	[Parent_change_number],
	[Notes]
	)
select
	[AUDIT_UNIQUE_ID]	= @AUDIT_UNIQUE_ID,
	a.[AUDIT_INSERTED_INDICATOR],
	a.[AUDIT_DELETED_INDICATOR],
	-- Columns from [dbo].[CT_MASTER_T]
	a.[ID],
	a.[Parent_change_number],
	a.[Notes]
from
	(
	select
		[AUDIT_INSERTED_INDICATOR]	= 0 ,
		[AUDIT_DELETED_INDICATOR]	= 1 ,
		a1.*
	from
		deleted a1
	union all
	select
		[AUDIT_INSERTED_INDICATOR]	= 1 ,
		[AUDIT_DELETED_INDICATOR]	= 0 ,
		a2.*
	from
		inserted a2
	) a
order by
	-- Order by Primary Key columns
	a.[ID],
	-- Order by Deleted first and then Inserted
	case
	when a.[AUDIT_DELETED_INDICATOR]	= 1
	then 1
	when a.[AUDIT_INSERTED_INDICATOR]	= 1
	then 2
	else 3
	end

select @error = @@error , @rowcount = @@rowcount

if  @error <> 0
	begin
	select @ErrorMessage = 
	'Error inserting Audit data into [dbo].[CT_MASTER_T_AUDIT]'
	goto Error_Exit
	end

-- Normal exit
return

-- error handler 
Error_Exit:

select @ErrorMessage = isnull(@ErrorMessage+' - ','') +
	'Error in Trigger [dbo].[TR_AUDIT__CT_MASTER_T]' + 
	', SQL Error = ' +
	isnull(convert(varchar(20),@error),'NULL') +
	', Rowcount = ' +
	isnull(convert(varchar(20),@rowcount),'NULL')

raiserror( @ErrorMessage, 16, 1 )

rollback

return
go