Create   trigger [dbo].[TR_UID_Product]
	on [dbo].[Product] FOR Insert,Update, Delete
	as
begin

SET NOCOUNT ON

--==========
BEGIN TRY
--===========

declare @cnti int, @cntd int, @action char(1), @table_nm varchar(100), @rowstatus char(1);
declare @db_nm varchar(30), @dt datetime, @dataaudit_flg char(1), @auditrecord_flg char(1), @HardDelete_FLG  char(1) , @cntHD int;

set @table_nm = 'Product';		
set @db_nm = db_name();
set @dt = getdate();

set @cnti = -1;
set @cntd = -1;
set @cntHD = 0;

select	@dataaudit_flg = DataAudit_FLG,@auditrecord_flg = RowAudit_FLG, @HardDelete_FLG = HardDelete_FLG 
from dbo.Controltable 
where Table_NM = @table_nm;

-- do not audit at all if flags are N
IF ( @dataaudit_flg <> 'Y' and @auditrecord_flg <> 'Y')
	return;

select @cnti = count(*) from inserted;
select @cntd = count(*) from deleted;

IF (@cnti = @cntd and @cnti > 0) 
	begin
		set @action = 'U';
		
	-- number of deleted records
	select @cntHD = count(*) from inserted where rowstatus = 'H';
	end;

IF (@cnti > @cntd) 
	set @action = 'I';

IF (@cnti < @cntd) 
	set @action = 'D';
	
-- physical deletion for audited tables is not allowed.Only by updating rowstatus = 'H'
IF (@action = 'D')
	begin	
		raiserror ('Hard delete is not allowed! ', 16, 1);
	end;

-- If audit status is hard delete will delete the record
--last update (modification status D - delete) will have the deleted data
IF ( @dataaudit_flg = 'Y' )
	BEGIN	
				-- always add current row for inserts or updates. Old row will be the previous change
				INSERT INTO [AuditDB].[products].[A_Product]
				   (product_id, 
					product_name, 
					rowstatus, 
					row_id, 
					modif_id , 
					createdate,
					actioncd )
			   SELECT 
					product_id, 
					product_name, 
					rowstatus, 
					row_id, 
					modif_id , 
					createdate,
					@action
			  FROM inserted;	
	END;

--  audit record changes based on the table and row id.
IF ( @auditrecord_flg = 'Y' )
	BEGIN
			
		IF (@action = 'U')
		   begin
		   
			INSERT INTO [AuditDB].[dbo].[RowAudit]
			   ([Modif_ID]
			   ,[Database_NM]
			   ,[Table_NM]
			   ,[ROW_ID]
			   ,[ActionCD]
			   ,[OLD_Status]
			   ,[New_Status]
			   ,[CreateDate])
			SELECT 
				i.Modif_ID,
				@db_nm,
				@table_nm,
				i.ROW_ID,
				CASE 
					WHEN i.rowstatus = 'H' THEN 'D' -- hard delete
					ELSE @action 
				END ,			
				d.rowstatus,
				i.rowstatus,
				@dt
			FROM inserted i
					inner join deleted d
						on i.ROW_ID = d.ROW_ID;
						
		   end;

	IF (@action = 'I' )
	begin
	
		INSERT INTO [AuditDB].[dbo].[RowAudit]
           ([Modif_ID]
           ,[Database_NM]
           ,[Table_NM]
           ,[ROW_ID]
           ,[ActionCD]
           ,[OLD_Status]
           ,[New_Status]
           ,[CreateDate])
		SELECT 
			Modif_ID,
			@db_nm,
			@table_nm,
			ROW_ID,
			@action,			
			'I',
			rowstatus,
			@dt,
			ISNULL([UpdateSource], 'App=(' + isnull(APP_NAME()) + ') Proc=(' + isnull(object_name(@@procid), '') + ')')
		FROM inserted;
		
	end;
	
   END;
   
-- add row to modification table for any scenario.
IF (@cntHD = 0)
			begin
				INSERT INTO [AuditDB].[dbo].[Modification]
				   ([Modif_ID]
				   ,[Database_NM]
				   ,[Table_NM]
				   ,[DBAction_CD]
				   ,TotNumModifRows
				   ,[CreateDate]
				   ,[CreateSource]
				   ,CreateUserMachine
				   ,CreateUserName
				   	,Audit_XML )
				SELECT top 1
					Modif_ID,
					@db_nm,
					@table_nm,
					@action,
					@cnti,			
					@dt,
					[UpdateSource] ,
					host_name(),
					suser_sname(),
				    Audit_XML 
				FROM inserted;
			end;
		
-- in the same update can be records with different rowstatus based on case statement
		IF (@cntHD > 0) 
			begin
		
				-- for records that are inserted/updated 
				INSERT INTO [AuditDB].[dbo].[Modification]
				   ([Modif_ID]
				   ,[Database_NM]
				   ,[Table_NM]
				   ,[DBAction_CD]
				   ,TotNumModifRows
				   ,[CreateDate]
				   ,[CreateSource]
				   ,CreateUserMachine
				   ,CreateUserName
				   ,Audit_XML )
				SELECT top 1
					Modif_ID,
					@db_nm,
					@table_nm,					
					@action,
					@cnti,			
					@dt,
					ISNULL([UpdateSource],'App=(' + rtrim(isnull(APP_NAME(), '')) + ') Proc=(' + isnull(object_name(@@procid), '') + ')') ,
					host_name(),
					suser_sname(),
				    Audit_XML 
				FROM inserted
				where rowstatus <> 'H';
				
		-- for records that has to be physically deleted 
				INSERT INTO [AuditDB].[dbo].[Modification]
				   ([Modif_ID]
				   ,[Database_NM]
				   ,[Table_NM]
				   ,[DBAction_CD]
				   ,TotNumModifRows
				   ,[CreateDate]
				   ,[CreateSource]
				   ,CreateUserMachine
				   ,CreateUserName
				   ,Audit_XML )
				SELECT top 1
					Modif_ID,
					@db_nm,
					@table_nm,					
					'D',
					@cnti,			
					@dt,
					ISNULL([UpdateSource],'App=(' + rtrim(isnull(APP_NAME(), '')) + ') Proc=(' + isnull(object_name(@@procid), '') + ')') ,
					host_name(),
					suser_sname(),
				    Audit_XML 
				FROM inserted
				where rowstatus = 'H';	
	
				-- delete actual records
				IF (@HardDelete_FLG = 'Y' )
				   begin
					delete p
					from dbo.Product p
					inner join inserted i
						on i.ROW_ID = p.ROW_ID
					where i.rowstatus = 'H';	
				end;			
			
	end;
	
--========
END TRY
--========
--===========
BEGIN CATCH
--===========

ROLLBACK;


--===========
END CATCH
--===========

  SET NOCOUNT OFF

end