Data Auditing Tool (Audit Trigger Generator)

J.Jash
Rate me:
5.00/5 (8 votes)
28 Sep 2012CPOL3 min read
58.7K
2.1K
Data Auditing Trigger Generator
 
GO
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'Sales.[trgSalesPersonInsertAudit]'))
DROP TRIGGER [Sales].[trgSalesPersonInsertAudit]
 
GO
 
/********************************************************************************
*  Name  		: trgSalesPersonInsertAudit
*  Version 		: 1.0
*  Developer 	: Jash
*  Description 	: Insert Audit Log Trigger
*  Tables  		: SalesPerson
*  Views Used 	: None
*  Procedures  	: None
*  Functions 	: None
*********************************************************************************
*                  Modification History
*
* DATE				NAME				NATURE OF CHANGES
*--------------------------------------------------------------------------------
* 06-10-2012		Jash				Creation & Initial Coding
*
*
*
*
*
*********************************************************************************/
 
CREATE TRIGGER [Sales].[trgSalesPersonInsertAudit]
ON [Sales].[SalesPerson]
AFTER  INSERT
AS
 
BEGIN
 
		/*Local variable declartion for new data*/
 
		DECLARE @NEW_SalesPersonID VARCHAR(1000)
		DECLARE @NEW_SalesPersonName VARCHAR(1000)
		DECLARE @NEW_TerritoryID VARCHAR(1000)
		DECLARE @NEW_SalesQuota VARCHAR(1000)
		DECLARE @NEW_Bonus VARCHAR(1000)
		DECLARE @NEW_CommissionPct VARCHAR(1000)
		DECLARE @NEW_ModifiedBy VARCHAR(1000)
		DECLARE @NEW_ModifiedDate VARCHAR(1000)
 
 
 
 
 
 
		/*Local variable declartion for new data Decode*/
		DECLARE @NEW_SalesPersonID_DECODE_VALUE VARCHAR(1000)
		DECLARE @NEW_SalesPersonName_DECODE_VALUE VARCHAR(1000)
		DECLARE @NEW_TerritoryID_DECODE_VALUE VARCHAR(1000)
		DECLARE @NEW_SalesQuota_DECODE_VALUE VARCHAR(1000)
		DECLARE @NEW_Bonus_DECODE_VALUE VARCHAR(1000)
		DECLARE @NEW_CommissionPct_DECODE_VALUE VARCHAR(1000)
		DECLARE @NEW_ModifiedBy_DECODE_VALUE VARCHAR(1000)
		DECLARE @NEW_ModifiedDate_DECODE_VALUE VARCHAR(1000)
 
 
		/*Local variable declartion for new data decode value sql*/
		DECLARE @NEW_SalesPersonID_SQL NVARCHAR(1000)
		DECLARE @NEW_SalesPersonName_SQL NVARCHAR(1000)
		DECLARE @NEW_TerritoryID_SQL NVARCHAR(1000)
		DECLARE @NEW_SalesQuota_SQL NVARCHAR(1000)
		DECLARE @NEW_Bonus_SQL NVARCHAR(1000)
		DECLARE @NEW_CommissionPct_SQL NVARCHAR(1000)
		DECLARE @NEW_ModifiedBy_SQL NVARCHAR(1000)
		DECLARE @NEW_ModifiedDate_SQL NVARCHAR(1000)
 
		/*Local variable declartion*/
 
		DECLARE @AuditLogActionTypeID  INT
		DECLARE @RecordIdentifierName NVARCHAR(100)
		DECLARE @RecordIdentifierValue NVARCHAR(1000)
		DECLARE @ActionBy_UserId VARCHAR(25)
		DECLARE @COMMENTS VARCHAR(600)
		
		IF (SELECT COUNT(*) FROM INSERTED) > 0 
		BEGIN
			/*Get new data from inserted table and assign into local variable*/
			SELECT
				@NEW_SalesPersonID = CAST(SalesPersonID AS VARCHAR(1000)),
				@RecordIdentifierName = 'SalesPersonID',
				@RecordIdentifierValue = CAST(SalesPersonID AS VARCHAR(1000)),
				@NEW_SalesPersonName = CAST(SalesPersonName AS VARCHAR(1000)),
				@NEW_TerritoryID = CAST(TerritoryID AS VARCHAR(1000)),
				@NEW_TerritoryID_SQL = Audit.GenerateDynamicQuery('Sales.SalesPerson','TerritoryID', CAST(TerritoryID AS VARCHAR(1000))),
				@NEW_SalesQuota = CAST(SalesQuota AS VARCHAR(1000)),
				@NEW_Bonus = CAST(Bonus AS VARCHAR(1000)),
				@NEW_CommissionPct = CAST(CommissionPct AS VARCHAR(1000)),
				@NEW_ModifiedBy = CAST(ModifiedBy AS VARCHAR(1000)),
				@NEW_ModifiedDate = CAST(ModifiedDate AS VARCHAR(1000))
			FROM
				INSERTED
 
		END
		
		DECLARE @CURRENT_USER_NAME VARCHAR(MAX) 
		SET @CURRENT_USER_NAME  = suser_sname() 
		DECLARE @USER_NAME VARCHAR(MAX) 
		SET @USER_NAME = Substring(@CURRENT_USER_NAME, patindex('%\%', @CURRENT_USER_NAME) + 1, LEN(@CURRENT_USER_NAME)) 
		SELECT @ActionBy_UserId = @@SPID
		
		IF (@ActionBy_UserId IS NULL)
		BEGIN 
			SET @ActionBy_UserId = '-1'
		END 
		SET  @COMMENTS = ' Column in Sales.SalesPerson table has been Inserted by user id ' +  @ActionBy_UserId + ''
 
		/*Execute the SQL Statement to load decode value for New Data*/
		EXEC sp_executesql @NEW_TerritoryID_SQL , N'@DecodedValue VARCHAR(MAX) OUTPUT', @DecodedValue  = @NEW_TerritoryID_DECODE_VALUE  OUTPUT
		
		DECLARE @AuditSubCategoryID smallint 
		SET @AuditSubCategoryID = NULL
		SELECT @AuditSubCategoryID = AuditSubCategoryID from Audit.AuditSubCategory where AssociatedTable = 'SalesPerson'
 
		IF @AuditSubCategoryID IS NULL
		SET @AuditSubCategoryID = -1
		/*Get the Action Type Id from Audit.AuditLogActionType Table for Insert transaction*/
		SELECT @AuditLogActionTypeID  = ActionTypeId  FROM Audit.AuditLogActionType WHERE ActionTypeDesc = 'Insert'
 
		Insert into Audit.AuditLogData  (TableName, ColumnName, RecordIdentifierName, RecordIdentifierValue, ActionBy_UserId, ActionDateTime, ActionType, Audit_Description, OldValue, OldValue_Decode, NewValue, NewValue_Decode, AuditSubCategoryID)
			SELECT 'SalesPerson', 'SalesPersonID', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'SalesPersonID' + @COMMENTS , null , null, CAST(@NEW_SalesPersonID AS VARCHAR(1000)), COALESCE(('' + @NEW_SalesPersonID_DECODE_VALUE + ''), null), @AuditSubCategoryID UNION ALL
			SELECT 'SalesPerson', 'SalesPersonName', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'SalesPersonName' + @COMMENTS , null , null, CAST(@NEW_SalesPersonName AS VARCHAR(1000)), COALESCE(('' + @NEW_SalesPersonName_DECODE_VALUE + ''), null), @AuditSubCategoryID UNION ALL
			SELECT 'SalesPerson', 'TerritoryID', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'TerritoryID' + @COMMENTS , null , null, CAST(@NEW_TerritoryID AS VARCHAR(1000)), COALESCE(('' + @NEW_TerritoryID_DECODE_VALUE + ''), null), @AuditSubCategoryID UNION ALL
			SELECT 'SalesPerson', 'SalesQuota', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'SalesQuota' + @COMMENTS , null , null, CAST(@NEW_SalesQuota AS VARCHAR(1000)), COALESCE(('' + @NEW_SalesQuota_DECODE_VALUE + ''), null), @AuditSubCategoryID UNION ALL
			SELECT 'SalesPerson', 'Bonus', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'Bonus' + @COMMENTS , null , null, CAST(@NEW_Bonus AS VARCHAR(1000)), COALESCE(('' + @NEW_Bonus_DECODE_VALUE + ''), null), @AuditSubCategoryID UNION ALL
			SELECT 'SalesPerson', 'CommissionPct', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'CommissionPct' + @COMMENTS , null , null, CAST(@NEW_CommissionPct AS VARCHAR(1000)), COALESCE(('' + @NEW_CommissionPct_DECODE_VALUE + ''), null), @AuditSubCategoryID UNION ALL
			SELECT 'SalesPerson', 'ModifiedBy', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'ModifiedBy' + @COMMENTS , null , null, CAST(@NEW_ModifiedBy AS VARCHAR(1000)), COALESCE(('' + @NEW_ModifiedBy_DECODE_VALUE + ''), null), @AuditSubCategoryID UNION ALL
			SELECT 'SalesPerson', 'ModifiedDate', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'ModifiedDate' + @COMMENTS , null , null, CAST(@NEW_ModifiedDate AS VARCHAR(1000)), COALESCE(('' + @NEW_ModifiedDate_DECODE_VALUE + ''), null), @AuditSubCategoryID
 
END
 
 
GO
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'Sales.[trgSalesTerritoryInsertAudit]'))
DROP TRIGGER [Sales].[trgSalesTerritoryInsertAudit]
 
GO
 
/********************************************************************************
*  Name  		: trgSalesTerritoryInsertAudit
*  Version 		: 1.0
*  Developer 	: Jash
*  Description 	: Insert Audit Log Trigger
*  Tables  		: SalesTerritory
*  Views Used 	: None
*  Procedures  	: None
*  Functions 	: None
*********************************************************************************
*                  Modification History
*
* DATE				NAME				NATURE OF CHANGES
*--------------------------------------------------------------------------------
* 06-10-2012		Jash				Creation & Initial Coding
*
*
*
*
*
*********************************************************************************/
 
CREATE TRIGGER [Sales].[trgSalesTerritoryInsertAudit]
ON [Sales].[SalesTerritory]
AFTER  INSERT
AS
 
BEGIN
 
		/*Local variable declartion for new data*/
 
		DECLARE @NEW_TerritoryID VARCHAR(1000)
		DECLARE @NEW_TerritoryName VARCHAR(1000)
		DECLARE @NEW_CountryRegionCode VARCHAR(1000)
		DECLARE @NEW_ModifiedBy VARCHAR(1000)
		DECLARE @NEW_ModifiedDate VARCHAR(1000)
 
 
 
 
 
 
		/*Local variable declartion for new data Decode*/
		DECLARE @NEW_TerritoryID_DECODE_VALUE VARCHAR(1000)
		DECLARE @NEW_TerritoryName_DECODE_VALUE VARCHAR(1000)
		DECLARE @NEW_CountryRegionCode_DECODE_VALUE VARCHAR(1000)
		DECLARE @NEW_ModifiedBy_DECODE_VALUE VARCHAR(1000)
		DECLARE @NEW_ModifiedDate_DECODE_VALUE VARCHAR(1000)
 
 
		/*Local variable declartion for new data decode value sql*/
		DECLARE @NEW_TerritoryID_SQL NVARCHAR(1000)
		DECLARE @NEW_TerritoryName_SQL NVARCHAR(1000)
		DECLARE @NEW_CountryRegionCode_SQL NVARCHAR(1000)
		DECLARE @NEW_ModifiedBy_SQL NVARCHAR(1000)
		DECLARE @NEW_ModifiedDate_SQL NVARCHAR(1000)
 
		/*Local variable declartion*/
 
		DECLARE @AuditLogActionTypeID  INT
		DECLARE @RecordIdentifierName NVARCHAR(100)
		DECLARE @RecordIdentifierValue NVARCHAR(1000)
		DECLARE @ActionBy_UserId VARCHAR(25)
		DECLARE @COMMENTS VARCHAR(600)
		
		IF (SELECT COUNT(*) FROM INSERTED) > 0 
		BEGIN
			/*Get new data from inserted table and assign into local variable*/
			SELECT
				@NEW_TerritoryID = CAST(TerritoryID AS VARCHAR(1000)),
				@NEW_TerritoryID_SQL = Audit.GenerateDynamicQuery('Sales.SalesTerritory','TerritoryID', CAST(TerritoryID AS VARCHAR(1000))),
				@RecordIdentifierName = 'TerritoryID',
				@RecordIdentifierValue = CAST(TerritoryID AS VARCHAR(1000)),
				@NEW_TerritoryName = CAST(TerritoryName AS VARCHAR(1000)),
				@NEW_CountryRegionCode = CAST(CountryRegionCode AS VARCHAR(1000)),
				@NEW_CountryRegionCode_SQL = Audit.GenerateDynamicQuery('Sales.SalesTerritory','CountryRegionCode', CAST(CountryRegionCode AS VARCHAR(1000))),
				@NEW_ModifiedBy = CAST(ModifiedBy AS VARCHAR(1000)),
				@NEW_ModifiedDate = CAST(ModifiedDate AS VARCHAR(1000))
			FROM
				INSERTED
 
		END
		
		DECLARE @CURRENT_USER_NAME VARCHAR(MAX) 
		SET @CURRENT_USER_NAME  = suser_sname() 
		DECLARE @USER_NAME VARCHAR(MAX) 
		SET @USER_NAME = Substring(@CURRENT_USER_NAME, patindex('%\%', @CURRENT_USER_NAME) + 1, LEN(@CURRENT_USER_NAME)) 
		SELECT @ActionBy_UserId = @@SPID
		
		IF (@ActionBy_UserId IS NULL)
		BEGIN 
			SET @ActionBy_UserId = '-1'
		END 
		SET  @COMMENTS = ' Column in Sales.SalesTerritory table has been Inserted by user id ' +  @ActionBy_UserId + ''
 
		/*Execute the SQL Statement to load decode value for New Data*/
		EXEC sp_executesql @NEW_TerritoryID_SQL , N'@DecodedValue VARCHAR(MAX) OUTPUT', @DecodedValue  = @NEW_TerritoryID_DECODE_VALUE  OUTPUT
		EXEC sp_executesql @NEW_CountryRegionCode_SQL , N'@DecodedValue VARCHAR(MAX) OUTPUT', @DecodedValue  = @NEW_CountryRegionCode_DECODE_VALUE  OUTPUT
		
		DECLARE @AuditSubCategoryID smallint 
		SET @AuditSubCategoryID = NULL
		SELECT @AuditSubCategoryID = AuditSubCategoryID from Audit.AuditSubCategory where AssociatedTable = 'SalesTerritory'
 
		IF @AuditSubCategoryID IS NULL
		SET @AuditSubCategoryID = -1
		/*Get the Action Type Id from Audit.AuditLogActionType Table for Insert transaction*/
		SELECT @AuditLogActionTypeID  = ActionTypeId  FROM Audit.AuditLogActionType WHERE ActionTypeDesc = 'Insert'
 
		Insert into Audit.AuditLogData  (TableName, ColumnName, RecordIdentifierName, RecordIdentifierValue, ActionBy_UserId, ActionDateTime, ActionType, Audit_Description, OldValue, OldValue_Decode, NewValue, NewValue_Decode, AuditSubCategoryID)
			SELECT 'SalesTerritory', 'TerritoryID', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'TerritoryID' + @COMMENTS , null , null, CAST(@NEW_TerritoryID AS VARCHAR(1000)), COALESCE(('' + @NEW_TerritoryID_DECODE_VALUE + ''), null), @AuditSubCategoryID UNION ALL
			SELECT 'SalesTerritory', 'TerritoryName', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'TerritoryName' + @COMMENTS , null , null, CAST(@NEW_TerritoryName AS VARCHAR(1000)), COALESCE(('' + @NEW_TerritoryName_DECODE_VALUE + ''), null), @AuditSubCategoryID UNION ALL
			SELECT 'SalesTerritory', 'CountryRegionCode', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'CountryRegionCode' + @COMMENTS , null , null, CAST(@NEW_CountryRegionCode AS VARCHAR(1000)), COALESCE(('' + @NEW_CountryRegionCode_DECODE_VALUE + ''), null), @AuditSubCategoryID UNION ALL
			SELECT 'SalesTerritory', 'ModifiedBy', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'ModifiedBy' + @COMMENTS , null , null, CAST(@NEW_ModifiedBy AS VARCHAR(1000)), COALESCE(('' + @NEW_ModifiedBy_DECODE_VALUE + ''), null), @AuditSubCategoryID UNION ALL
			SELECT 'SalesTerritory', 'ModifiedDate', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'ModifiedDate' + @COMMENTS , null , null, CAST(@NEW_ModifiedDate AS VARCHAR(1000)), COALESCE(('' + @NEW_ModifiedDate_DECODE_VALUE + ''), null), @AuditSubCategoryID
 
END
 
 
GO
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'Sales.[trgCountryRegionInsertAudit]'))
DROP TRIGGER [Sales].[trgCountryRegionInsertAudit]
 
GO
 
/********************************************************************************
*  Name  		: trgCountryRegionInsertAudit
*  Version 		: 1.0
*  Developer 	: Jash
*  Description 	: Insert Audit Log Trigger
*  Tables  		: CountryRegion
*  Views Used 	: None
*  Procedures  	: None
*  Functions 	: None
*********************************************************************************
*                  Modification History
*
* DATE				NAME				NATURE OF CHANGES
*--------------------------------------------------------------------------------
* 06-10-2012		Jash				Creation & Initial Coding
*
*
*
*
*
*********************************************************************************/
 
CREATE TRIGGER [Sales].[trgCountryRegionInsertAudit]
ON [Sales].[CountryRegion]
AFTER  INSERT
AS
 
BEGIN
 
		/*Local variable declartion for new data*/
 
		DECLARE @NEW_CountryRegionCode VARCHAR(1000)
		DECLARE @NEW_CountryRegionName VARCHAR(1000)
		DECLARE @NEW_ModifiedBy VARCHAR(1000)
		DECLARE @NEW_ModifiedDate VARCHAR(1000)
 
 
 
 
 
 
		/*Local variable declartion for new data Decode*/
		DECLARE @NEW_CountryRegionCode_DECODE_VALUE VARCHAR(1000)
		DECLARE @NEW_CountryRegionName_DECODE_VALUE VARCHAR(1000)
		DECLARE @NEW_ModifiedBy_DECODE_VALUE VARCHAR(1000)
		DECLARE @NEW_ModifiedDate_DECODE_VALUE VARCHAR(1000)
 
 
		/*Local variable declartion for new data decode value sql*/
		DECLARE @NEW_CountryRegionCode_SQL NVARCHAR(1000)
		DECLARE @NEW_CountryRegionName_SQL NVARCHAR(1000)
		DECLARE @NEW_ModifiedBy_SQL NVARCHAR(1000)
		DECLARE @NEW_ModifiedDate_SQL NVARCHAR(1000)
 
		/*Local variable declartion*/
 
		DECLARE @AuditLogActionTypeID  INT
		DECLARE @RecordIdentifierName NVARCHAR(100)
		DECLARE @RecordIdentifierValue NVARCHAR(1000)
		DECLARE @ActionBy_UserId VARCHAR(25)
		DECLARE @COMMENTS VARCHAR(600)
		
		IF (SELECT COUNT(*) FROM INSERTED) > 0 
		BEGIN
			/*Get new data from inserted table and assign into local variable*/
			SELECT
				@NEW_CountryRegionCode = CAST(CountryRegionCode AS VARCHAR(1000)),
				@NEW_CountryRegionCode_SQL = Audit.GenerateDynamicQuery('Sales.CountryRegion','CountryRegionCode', CAST(CountryRegionCode AS VARCHAR(1000))),
				@RecordIdentifierName = 'CountryRegionCode',
				@RecordIdentifierValue = CAST(CountryRegionCode AS VARCHAR(1000)),
				@NEW_CountryRegionName = CAST(CountryRegionName AS VARCHAR(1000)),
				@NEW_ModifiedBy = CAST(ModifiedBy AS VARCHAR(1000)),
				@NEW_ModifiedDate = CAST(ModifiedDate AS VARCHAR(1000))
			FROM
				INSERTED
 
		END
		
		DECLARE @CURRENT_USER_NAME VARCHAR(MAX) 
		SET @CURRENT_USER_NAME  = suser_sname() 
		DECLARE @USER_NAME VARCHAR(MAX) 
		SET @USER_NAME = Substring(@CURRENT_USER_NAME, patindex('%\%', @CURRENT_USER_NAME) + 1, LEN(@CURRENT_USER_NAME)) 
		SELECT @ActionBy_UserId = @@SPID
		
		IF (@ActionBy_UserId IS NULL)
		BEGIN 
			SET @ActionBy_UserId = '-1'
		END 
		SET  @COMMENTS = ' Column in Sales.CountryRegion table has been Inserted by user id ' +  @ActionBy_UserId + ''
 
		/*Execute the SQL Statement to load decode value for New Data*/
		EXEC sp_executesql @NEW_CountryRegionCode_SQL , N'@DecodedValue VARCHAR(MAX) OUTPUT', @DecodedValue  = @NEW_CountryRegionCode_DECODE_VALUE  OUTPUT
		
		DECLARE @AuditSubCategoryID smallint 
		SET @AuditSubCategoryID = NULL
		SELECT @AuditSubCategoryID = AuditSubCategoryID from Audit.AuditSubCategory where AssociatedTable = 'CountryRegion'
 
		IF @AuditSubCategoryID IS NULL
		SET @AuditSubCategoryID = -1
		/*Get the Action Type Id from Audit.AuditLogActionType Table for Insert transaction*/
		SELECT @AuditLogActionTypeID  = ActionTypeId  FROM Audit.AuditLogActionType WHERE ActionTypeDesc = 'Insert'
 
		Insert into Audit.AuditLogData  (TableName, ColumnName, RecordIdentifierName, RecordIdentifierValue, ActionBy_UserId, ActionDateTime, ActionType, Audit_Description, OldValue, OldValue_Decode, NewValue, NewValue_Decode, AuditSubCategoryID)
			SELECT 'CountryRegion', 'CountryRegionCode', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'CountryRegionCode' + @COMMENTS , null , null, CAST(@NEW_CountryRegionCode AS VARCHAR(1000)), COALESCE(('' + @NEW_CountryRegionCode_DECODE_VALUE + ''), null), @AuditSubCategoryID UNION ALL
			SELECT 'CountryRegion', 'CountryRegionName', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'CountryRegionName' + @COMMENTS , null , null, CAST(@NEW_CountryRegionName AS VARCHAR(1000)), COALESCE(('' + @NEW_CountryRegionName_DECODE_VALUE + ''), null), @AuditSubCategoryID UNION ALL
			SELECT 'CountryRegion', 'ModifiedBy', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'ModifiedBy' + @COMMENTS , null , null, CAST(@NEW_ModifiedBy AS VARCHAR(1000)), COALESCE(('' + @NEW_ModifiedBy_DECODE_VALUE + ''), null), @AuditSubCategoryID UNION ALL
			SELECT 'CountryRegion', 'ModifiedDate', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'ModifiedDate' + @COMMENTS , null , null, CAST(@NEW_ModifiedDate AS VARCHAR(1000)), COALESCE(('' + @NEW_ModifiedDate_DECODE_VALUE + ''), null), @AuditSubCategoryID
 
END
 
GO
 
GO
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'Sales.[trgSalesPersonUpdateAudit]'))
DROP TRIGGER [Sales].[trgSalesPersonUpdateAudit]
 
GO
 
/********************************************************************************
*  Name  		: trgSalesPersonUpdateAudit
*  Version 		: 1.0
*  Developer 	: Jash
*  Description 	: Update Audit Log Trigger
*  Tables  		: SalesPerson
*  Views Used 	: None
*  Procedures  	: None
*  Functions 	: None
*********************************************************************************
*                  Modification History
*
* DATE				NAME				NATURE OF CHANGES
*--------------------------------------------------------------------------------
* 06-10-2012		Jash				Creation & Initial Coding
*
*
*
*
*
*********************************************************************************/
 
CREATE TRIGGER [Sales].[trgSalesPersonUpdateAudit]
ON [Sales].[SalesPerson]
AFTER  UPDATE
AS
 
BEGIN
 
		/*Local variable declartion for new data*/
 
		DECLARE @NEW_SalesPersonID VARCHAR(1000)
		DECLARE @NEW_SalesPersonName VARCHAR(1000)
		DECLARE @NEW_TerritoryID VARCHAR(1000)
		DECLARE @NEW_SalesQuota VARCHAR(1000)
		DECLARE @NEW_Bonus VARCHAR(1000)
		DECLARE @NEW_CommissionPct VARCHAR(1000)
		DECLARE @NEW_ModifiedBy VARCHAR(1000)
		DECLARE @NEW_ModifiedDate VARCHAR(1000)
 
 
 
 
 
 
		/*Local variable declartion for new data Decode*/
		DECLARE @NEW_SalesPersonID_DECODE_VALUE VARCHAR(1000)
		DECLARE @NEW_SalesPersonName_DECODE_VALUE VARCHAR(1000)
		DECLARE @NEW_TerritoryID_DECODE_VALUE VARCHAR(1000)
		DECLARE @NEW_SalesQuota_DECODE_VALUE VARCHAR(1000)
		DECLARE @NEW_Bonus_DECODE_VALUE VARCHAR(1000)
		DECLARE @NEW_CommissionPct_DECODE_VALUE VARCHAR(1000)
		DECLARE @NEW_ModifiedBy_DECODE_VALUE VARCHAR(1000)
		DECLARE @NEW_ModifiedDate_DECODE_VALUE VARCHAR(1000)
 
		/*Local variable declartion for old  data*/
 
		DECLARE @OLD_SalesPersonID VARCHAR(1000)
		DECLARE @OLD_SalesPersonName VARCHAR(1000)
		DECLARE @OLD_TerritoryID VARCHAR(1000)
		DECLARE @OLD_SalesQuota VARCHAR(1000)
		DECLARE @OLD_Bonus VARCHAR(1000)
		DECLARE @OLD_CommissionPct VARCHAR(1000)
		DECLARE @OLD_ModifiedBy VARCHAR(1000)
		DECLARE @OLD_ModifiedDate VARCHAR(1000)
 
 
 
 
 
		/*Local variable declartion for old data Decode*/
		DECLARE @OLD_SalesPersonID_DECODE_VALUE VARCHAR(1000)
		DECLARE @OLD_SalesPersonName_DECODE_VALUE VARCHAR(1000)
		DECLARE @OLD_TerritoryID_DECODE_VALUE VARCHAR(1000)
		DECLARE @OLD_SalesQuota_DECODE_VALUE VARCHAR(1000)
		DECLARE @OLD_Bonus_DECODE_VALUE VARCHAR(1000)
		DECLARE @OLD_CommissionPct_DECODE_VALUE VARCHAR(1000)
		DECLARE @OLD_ModifiedBy_DECODE_VALUE VARCHAR(1000)
		DECLARE @OLD_ModifiedDate_DECODE_VALUE VARCHAR(1000)
 
		/*Local variable declartion for new data decode value sql*/
		DECLARE @NEW_SalesPersonID_SQL NVARCHAR(1000)
		DECLARE @NEW_SalesPersonName_SQL NVARCHAR(1000)
		DECLARE @NEW_TerritoryID_SQL NVARCHAR(1000)
		DECLARE @NEW_SalesQuota_SQL NVARCHAR(1000)
		DECLARE @NEW_Bonus_SQL NVARCHAR(1000)
		DECLARE @NEW_CommissionPct_SQL NVARCHAR(1000)
		DECLARE @NEW_ModifiedBy_SQL NVARCHAR(1000)
		DECLARE @NEW_ModifiedDate_SQL NVARCHAR(1000)
 
		/*Local variable declartion for new data decode value sql*/
		DECLARE @OLD_SalesPersonID_SQL NVARCHAR(1000)
		DECLARE @OLD_SalesPersonName_SQL NVARCHAR(1000)
		DECLARE @OLD_TerritoryID_SQL NVARCHAR(1000)
		DECLARE @OLD_SalesQuota_SQL NVARCHAR(1000)
		DECLARE @OLD_Bonus_SQL NVARCHAR(1000)
		DECLARE @OLD_CommissionPct_SQL NVARCHAR(1000)
		DECLARE @OLD_ModifiedBy_SQL NVARCHAR(1000)
		DECLARE @OLD_ModifiedDate_SQL NVARCHAR(1000)
		/*Local variable declartion*/
 
		DECLARE @AuditLogActionTypeID  INT
		DECLARE @RecordIdentifierName NVARCHAR(100)
		DECLARE @RecordIdentifierValue NVARCHAR(1000)
		DECLARE @ActionBy_UserId VARCHAR(25)
		DECLARE @COMMENTS VARCHAR(600)
		
		IF (SELECT COUNT(*) FROM INSERTED) > 0 
		BEGIN
			/*Get new data from inserted table and assign into local variable*/
			SELECT
				@NEW_SalesPersonID = CAST(SalesPersonID AS VARCHAR(1000)),
				@NEW_SalesPersonName = CAST(SalesPersonName AS VARCHAR(1000)),
				@NEW_TerritoryID = CAST(TerritoryID AS VARCHAR(1000)),
				@NEW_TerritoryID_SQL = Audit.GenerateDynamicQuery('Sales.SalesPerson','TerritoryID', CAST(TerritoryID AS VARCHAR(1000))),
				@NEW_SalesQuota = CAST(SalesQuota AS VARCHAR(1000)),
				@NEW_Bonus = CAST(Bonus AS VARCHAR(1000)),
				@NEW_CommissionPct = CAST(CommissionPct AS VARCHAR(1000)),
				@NEW_ModifiedBy = CAST(ModifiedBy AS VARCHAR(1000)),
				@NEW_ModifiedDate = CAST(ModifiedDate AS VARCHAR(1000))
			FROM
				INSERTED
 
		END
		
		DECLARE @CURRENT_USER_NAME VARCHAR(MAX) 
		SET @CURRENT_USER_NAME  = suser_sname() 
		DECLARE @USER_NAME VARCHAR(MAX) 
		SET @USER_NAME = Substring(@CURRENT_USER_NAME, patindex('%\%', @CURRENT_USER_NAME) + 1, LEN(@CURRENT_USER_NAME)) 
		SELECT @ActionBy_UserId = @@SPID
		
		IF (@ActionBy_UserId IS NULL)
		BEGIN 
			SET @ActionBy_UserId = '-1'
		END 
		SET  @COMMENTS = ' Column in Sales.SalesPerson table has been updated by user id ' +  @ActionBy_UserId + ''
 
		/*If record exist in Deleted table means this is Update transaction*/
		IF (SELECT COUNT(*) FROM DELETED) > 0
		BEGIN
			/*Get existing data from deleted table and assign into local variable*/
			SELECT
				@OLD_SalesPersonID = CAST(SalesPersonID AS VARCHAR(1000)),
				@RecordIdentifierName = 'SalesPersonID',
				@RecordIdentifierValue = CAST(SalesPersonID AS VARCHAR(1000)),
				@OLD_SalesPersonName = CAST(SalesPersonName AS VARCHAR(1000)),
				@OLD_TerritoryID = CAST(TerritoryID AS VARCHAR(1000)),
				@OLD_TerritoryID_SQL = Audit.GenerateDynamicQuery('Sales.SalesPerson','TerritoryID', CAST(TerritoryID AS VARCHAR(1000))),
				@OLD_SalesQuota = CAST(SalesQuota AS VARCHAR(1000)),
				@OLD_Bonus = CAST(Bonus AS VARCHAR(1000)),
				@OLD_CommissionPct = CAST(CommissionPct AS VARCHAR(1000)),
				@OLD_ModifiedBy = CAST(ModifiedBy AS VARCHAR(1000)),
				@OLD_ModifiedDate = CAST(ModifiedDate AS VARCHAR(1000))
			FROM
				DELETED
 
		END
		/*Execute the SQL Statement to load decode value for Old Data*/
		EXEC sp_executesql @OLD_TerritoryID_SQL , N'@DecodedValue VARCHAR(MAX) OUTPUT', @DecodedValue  = @OLD_TerritoryID_DECODE_VALUE  OUTPUT
		
		/*Execute the SQL Statement to load decode value for New Data*/
		EXEC sp_executesql @NEW_TerritoryID_SQL , N'@DecodedValue VARCHAR(MAX) OUTPUT', @DecodedValue  = @NEW_TerritoryID_DECODE_VALUE  OUTPUT
		
		DECLARE @AuditSubCategoryID smallint 
		SET @AuditSubCategoryID = NULL
		SELECT @AuditSubCategoryID = AuditSubCategoryID from Audit.AuditSubCategory where AssociatedTable = 'SalesPerson'
 
		IF @AuditSubCategoryID IS NULL
		SET @AuditSubCategoryID = -1
		/*Get the Action Type Id from Audit.AuditLogActionType Table for Update transaction*/
		SELECT @AuditLogActionTypeID  = ActionTypeId  FROM Audit.AuditLogActionType WHERE ActionTypeDesc = 'Update'
 
		Insert into Audit.AuditLogData  (TableName, ColumnName, RecordIdentifierName, RecordIdentifierValue, ActionBy_UserId, ActionDateTime, ActionType, Audit_Description, OldValue, OldValue_Decode, NewValue, NewValue_Decode, AuditSubCategoryID)
			SELECT 'SalesPerson', 'SalesPersonID', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'SalesPersonID' + @COMMENTS , @OLD_SalesPersonID , COALESCE(('' + @OLD_SalesPersonID_DECODE_VALUE + ''), null), CAST(@NEW_SalesPersonID AS VARCHAR(1000)), COALESCE(('' + @NEW_SalesPersonID_DECODE_VALUE + ''), null), @AuditSubCategoryID UNION ALL
			SELECT 'SalesPerson', 'SalesPersonName', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'SalesPersonName' + @COMMENTS , @OLD_SalesPersonName , COALESCE(('' + @OLD_SalesPersonName_DECODE_VALUE + ''), null), CAST(@NEW_SalesPersonName AS VARCHAR(1000)), COALESCE(('' + @NEW_SalesPersonName_DECODE_VALUE + ''), null), @AuditSubCategoryID UNION ALL
			SELECT 'SalesPerson', 'TerritoryID', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'TerritoryID' + @COMMENTS , @OLD_TerritoryID , COALESCE(('' + @OLD_TerritoryID_DECODE_VALUE + ''), null), CAST(@NEW_TerritoryID AS VARCHAR(1000)), COALESCE(('' + @NEW_TerritoryID_DECODE_VALUE + ''), null), @AuditSubCategoryID UNION ALL
			SELECT 'SalesPerson', 'SalesQuota', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'SalesQuota' + @COMMENTS , @OLD_SalesQuota , COALESCE(('' + @OLD_SalesQuota_DECODE_VALUE + ''), null), CAST(@NEW_SalesQuota AS VARCHAR(1000)), COALESCE(('' + @NEW_SalesQuota_DECODE_VALUE + ''), null), @AuditSubCategoryID UNION ALL
			SELECT 'SalesPerson', 'Bonus', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'Bonus' + @COMMENTS , @OLD_Bonus , COALESCE(('' + @OLD_Bonus_DECODE_VALUE + ''), null), CAST(@NEW_Bonus AS VARCHAR(1000)), COALESCE(('' + @NEW_Bonus_DECODE_VALUE + ''), null), @AuditSubCategoryID UNION ALL
			SELECT 'SalesPerson', 'CommissionPct', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'CommissionPct' + @COMMENTS , @OLD_CommissionPct , COALESCE(('' + @OLD_CommissionPct_DECODE_VALUE + ''), null), CAST(@NEW_CommissionPct AS VARCHAR(1000)), COALESCE(('' + @NEW_CommissionPct_DECODE_VALUE + ''), null), @AuditSubCategoryID UNION ALL
			SELECT 'SalesPerson', 'ModifiedBy', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'ModifiedBy' + @COMMENTS , @OLD_ModifiedBy , COALESCE(('' + @OLD_ModifiedBy_DECODE_VALUE + ''), null), CAST(@NEW_ModifiedBy AS VARCHAR(1000)), COALESCE(('' + @NEW_ModifiedBy_DECODE_VALUE + ''), null), @AuditSubCategoryID UNION ALL
			SELECT 'SalesPerson', 'ModifiedDate', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'ModifiedDate' + @COMMENTS , @OLD_ModifiedDate , COALESCE(('' + @OLD_ModifiedDate_DECODE_VALUE + ''), null), CAST(@NEW_ModifiedDate AS VARCHAR(1000)), COALESCE(('' + @NEW_ModifiedDate_DECODE_VALUE + ''), null), @AuditSubCategoryID
 
END
 
 
GO
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'Sales.[trgSalesTerritoryUpdateAudit]'))
DROP TRIGGER [Sales].[trgSalesTerritoryUpdateAudit]
 
GO
 
/********************************************************************************
*  Name  		: trgSalesTerritoryUpdateAudit
*  Version 		: 1.0
*  Developer 	: Jash
*  Description 	: Update Audit Log Trigger
*  Tables  		: SalesTerritory
*  Views Used 	: None
*  Procedures  	: None
*  Functions 	: None
*********************************************************************************
*                  Modification History
*
* DATE				NAME				NATURE OF CHANGES
*--------------------------------------------------------------------------------
* 06-10-2012		Jash				Creation & Initial Coding
*
*
*
*
*
*********************************************************************************/
 
CREATE TRIGGER [Sales].[trgSalesTerritoryUpdateAudit]
ON [Sales].[SalesTerritory]
AFTER  UPDATE
AS
 
BEGIN
 
		/*Local variable declartion for new data*/
 
		DECLARE @NEW_TerritoryID VARCHAR(1000)
		DECLARE @NEW_TerritoryName VARCHAR(1000)
		DECLARE @NEW_CountryRegionCode VARCHAR(1000)
		DECLARE @NEW_ModifiedBy VARCHAR(1000)
		DECLARE @NEW_ModifiedDate VARCHAR(1000)
 
 
 
 
 
 
		/*Local variable declartion for new data Decode*/
		DECLARE @NEW_TerritoryID_DECODE_VALUE VARCHAR(1000)
		DECLARE @NEW_TerritoryName_DECODE_VALUE VARCHAR(1000)
		DECLARE @NEW_CountryRegionCode_DECODE_VALUE VARCHAR(1000)
		DECLARE @NEW_ModifiedBy_DECODE_VALUE VARCHAR(1000)
		DECLARE @NEW_ModifiedDate_DECODE_VALUE VARCHAR(1000)
 
		/*Local variable declartion for old  data*/
 
		DECLARE @OLD_TerritoryID VARCHAR(1000)
		DECLARE @OLD_TerritoryName VARCHAR(1000)
		DECLARE @OLD_CountryRegionCode VARCHAR(1000)
		DECLARE @OLD_ModifiedBy VARCHAR(1000)
		DECLARE @OLD_ModifiedDate VARCHAR(1000)
 
 
 
 
 
		/*Local variable declartion for old data Decode*/
		DECLARE @OLD_TerritoryID_DECODE_VALUE VARCHAR(1000)
		DECLARE @OLD_TerritoryName_DECODE_VALUE VARCHAR(1000)
		DECLARE @OLD_CountryRegionCode_DECODE_VALUE VARCHAR(1000)
		DECLARE @OLD_ModifiedBy_DECODE_VALUE VARCHAR(1000)
		DECLARE @OLD_ModifiedDate_DECODE_VALUE VARCHAR(1000)
 
		/*Local variable declartion for new data decode value sql*/
		DECLARE @NEW_TerritoryID_SQL NVARCHAR(1000)
		DECLARE @NEW_TerritoryName_SQL NVARCHAR(1000)
		DECLARE @NEW_CountryRegionCode_SQL NVARCHAR(1000)
		DECLARE @NEW_ModifiedBy_SQL NVARCHAR(1000)
		DECLARE @NEW_ModifiedDate_SQL NVARCHAR(1000)
 
		/*Local variable declartion for new data decode value sql*/
		DECLARE @OLD_TerritoryID_SQL NVARCHAR(1000)
		DECLARE @OLD_TerritoryName_SQL NVARCHAR(1000)
		DECLARE @OLD_CountryRegionCode_SQL NVARCHAR(1000)
		DECLARE @OLD_ModifiedBy_SQL NVARCHAR(1000)
		DECLARE @OLD_ModifiedDate_SQL NVARCHAR(1000)
		/*Local variable declartion*/
 
		DECLARE @AuditLogActionTypeID  INT
		DECLARE @RecordIdentifierName NVARCHAR(100)
		DECLARE @RecordIdentifierValue NVARCHAR(1000)
		DECLARE @ActionBy_UserId VARCHAR(25)
		DECLARE @COMMENTS VARCHAR(600)
		
		IF (SELECT COUNT(*) FROM INSERTED) > 0 
		BEGIN
			/*Get new data from inserted table and assign into local variable*/
			SELECT
				@NEW_TerritoryID = CAST(TerritoryID AS VARCHAR(1000)),
				@NEW_TerritoryID_SQL = Audit.GenerateDynamicQuery('Sales.SalesTerritory','TerritoryID', CAST(TerritoryID AS VARCHAR(1000))),
				@NEW_TerritoryName = CAST(TerritoryName AS VARCHAR(1000)),
				@NEW_CountryRegionCode = CAST(CountryRegionCode AS VARCHAR(1000)),
				@NEW_CountryRegionCode_SQL = Audit.GenerateDynamicQuery('Sales.SalesTerritory','CountryRegionCode', CAST(CountryRegionCode AS VARCHAR(1000))),
				@NEW_ModifiedBy = CAST(ModifiedBy AS VARCHAR(1000)),
				@NEW_ModifiedDate = CAST(ModifiedDate AS VARCHAR(1000))
			FROM
				INSERTED
 
		END
		
		DECLARE @CURRENT_USER_NAME VARCHAR(MAX) 
		SET @CURRENT_USER_NAME  = suser_sname() 
		DECLARE @USER_NAME VARCHAR(MAX) 
		SET @USER_NAME = Substring(@CURRENT_USER_NAME, patindex('%\%', @CURRENT_USER_NAME) + 1, LEN(@CURRENT_USER_NAME)) 
		SELECT @ActionBy_UserId = @@SPID
		
		IF (@ActionBy_UserId IS NULL)
		BEGIN 
			SET @ActionBy_UserId = '-1'
		END 
		SET  @COMMENTS = ' Column in Sales.SalesTerritory table has been updated by user id ' +  @ActionBy_UserId + ''
 
		/*If record exist in Deleted table means this is Update transaction*/
		IF (SELECT COUNT(*) FROM DELETED) > 0
		BEGIN
			/*Get existing data from deleted table and assign into local variable*/
			SELECT
				@OLD_TerritoryID = CAST(TerritoryID AS VARCHAR(1000)),
				@OLD_TerritoryID_SQL = Audit.GenerateDynamicQuery('Sales.SalesTerritory','TerritoryID', CAST(TerritoryID AS VARCHAR(1000))),
				@RecordIdentifierName = 'TerritoryID',
				@RecordIdentifierValue = CAST(TerritoryID AS VARCHAR(1000)),
				@OLD_TerritoryName = CAST(TerritoryName AS VARCHAR(1000)),
				@OLD_CountryRegionCode = CAST(CountryRegionCode AS VARCHAR(1000)),
				@OLD_CountryRegionCode_SQL = Audit.GenerateDynamicQuery('Sales.SalesTerritory','CountryRegionCode', CAST(CountryRegionCode AS VARCHAR(1000))),
				@OLD_ModifiedBy = CAST(ModifiedBy AS VARCHAR(1000)),
				@OLD_ModifiedDate = CAST(ModifiedDate AS VARCHAR(1000))
			FROM
				DELETED
 
		END
		/*Execute the SQL Statement to load decode value for Old Data*/
		EXEC sp_executesql @OLD_TerritoryID_SQL , N'@DecodedValue VARCHAR(MAX) OUTPUT', @DecodedValue  = @OLD_TerritoryID_DECODE_VALUE  OUTPUT
		EXEC sp_executesql @OLD_CountryRegionCode_SQL , N'@DecodedValue VARCHAR(MAX) OUTPUT', @DecodedValue  = @OLD_CountryRegionCode_DECODE_VALUE  OUTPUT
		
		/*Execute the SQL Statement to load decode value for New Data*/
		EXEC sp_executesql @NEW_TerritoryID_SQL , N'@DecodedValue VARCHAR(MAX) OUTPUT', @DecodedValue  = @NEW_TerritoryID_DECODE_VALUE  OUTPUT
		EXEC sp_executesql @NEW_CountryRegionCode_SQL , N'@DecodedValue VARCHAR(MAX) OUTPUT', @DecodedValue  = @NEW_CountryRegionCode_DECODE_VALUE  OUTPUT
		
		DECLARE @AuditSubCategoryID smallint 
		SET @AuditSubCategoryID = NULL
		SELECT @AuditSubCategoryID = AuditSubCategoryID from Audit.AuditSubCategory where AssociatedTable = 'SalesTerritory'
 
		IF @AuditSubCategoryID IS NULL
		SET @AuditSubCategoryID = -1
		/*Get the Action Type Id from Audit.AuditLogActionType Table for Update transaction*/
		SELECT @AuditLogActionTypeID  = ActionTypeId  FROM Audit.AuditLogActionType WHERE ActionTypeDesc = 'Update'
 
		Insert into Audit.AuditLogData  (TableName, ColumnName, RecordIdentifierName, RecordIdentifierValue, ActionBy_UserId, ActionDateTime, ActionType, Audit_Description, OldValue, OldValue_Decode, NewValue, NewValue_Decode, AuditSubCategoryID)
			SELECT 'SalesTerritory', 'TerritoryID', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'TerritoryID' + @COMMENTS , @OLD_TerritoryID , COALESCE(('' + @OLD_TerritoryID_DECODE_VALUE + ''), null), CAST(@NEW_TerritoryID AS VARCHAR(1000)), COALESCE(('' + @NEW_TerritoryID_DECODE_VALUE + ''), null), @AuditSubCategoryID UNION ALL
			SELECT 'SalesTerritory', 'TerritoryName', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'TerritoryName' + @COMMENTS , @OLD_TerritoryName , COALESCE(('' + @OLD_TerritoryName_DECODE_VALUE + ''), null), CAST(@NEW_TerritoryName AS VARCHAR(1000)), COALESCE(('' + @NEW_TerritoryName_DECODE_VALUE + ''), null), @AuditSubCategoryID UNION ALL
			SELECT 'SalesTerritory', 'CountryRegionCode', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'CountryRegionCode' + @COMMENTS , @OLD_CountryRegionCode , COALESCE(('' + @OLD_CountryRegionCode_DECODE_VALUE + ''), null), CAST(@NEW_CountryRegionCode AS VARCHAR(1000)), COALESCE(('' + @NEW_CountryRegionCode_DECODE_VALUE + ''), null), @AuditSubCategoryID UNION ALL
			SELECT 'SalesTerritory', 'ModifiedBy', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'ModifiedBy' + @COMMENTS , @OLD_ModifiedBy , COALESCE(('' + @OLD_ModifiedBy_DECODE_VALUE + ''), null), CAST(@NEW_ModifiedBy AS VARCHAR(1000)), COALESCE(('' + @NEW_ModifiedBy_DECODE_VALUE + ''), null), @AuditSubCategoryID UNION ALL
			SELECT 'SalesTerritory', 'ModifiedDate', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'ModifiedDate' + @COMMENTS , @OLD_ModifiedDate , COALESCE(('' + @OLD_ModifiedDate_DECODE_VALUE + ''), null), CAST(@NEW_ModifiedDate AS VARCHAR(1000)), COALESCE(('' + @NEW_ModifiedDate_DECODE_VALUE + ''), null), @AuditSubCategoryID
 
END
 
 
GO
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'Sales.[trgCountryRegionUpdateAudit]'))
DROP TRIGGER [Sales].[trgCountryRegionUpdateAudit]
 
GO
 
/********************************************************************************
*  Name  		: trgCountryRegionUpdateAudit
*  Version 		: 1.0
*  Developer 	: Jash
*  Description 	: Update Audit Log Trigger
*  Tables  		: CountryRegion
*  Views Used 	: None
*  Procedures  	: None
*  Functions 	: None
*********************************************************************************
*                  Modification History
*
* DATE				NAME				NATURE OF CHANGES
*--------------------------------------------------------------------------------
* 06-10-2012		Jash				Creation & Initial Coding
*
*
*
*
*
*********************************************************************************/
 
CREATE TRIGGER [Sales].[trgCountryRegionUpdateAudit]
ON [Sales].[CountryRegion]
AFTER  UPDATE
AS
 
BEGIN
 
		/*Local variable declartion for new data*/
 
		DECLARE @NEW_CountryRegionCode VARCHAR(1000)
		DECLARE @NEW_CountryRegionName VARCHAR(1000)
		DECLARE @NEW_ModifiedBy VARCHAR(1000)
		DECLARE @NEW_ModifiedDate VARCHAR(1000)
 
 
 
 
 
 
		/*Local variable declartion for new data Decode*/
		DECLARE @NEW_CountryRegionCode_DECODE_VALUE VARCHAR(1000)
		DECLARE @NEW_CountryRegionName_DECODE_VALUE VARCHAR(1000)
		DECLARE @NEW_ModifiedBy_DECODE_VALUE VARCHAR(1000)
		DECLARE @NEW_ModifiedDate_DECODE_VALUE VARCHAR(1000)
 
		/*Local variable declartion for old  data*/
 
		DECLARE @OLD_CountryRegionCode VARCHAR(1000)
		DECLARE @OLD_CountryRegionName VARCHAR(1000)
		DECLARE @OLD_ModifiedBy VARCHAR(1000)
		DECLARE @OLD_ModifiedDate VARCHAR(1000)
 
 
 
 
 
		/*Local variable declartion for old data Decode*/
		DECLARE @OLD_CountryRegionCode_DECODE_VALUE VARCHAR(1000)
		DECLARE @OLD_CountryRegionName_DECODE_VALUE VARCHAR(1000)
		DECLARE @OLD_ModifiedBy_DECODE_VALUE VARCHAR(1000)
		DECLARE @OLD_ModifiedDate_DECODE_VALUE VARCHAR(1000)
 
		/*Local variable declartion for new data decode value sql*/
		DECLARE @NEW_CountryRegionCode_SQL NVARCHAR(1000)
		DECLARE @NEW_CountryRegionName_SQL NVARCHAR(1000)
		DECLARE @NEW_ModifiedBy_SQL NVARCHAR(1000)
		DECLARE @NEW_ModifiedDate_SQL NVARCHAR(1000)
 
		/*Local variable declartion for new data decode value sql*/
		DECLARE @OLD_CountryRegionCode_SQL NVARCHAR(1000)
		DECLARE @OLD_CountryRegionName_SQL NVARCHAR(1000)
		DECLARE @OLD_ModifiedBy_SQL NVARCHAR(1000)
		DECLARE @OLD_ModifiedDate_SQL NVARCHAR(1000)
		/*Local variable declartion*/
 
		DECLARE @AuditLogActionTypeID  INT
		DECLARE @RecordIdentifierName NVARCHAR(100)
		DECLARE @RecordIdentifierValue NVARCHAR(1000)
		DECLARE @ActionBy_UserId VARCHAR(25)
		DECLARE @COMMENTS VARCHAR(600)
		
		IF (SELECT COUNT(*) FROM INSERTED) > 0 
		BEGIN
			/*Get new data from inserted table and assign into local variable*/
			SELECT
				@NEW_CountryRegionCode = CAST(CountryRegionCode AS VARCHAR(1000)),
				@NEW_CountryRegionCode_SQL = Audit.GenerateDynamicQuery('Sales.CountryRegion','CountryRegionCode', CAST(CountryRegionCode AS VARCHAR(1000))),
				@NEW_CountryRegionName = CAST(CountryRegionName AS VARCHAR(1000)),
				@NEW_ModifiedBy = CAST(ModifiedBy AS VARCHAR(1000)),
				@NEW_ModifiedDate = CAST(ModifiedDate AS VARCHAR(1000))
			FROM
				INSERTED
 
		END
		
		DECLARE @CURRENT_USER_NAME VARCHAR(MAX) 
		SET @CURRENT_USER_NAME  = suser_sname() 
		DECLARE @USER_NAME VARCHAR(MAX) 
		SET @USER_NAME = Substring(@CURRENT_USER_NAME, patindex('%\%', @CURRENT_USER_NAME) + 1, LEN(@CURRENT_USER_NAME)) 
		SELECT @ActionBy_UserId = @@SPID
		
		IF (@ActionBy_UserId IS NULL)
		BEGIN 
			SET @ActionBy_UserId = '-1'
		END 
		SET  @COMMENTS = ' Column in Sales.CountryRegion table has been updated by user id ' +  @ActionBy_UserId + ''
 
		/*If record exist in Deleted table means this is Update transaction*/
		IF (SELECT COUNT(*) FROM DELETED) > 0
		BEGIN
			/*Get existing data from deleted table and assign into local variable*/
			SELECT
				@OLD_CountryRegionCode = CAST(CountryRegionCode AS VARCHAR(1000)),
				@OLD_CountryRegionCode_SQL = Audit.GenerateDynamicQuery('Sales.CountryRegion','CountryRegionCode', CAST(CountryRegionCode AS VARCHAR(1000))),
				@RecordIdentifierName = 'CountryRegionCode',
				@RecordIdentifierValue = CAST(CountryRegionCode AS VARCHAR(1000)),
				@OLD_CountryRegionName = CAST(CountryRegionName AS VARCHAR(1000)),
				@OLD_ModifiedBy = CAST(ModifiedBy AS VARCHAR(1000)),
				@OLD_ModifiedDate = CAST(ModifiedDate AS VARCHAR(1000))
			FROM
				DELETED
 
		END
		/*Execute the SQL Statement to load decode value for Old Data*/
		EXEC sp_executesql @OLD_CountryRegionCode_SQL , N'@DecodedValue VARCHAR(MAX) OUTPUT', @DecodedValue  = @OLD_CountryRegionCode_DECODE_VALUE  OUTPUT
		
		/*Execute the SQL Statement to load decode value for New Data*/
		EXEC sp_executesql @NEW_CountryRegionCode_SQL , N'@DecodedValue VARCHAR(MAX) OUTPUT', @DecodedValue  = @NEW_CountryRegionCode_DECODE_VALUE  OUTPUT
		
		DECLARE @AuditSubCategoryID smallint 
		SET @AuditSubCategoryID = NULL
		SELECT @AuditSubCategoryID = AuditSubCategoryID from Audit.AuditSubCategory where AssociatedTable = 'CountryRegion'
 
		IF @AuditSubCategoryID IS NULL
		SET @AuditSubCategoryID = -1
		/*Get the Action Type Id from Audit.AuditLogActionType Table for Update transaction*/
		SELECT @AuditLogActionTypeID  = ActionTypeId  FROM Audit.AuditLogActionType WHERE ActionTypeDesc = 'Update'
 
		Insert into Audit.AuditLogData  (TableName, ColumnName, RecordIdentifierName, RecordIdentifierValue, ActionBy_UserId, ActionDateTime, ActionType, Audit_Description, OldValue, OldValue_Decode, NewValue, NewValue_Decode, AuditSubCategoryID)
			SELECT 'CountryRegion', 'CountryRegionCode', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'CountryRegionCode' + @COMMENTS , @OLD_CountryRegionCode , COALESCE(('' + @OLD_CountryRegionCode_DECODE_VALUE + ''), null), CAST(@NEW_CountryRegionCode AS VARCHAR(1000)), COALESCE(('' + @NEW_CountryRegionCode_DECODE_VALUE + ''), null), @AuditSubCategoryID UNION ALL
			SELECT 'CountryRegion', 'CountryRegionName', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'CountryRegionName' + @COMMENTS , @OLD_CountryRegionName , COALESCE(('' + @OLD_CountryRegionName_DECODE_VALUE + ''), null), CAST(@NEW_CountryRegionName AS VARCHAR(1000)), COALESCE(('' + @NEW_CountryRegionName_DECODE_VALUE + ''), null), @AuditSubCategoryID UNION ALL
			SELECT 'CountryRegion', 'ModifiedBy', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'ModifiedBy' + @COMMENTS , @OLD_ModifiedBy , COALESCE(('' + @OLD_ModifiedBy_DECODE_VALUE + ''), null), CAST(@NEW_ModifiedBy AS VARCHAR(1000)), COALESCE(('' + @NEW_ModifiedBy_DECODE_VALUE + ''), null), @AuditSubCategoryID UNION ALL
			SELECT 'CountryRegion', 'ModifiedDate', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'ModifiedDate' + @COMMENTS , @OLD_ModifiedDate , COALESCE(('' + @OLD_ModifiedDate_DECODE_VALUE + ''), null), CAST(@NEW_ModifiedDate AS VARCHAR(1000)), COALESCE(('' + @NEW_ModifiedDate_DECODE_VALUE + ''), null), @AuditSubCategoryID
 
END
 
GO
 
GO
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'Sales.[trgSalesPersonDeleteAudit]'))
DROP TRIGGER [Sales].[trgSalesPersonDeleteAudit]
 
GO
 
/********************************************************************************
*  Name  		: trgSalesPersonDeleteAudit
*  Version 		: 1.0
*  Developer 	: Jash
*  Description 	: Delete Audit Log Trigger
*  Tables  		: SalesPerson
*  Views Used 	: None
*  Procedures  	: None
*  Functions 	: None
*********************************************************************************
*                  Modification History
*
* DATE				NAME				NATURE OF CHANGES
*--------------------------------------------------------------------------------
* 06-10-2012		Jash				Creation & Initial Coding
*
*
*
*
*
*********************************************************************************/
 
CREATE TRIGGER [Sales].[trgSalesPersonDeleteAudit]
ON [Sales].[SalesPerson]
AFTER  DELETE
AS
 
BEGIN
 
		/*Local variable declartion for new data*/
 
		DECLARE @NEW_SalesPersonID VARCHAR(1000)
		DECLARE @NEW_SalesPersonName VARCHAR(1000)
		DECLARE @NEW_TerritoryID VARCHAR(1000)
		DECLARE @NEW_SalesQuota VARCHAR(1000)
		DECLARE @NEW_Bonus VARCHAR(1000)
		DECLARE @NEW_CommissionPct VARCHAR(1000)
		DECLARE @NEW_ModifiedBy VARCHAR(1000)
		DECLARE @NEW_ModifiedDate VARCHAR(1000)
 
 
 
 
 
 
		/*Local variable declartion for old  data*/
 
		DECLARE @OLD_SalesPersonID VARCHAR(1000)
		DECLARE @OLD_SalesPersonName VARCHAR(1000)
		DECLARE @OLD_TerritoryID VARCHAR(1000)
		DECLARE @OLD_SalesQuota VARCHAR(1000)
		DECLARE @OLD_Bonus VARCHAR(1000)
		DECLARE @OLD_CommissionPct VARCHAR(1000)
		DECLARE @OLD_ModifiedBy VARCHAR(1000)
		DECLARE @OLD_ModifiedDate VARCHAR(1000)
 
 
 
 
 
		/*Local variable declartion for old data Decode*/
		DECLARE @OLD_SalesPersonID_DECODE_VALUE VARCHAR(1000)
		DECLARE @OLD_SalesPersonName_DECODE_VALUE VARCHAR(1000)
		DECLARE @OLD_TerritoryID_DECODE_VALUE VARCHAR(1000)
		DECLARE @OLD_SalesQuota_DECODE_VALUE VARCHAR(1000)
		DECLARE @OLD_Bonus_DECODE_VALUE VARCHAR(1000)
		DECLARE @OLD_CommissionPct_DECODE_VALUE VARCHAR(1000)
		DECLARE @OLD_ModifiedBy_DECODE_VALUE VARCHAR(1000)
		DECLARE @OLD_ModifiedDate_DECODE_VALUE VARCHAR(1000)
 
		/*Local variable declartion for new data decode value sql*/
		DECLARE @OLD_SalesPersonID_SQL NVARCHAR(1000)
		DECLARE @OLD_SalesPersonName_SQL NVARCHAR(1000)
		DECLARE @OLD_TerritoryID_SQL NVARCHAR(1000)
		DECLARE @OLD_SalesQuota_SQL NVARCHAR(1000)
		DECLARE @OLD_Bonus_SQL NVARCHAR(1000)
		DECLARE @OLD_CommissionPct_SQL NVARCHAR(1000)
		DECLARE @OLD_ModifiedBy_SQL NVARCHAR(1000)
		DECLARE @OLD_ModifiedDate_SQL NVARCHAR(1000)
 
		DECLARE @AuditLogActionTypeID  INT
		DECLARE @RecordIdentifierName NVARCHAR(100)
		DECLARE @RecordIdentifierValue NVARCHAR(1000)
		DECLARE @ActionBy_UserId VARCHAR(25)
		DECLARE @COMMENTS VARCHAR(600)
		
		IF (SELECT COUNT(*) FROM INSERTED) > 0 
		BEGIN
		/*Get new data from inserted table and assign into local variable*/
		SELECT
				@NEW_SalesPersonID = null,
				@NEW_SalesPersonName = null,
				@NEW_TerritoryID = null,
				@NEW_SalesQuota = null,
				@NEW_Bonus = null,
				@NEW_CommissionPct = null,
				@NEW_ModifiedBy = null,
				@NEW_ModifiedDate = null
		FROM
				INSERTED
 
		END
		
			DECLARE @CURRENT_USER_NAME VARCHAR(MAX) 
			SET @CURRENT_USER_NAME  = suser_sname() 
			DECLARE @USER_NAME VARCHAR(MAX) 
			SET @USER_NAME = Substring(@CURRENT_USER_NAME, patindex('%\%', @CURRENT_USER_NAME) + 1, LEN(@CURRENT_USER_NAME)) 
			SELECT @ActionBy_UserId = @@SPID
		
		IF (@ActionBy_UserId IS NULL)
		BEGIN 
			SET @ActionBy_UserId = '-1'
		END 
		SET  @COMMENTS = ' Column in Sales.SalesPerson table has been deleted by user id ' +  @ActionBy_UserId + ''
 
		/*If record exist in Deleted table means this is Update or delete transaction*/
		IF (SELECT COUNT(*) FROM DELETED) > 0
			BEGIN
				/*Get existing data from deleted table and assign into local variable*/
				SELECT
				@OLD_SalesPersonID = CAST(SalesPersonID AS VARCHAR(1000)),
				@RecordIdentifierName = 'SalesPersonID',
				@RecordIdentifierValue = CAST(SalesPersonID AS VARCHAR(1000)),
				@OLD_SalesPersonName = CAST(SalesPersonName AS VARCHAR(1000)),
				@OLD_TerritoryID = CAST(TerritoryID AS VARCHAR(1000)),
				@OLD_TerritoryID_SQL = Audit.GenerateDynamicQuery('Sales.SalesPerson','TerritoryID', CAST(TerritoryID AS VARCHAR(1000))),
				@OLD_SalesQuota = CAST(SalesQuota AS VARCHAR(1000)),
				@OLD_Bonus = CAST(Bonus AS VARCHAR(1000)),
				@OLD_CommissionPct = CAST(CommissionPct AS VARCHAR(1000)),
				@OLD_ModifiedBy = CAST(ModifiedBy AS VARCHAR(1000)),
				@OLD_ModifiedDate = CAST(ModifiedDate AS VARCHAR(1000))
		FROM
				DELETED
 
		/*Execute the SQL into the variables*/
		EXEC sp_executesql @OLD_TerritoryID_SQL , N'@DecodedValue VARCHAR(MAX) OUTPUT', @DecodedValue  = @OLD_TerritoryID_DECODE_VALUE  OUTPUT
		
		DECLARE @AuditSubCategoryID smallint 
		SET @AuditSubCategoryID = NULL
		SELECT @AuditSubCategoryID = AuditSubCategoryID from Audit.AuditSubCategory where AssociatedTable = 'SalesPerson'
 
		IF @AuditSubCategoryID IS NULL
			SET @AuditSubCategoryID = -1
 
		/*Get the Action Type Id from Audit.AuditLogActionType Table for delete transaction*/
		SELECT @AuditLogActionTypeID  = ActionTypeId  FROM Audit.AuditLogActionType WHERE ActionTypeDesc = 'Delete'
 
		Insert into Audit.AuditLogData  (TableName, ColumnName, RecordIdentifierName, RecordIdentifierValue, ActionBy_UserId, ActionDateTime, ActionType, Audit_Description, OldValue, OldValue_Decode, NewValue, NewValue_Decode, AuditSubCategoryID)
			SELECT 'SalesPerson', 'SalesPersonID', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'SalesPersonID' + @COMMENTS , @OLD_SalesPersonID , COALESCE(('' + @OLD_SalesPersonID_DECODE_VALUE + ''), null), CAST(@NEW_SalesPersonID AS VARCHAR(1000)), null, @AuditSubCategoryID UNION ALL
			SELECT 'SalesPerson', 'SalesPersonName', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'SalesPersonName' + @COMMENTS , @OLD_SalesPersonName , COALESCE(('' + @OLD_SalesPersonName_DECODE_VALUE + ''), null), CAST(@NEW_SalesPersonName AS VARCHAR(1000)), null, @AuditSubCategoryID UNION ALL
			SELECT 'SalesPerson', 'TerritoryID', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'TerritoryID' + @COMMENTS , @OLD_TerritoryID , COALESCE(('' + @OLD_TerritoryID_DECODE_VALUE + ''), null), CAST(@NEW_TerritoryID AS VARCHAR(1000)), null, @AuditSubCategoryID UNION ALL
			SELECT 'SalesPerson', 'SalesQuota', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'SalesQuota' + @COMMENTS , @OLD_SalesQuota , COALESCE(('' + @OLD_SalesQuota_DECODE_VALUE + ''), null), CAST(@NEW_SalesQuota AS VARCHAR(1000)), null, @AuditSubCategoryID UNION ALL
			SELECT 'SalesPerson', 'Bonus', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'Bonus' + @COMMENTS , @OLD_Bonus , COALESCE(('' + @OLD_Bonus_DECODE_VALUE + ''), null), CAST(@NEW_Bonus AS VARCHAR(1000)), null, @AuditSubCategoryID UNION ALL
			SELECT 'SalesPerson', 'CommissionPct', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'CommissionPct' + @COMMENTS , @OLD_CommissionPct , COALESCE(('' + @OLD_CommissionPct_DECODE_VALUE + ''), null), CAST(@NEW_CommissionPct AS VARCHAR(1000)), null, @AuditSubCategoryID UNION ALL
			SELECT 'SalesPerson', 'ModifiedBy', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'ModifiedBy' + @COMMENTS , @OLD_ModifiedBy , COALESCE(('' + @OLD_ModifiedBy_DECODE_VALUE + ''), null), CAST(@NEW_ModifiedBy AS VARCHAR(1000)), null, @AuditSubCategoryID UNION ALL
			SELECT 'SalesPerson', 'ModifiedDate', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'ModifiedDate' + @COMMENTS , @OLD_ModifiedDate , null, CAST(@NEW_ModifiedDate AS VARCHAR(1000)), null, @AuditSubCategoryID
		END
 
END
 
GO
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'Sales.[trgSalesTerritoryDeleteAudit]'))
DROP TRIGGER [Sales].[trgSalesTerritoryDeleteAudit]
 
GO
 
/********************************************************************************
*  Name  		: trgSalesTerritoryDeleteAudit
*  Version 		: 1.0
*  Developer 	: Jash
*  Description 	: Delete Audit Log Trigger
*  Tables  		: SalesTerritory
*  Views Used 	: None
*  Procedures  	: None
*  Functions 	: None
*********************************************************************************
*                  Modification History
*
* DATE				NAME				NATURE OF CHANGES
*--------------------------------------------------------------------------------
* 06-10-2012		Jash				Creation & Initial Coding
*
*
*
*
*
*********************************************************************************/
 
CREATE TRIGGER [Sales].[trgSalesTerritoryDeleteAudit]
ON [Sales].[SalesTerritory]
AFTER  DELETE
AS
 
BEGIN
 
		/*Local variable declartion for new data*/
 
		DECLARE @NEW_TerritoryID VARCHAR(1000)
		DECLARE @NEW_TerritoryName VARCHAR(1000)
		DECLARE @NEW_CountryRegionCode VARCHAR(1000)
		DECLARE @NEW_ModifiedBy VARCHAR(1000)
		DECLARE @NEW_ModifiedDate VARCHAR(1000)
 
 
 
 
 
 
		/*Local variable declartion for old  data*/
 
		DECLARE @OLD_TerritoryID VARCHAR(1000)
		DECLARE @OLD_TerritoryName VARCHAR(1000)
		DECLARE @OLD_CountryRegionCode VARCHAR(1000)
		DECLARE @OLD_ModifiedBy VARCHAR(1000)
		DECLARE @OLD_ModifiedDate VARCHAR(1000)
 
 
 
 
 
		/*Local variable declartion for old data Decode*/
		DECLARE @OLD_TerritoryID_DECODE_VALUE VARCHAR(1000)
		DECLARE @OLD_TerritoryName_DECODE_VALUE VARCHAR(1000)
		DECLARE @OLD_CountryRegionCode_DECODE_VALUE VARCHAR(1000)
		DECLARE @OLD_ModifiedBy_DECODE_VALUE VARCHAR(1000)
		DECLARE @OLD_ModifiedDate_DECODE_VALUE VARCHAR(1000)
 
		/*Local variable declartion for new data decode value sql*/
		DECLARE @OLD_TerritoryID_SQL NVARCHAR(1000)
		DECLARE @OLD_TerritoryName_SQL NVARCHAR(1000)
		DECLARE @OLD_CountryRegionCode_SQL NVARCHAR(1000)
		DECLARE @OLD_ModifiedBy_SQL NVARCHAR(1000)
		DECLARE @OLD_ModifiedDate_SQL NVARCHAR(1000)
 
		DECLARE @AuditLogActionTypeID  INT
		DECLARE @RecordIdentifierName NVARCHAR(100)
		DECLARE @RecordIdentifierValue NVARCHAR(1000)
		DECLARE @ActionBy_UserId VARCHAR(25)
		DECLARE @COMMENTS VARCHAR(600)
		
		IF (SELECT COUNT(*) FROM INSERTED) > 0 
		BEGIN
		/*Get new data from inserted table and assign into local variable*/
		SELECT
				@NEW_TerritoryID = null,
				@NEW_TerritoryName = null,
				@NEW_CountryRegionCode = null,
				@NEW_ModifiedBy = null,
				@NEW_ModifiedDate = null
		FROM
				INSERTED
 
		END
		
			DECLARE @CURRENT_USER_NAME VARCHAR(MAX) 
			SET @CURRENT_USER_NAME  = suser_sname() 
			DECLARE @USER_NAME VARCHAR(MAX) 
			SET @USER_NAME = Substring(@CURRENT_USER_NAME, patindex('%\%', @CURRENT_USER_NAME) + 1, LEN(@CURRENT_USER_NAME)) 
			SELECT @ActionBy_UserId = @@SPID
		
		IF (@ActionBy_UserId IS NULL)
		BEGIN 
			SET @ActionBy_UserId = '-1'
		END 
		SET  @COMMENTS = ' Column in Sales.SalesTerritory table has been deleted by user id ' +  @ActionBy_UserId + ''
 
		/*If record exist in Deleted table means this is Update or delete transaction*/
		IF (SELECT COUNT(*) FROM DELETED) > 0
			BEGIN
				/*Get existing data from deleted table and assign into local variable*/
				SELECT
				@OLD_TerritoryID = CAST(TerritoryID AS VARCHAR(1000)),
				@OLD_TerritoryID_SQL = Audit.GenerateDynamicQuery('Sales.SalesTerritory','TerritoryID', CAST(TerritoryID AS VARCHAR(1000))),
				@RecordIdentifierName = 'TerritoryID',
				@RecordIdentifierValue = CAST(TerritoryID AS VARCHAR(1000)),
				@OLD_TerritoryName = CAST(TerritoryName AS VARCHAR(1000)),
				@OLD_CountryRegionCode = CAST(CountryRegionCode AS VARCHAR(1000)),
				@OLD_CountryRegionCode_SQL = Audit.GenerateDynamicQuery('Sales.SalesTerritory','CountryRegionCode', CAST(CountryRegionCode AS VARCHAR(1000))),
				@OLD_ModifiedBy = CAST(ModifiedBy AS VARCHAR(1000)),
				@OLD_ModifiedDate = CAST(ModifiedDate AS VARCHAR(1000))
		FROM
				DELETED
 
		/*Execute the SQL into the variables*/
		EXEC sp_executesql @OLD_TerritoryID_SQL , N'@DecodedValue VARCHAR(MAX) OUTPUT', @DecodedValue  = @OLD_TerritoryID_DECODE_VALUE  OUTPUT
		EXEC sp_executesql @OLD_CountryRegionCode_SQL , N'@DecodedValue VARCHAR(MAX) OUTPUT', @DecodedValue  = @OLD_CountryRegionCode_DECODE_VALUE  OUTPUT
		
		DECLARE @AuditSubCategoryID smallint 
		SET @AuditSubCategoryID = NULL
		SELECT @AuditSubCategoryID = AuditSubCategoryID from Audit.AuditSubCategory where AssociatedTable = 'SalesTerritory'
 
		IF @AuditSubCategoryID IS NULL
			SET @AuditSubCategoryID = -1
 
		/*Get the Action Type Id from Audit.AuditLogActionType Table for delete transaction*/
		SELECT @AuditLogActionTypeID  = ActionTypeId  FROM Audit.AuditLogActionType WHERE ActionTypeDesc = 'Delete'
 
		Insert into Audit.AuditLogData  (TableName, ColumnName, RecordIdentifierName, RecordIdentifierValue, ActionBy_UserId, ActionDateTime, ActionType, Audit_Description, OldValue, OldValue_Decode, NewValue, NewValue_Decode, AuditSubCategoryID)
			SELECT 'SalesTerritory', 'TerritoryID', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'TerritoryID' + @COMMENTS , @OLD_TerritoryID , COALESCE(('' + @OLD_TerritoryID_DECODE_VALUE + ''), null), CAST(@NEW_TerritoryID AS VARCHAR(1000)), null, @AuditSubCategoryID UNION ALL
			SELECT 'SalesTerritory', 'TerritoryName', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'TerritoryName' + @COMMENTS , @OLD_TerritoryName , COALESCE(('' + @OLD_TerritoryName_DECODE_VALUE + ''), null), CAST(@NEW_TerritoryName AS VARCHAR(1000)), null, @AuditSubCategoryID UNION ALL
			SELECT 'SalesTerritory', 'CountryRegionCode', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'CountryRegionCode' + @COMMENTS , @OLD_CountryRegionCode , COALESCE(('' + @OLD_CountryRegionCode_DECODE_VALUE + ''), null), CAST(@NEW_CountryRegionCode AS VARCHAR(1000)), null, @AuditSubCategoryID UNION ALL
			SELECT 'SalesTerritory', 'ModifiedBy', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'ModifiedBy' + @COMMENTS , @OLD_ModifiedBy , COALESCE(('' + @OLD_ModifiedBy_DECODE_VALUE + ''), null), CAST(@NEW_ModifiedBy AS VARCHAR(1000)), null, @AuditSubCategoryID UNION ALL
			SELECT 'SalesTerritory', 'ModifiedDate', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'ModifiedDate' + @COMMENTS , @OLD_ModifiedDate , null, CAST(@NEW_ModifiedDate AS VARCHAR(1000)), null, @AuditSubCategoryID
		END
 
END
 
GO
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'Sales.[trgCountryRegionDeleteAudit]'))
DROP TRIGGER [Sales].[trgCountryRegionDeleteAudit]
 
GO
 
/********************************************************************************
*  Name  		: trgCountryRegionDeleteAudit
*  Version 		: 1.0
*  Developer 	: Jash
*  Description 	: Delete Audit Log Trigger
*  Tables  		: CountryRegion
*  Views Used 	: None
*  Procedures  	: None
*  Functions 	: None
*********************************************************************************
*                  Modification History
*
* DATE				NAME				NATURE OF CHANGES
*--------------------------------------------------------------------------------
* 06-10-2012		Jash				Creation & Initial Coding
*
*
*
*
*
*********************************************************************************/
 
CREATE TRIGGER [Sales].[trgCountryRegionDeleteAudit]
ON [Sales].[CountryRegion]
AFTER  DELETE
AS
 
BEGIN
 
		/*Local variable declartion for new data*/
 
		DECLARE @NEW_CountryRegionCode VARCHAR(1000)
		DECLARE @NEW_CountryRegionName VARCHAR(1000)
		DECLARE @NEW_ModifiedBy VARCHAR(1000)
		DECLARE @NEW_ModifiedDate VARCHAR(1000)
 
 
 
 
 
 
		/*Local variable declartion for old  data*/
 
		DECLARE @OLD_CountryRegionCode VARCHAR(1000)
		DECLARE @OLD_CountryRegionName VARCHAR(1000)
		DECLARE @OLD_ModifiedBy VARCHAR(1000)
		DECLARE @OLD_ModifiedDate VARCHAR(1000)
 
 
 
 
 
		/*Local variable declartion for old data Decode*/
		DECLARE @OLD_CountryRegionCode_DECODE_VALUE VARCHAR(1000)
		DECLARE @OLD_CountryRegionName_DECODE_VALUE VARCHAR(1000)
		DECLARE @OLD_ModifiedBy_DECODE_VALUE VARCHAR(1000)
		DECLARE @OLD_ModifiedDate_DECODE_VALUE VARCHAR(1000)
 
		/*Local variable declartion for new data decode value sql*/
		DECLARE @OLD_CountryRegionCode_SQL NVARCHAR(1000)
		DECLARE @OLD_CountryRegionName_SQL NVARCHAR(1000)
		DECLARE @OLD_ModifiedBy_SQL NVARCHAR(1000)
		DECLARE @OLD_ModifiedDate_SQL NVARCHAR(1000)
 
		DECLARE @AuditLogActionTypeID  INT
		DECLARE @RecordIdentifierName NVARCHAR(100)
		DECLARE @RecordIdentifierValue NVARCHAR(1000)
		DECLARE @ActionBy_UserId VARCHAR(25)
		DECLARE @COMMENTS VARCHAR(600)
		
		IF (SELECT COUNT(*) FROM INSERTED) > 0 
		BEGIN
		/*Get new data from inserted table and assign into local variable*/
		SELECT
				@NEW_CountryRegionCode = null,
				@NEW_CountryRegionName = null,
				@NEW_ModifiedBy = null,
				@NEW_ModifiedDate = null
		FROM
				INSERTED
 
		END
		
			DECLARE @CURRENT_USER_NAME VARCHAR(MAX) 
			SET @CURRENT_USER_NAME  = suser_sname() 
			DECLARE @USER_NAME VARCHAR(MAX) 
			SET @USER_NAME = Substring(@CURRENT_USER_NAME, patindex('%\%', @CURRENT_USER_NAME) + 1, LEN(@CURRENT_USER_NAME)) 
			SELECT @ActionBy_UserId = @@SPID
		
		IF (@ActionBy_UserId IS NULL)
		BEGIN 
			SET @ActionBy_UserId = '-1'
		END 
		SET  @COMMENTS = ' Column in Sales.CountryRegion table has been deleted by user id ' +  @ActionBy_UserId + ''
 
		/*If record exist in Deleted table means this is Update or delete transaction*/
		IF (SELECT COUNT(*) FROM DELETED) > 0
			BEGIN
				/*Get existing data from deleted table and assign into local variable*/
				SELECT
				@OLD_CountryRegionCode = CAST(CountryRegionCode AS VARCHAR(1000)),
				@OLD_CountryRegionCode_SQL = Audit.GenerateDynamicQuery('Sales.CountryRegion','CountryRegionCode', CAST(CountryRegionCode AS VARCHAR(1000))),
				@RecordIdentifierName = 'CountryRegionCode',
				@RecordIdentifierValue = CAST(CountryRegionCode AS VARCHAR(1000)),
				@OLD_CountryRegionName = CAST(CountryRegionName AS VARCHAR(1000)),
				@OLD_ModifiedBy = CAST(ModifiedBy AS VARCHAR(1000)),
				@OLD_ModifiedDate = CAST(ModifiedDate AS VARCHAR(1000))
		FROM
				DELETED
 
		/*Execute the SQL into the variables*/
		EXEC sp_executesql @OLD_CountryRegionCode_SQL , N'@DecodedValue VARCHAR(MAX) OUTPUT', @DecodedValue  = @OLD_CountryRegionCode_DECODE_VALUE  OUTPUT
		
		DECLARE @AuditSubCategoryID smallint 
		SET @AuditSubCategoryID = NULL
		SELECT @AuditSubCategoryID = AuditSubCategoryID from Audit.AuditSubCategory where AssociatedTable = 'CountryRegion'
 
		IF @AuditSubCategoryID IS NULL
			SET @AuditSubCategoryID = -1
 
		/*Get the Action Type Id from Audit.AuditLogActionType Table for delete transaction*/
		SELECT @AuditLogActionTypeID  = ActionTypeId  FROM Audit.AuditLogActionType WHERE ActionTypeDesc = 'Delete'
 
		Insert into Audit.AuditLogData  (TableName, ColumnName, RecordIdentifierName, RecordIdentifierValue, ActionBy_UserId, ActionDateTime, ActionType, Audit_Description, OldValue, OldValue_Decode, NewValue, NewValue_Decode, AuditSubCategoryID)
			SELECT 'CountryRegion', 'CountryRegionCode', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'CountryRegionCode' + @COMMENTS , @OLD_CountryRegionCode , COALESCE(('' + @OLD_CountryRegionCode_DECODE_VALUE + ''), null), CAST(@NEW_CountryRegionCode AS VARCHAR(1000)), null, @AuditSubCategoryID UNION ALL
			SELECT 'CountryRegion', 'CountryRegionName', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'CountryRegionName' + @COMMENTS , @OLD_CountryRegionName , COALESCE(('' + @OLD_CountryRegionName_DECODE_VALUE + ''), null), CAST(@NEW_CountryRegionName AS VARCHAR(1000)), null, @AuditSubCategoryID UNION ALL
			SELECT 'CountryRegion', 'ModifiedBy', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'ModifiedBy' + @COMMENTS , @OLD_ModifiedBy , COALESCE(('' + @OLD_ModifiedBy_DECODE_VALUE + ''), null), CAST(@NEW_ModifiedBy AS VARCHAR(1000)), null, @AuditSubCategoryID UNION ALL
			SELECT 'CountryRegion', 'ModifiedDate', @RecordIdentifierName, @RecordIdentifierValue, @ActionBy_UserId, GetDate(), @AuditLogActionTypeID , 'ModifiedDate' + @COMMENTS , @OLD_ModifiedDate , null, CAST(@NEW_ModifiedDate AS VARCHAR(1000)), null, @AuditSubCategoryID
		END
 
END
GO
By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.
If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.
License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)
Written By
J.Jash
Technical Lead
United States
I am having 11+ Years of IT experience in Web based and Client-Server application development using Microsoft technologies.

Software Architect, as a field, had always fascinated me right from my college days, and always been fascinated with new technologies and Ideas.

I have also received EARLY ACHIVER award from Microsoft in Year 2003 for MCAD certification. And acquired MCDBA certification for SQL Server 2000.
Data Auditing Tool (Audit Trigger Generator)

License

Comments and Discussions
