USE [GSFSchema]GO CREATE PROCEDURE [dbo].[InsertIntoAuditLog] (@tableName VARCHAR(128), @primaryKeyColumn VARCHAR(128), @primaryKeyValue NVARCHAR(MAX), @deleted BIT = '0', @inserted BIT = '0') AS	BEGIN 	DECLARE @columnName varchar(100) 	DECLARE @cursorColumnNames CURSOR 		SET @cursorColumnNames = CURSOR FOR 	SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tableName AND TABLE_CATALOG = db_name() 	OPEN @cursorColumnNames  	FETCH NEXT FROM @cursorColumnNames INTO @columnName 	WHILE @@FETCH_STATUS = 0 	BEGIN  		DECLARE @sql VARCHAR(MAX)				IF @deleted = '0' AND @inserted = '1'			SET @sql = 'INSERT INTO AuditLog (TableName, PrimaryKeyColumn, PrimaryKeyValue, ColumnName, OriginalValue, NewValue, Deleted, UpdatedBy) ' +					'SELECT ''' + @tableName + ''', ''' + @primaryKeyColumn + ''', ''' + @primaryKeyValue + ''', ''' + @columnName + ''', ' +					'NULL, CONVERT(NVARCHAR(MAX), #inserted.' + @columnName + '), ''0'', #inserted.UpdatedBy FROM #inserted'		ELSE IF @deleted = '1' AND @inserted = '0'			BEGIN				DECLARE @context VARCHAR(128)				SELECT @context = CASE WHEN CONTEXT_INFO() IS NULL THEN SUSER_NAME() ELSE CAST(CONTEXT_INFO() AS VARCHAR(128)) END				SET @sql = 'INSERT INTO AuditLog (TableName, PrimaryKeyColumn, PrimaryKeyValue, ColumnName, OriginalValue, NewValue, Deleted, UpdatedBy) ' +						'SELECT ''' + @tableName + ''', ''' + @primaryKeyColumn + ''', ''' + @primaryKeyValue + ''', ''' + @columnName + ''', ' +						'CONVERT(NVARCHAR(MAX), #deleted.' + @columnName + '), NULL, ''1'', ''' + @context + ''' FROM #deleted'			END		ELSE			SET @sql = 'DECLARE @oldVal NVARCHAR(MAX) ' +					'DECLARE @newVal NVARCHAR(MAX) ' +					'SELECT @oldVal = CONVERT(NVARCHAR(MAX), #deleted.' + @columnName + '), @newVal = CONVERT(NVARCHAR(MAX), #inserted.' + @columnName + ') FROM #deleted, #inserted ' +					'IF @oldVal <> @newVal BEGIN ' +								'INSERT INTO AuditLog (TableName, PrimaryKeyColumn, PrimaryKeyValue, ColumnName, OriginalValue, NewValue, Deleted, UpdatedBy) ' +					'SELECT ''' + @tableName + ''', ''' + @primaryKeyColumn + ''', ''' + @primaryKeyValue + ''', ''' + @columnName + ''', ' +					'CONVERT(NVARCHAR(MAX), #deleted.' + @columnName + '), CONVERT(NVARCHAR(MAX), #inserted.' + @columnName + '), ''0'', #inserted.UpdatedBy ' +					'FROM #inserted, #deleted ' +					'END' 		EXECUTE (@sql)				FETCH NEXT FROM @cursorColumnNames INTO @columnName 	END  	CLOSE @cursorColumnNames 	DEALLOCATE @cursorColumnNames	ENDGO SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO CREATE TRIGGER UserAccount_AuditInsert    ON  UserAccount   AFTER INSERTAS BEGIN		SET NOCOUNT ON;		SELECT * INTO #inserted FROM inserted		DECLARE @id NVARCHAR(MAX)			SELECT @id = CONVERT(NVARCHAR(MAX), ID) FROM #inserted	 	EXEC InsertIntoAuditLog 'UserAccount', 'ID', @id, '0', '1'		DROP TABLE #inserted ENDGO SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO CREATE TRIGGER UserAccount_AuditUpdate    ON  UserAccount   AFTER UPDATEAS BEGIN		SET NOCOUNT ON;		SELECT * INTO #deleted  FROM deleted	SELECT * INTO #inserted FROM inserted		DECLARE @id NVARCHAR(MAX)			SELECT @id = CONVERT(NVARCHAR(MAX), ID) FROM #deleted	 	EXEC InsertIntoAuditLog 'UserAccount', 'ID', @id		DROP TABLE #inserted	DROP TABLE #deleted ENDGO SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO CREATE TRIGGER UserAccount_AuditDelete   ON  UserAccount   AFTER DELETEAS BEGIN		SET NOCOUNT ON;		SELECT * INTO #deleted FROM deleted			DECLARE @id NVARCHAR(MAX)			SELECT @id = CONVERT(NVARCHAR(MAX), ID) FROM #deleted	 	EXEC InsertIntoAuditLog 'UserAccount', 'ID', @id, '1'			DROP TABLE #deleted ENDGO SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO CREATE TRIGGER SecurityGroup_AuditInsert    ON  SecurityGroup   AFTER INSERTAS BEGIN		SET NOCOUNT ON;		SELECT * INTO #inserted FROM inserted 	DECLARE @id NVARCHAR(MAX)			SELECT @id = CONVERT(NVARCHAR(MAX), ID) FROM #inserted		EXEC InsertIntoAuditLog 'SecurityGroup', 'ID', @id, '0', '1'		DROP TABLE #inserted ENDGO SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO CREATE TRIGGER SecurityGroup_AuditUpdate    ON  SecurityGroup   AFTER UPDATEAS BEGIN		SET NOCOUNT ON;		SELECT * INTO #deleted  FROM deleted	SELECT * INTO #inserted FROM inserted 	DECLARE @id NVARCHAR(MAX)			SELECT @id = CONVERT(NVARCHAR(MAX), ID) FROM #deleted			EXEC InsertIntoAuditLog 'SecurityGroup', 'ID', @id		DROP TABLE #inserted	DROP TABLE #deleted ENDGO SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO CREATE TRIGGER SecurityGroup_AuditDelete   ON  SecurityGroup   AFTER DELETEAS BEGIN		SET NOCOUNT ON;		SELECT * INTO #deleted FROM deleted			DECLARE @id NVARCHAR(MAX)			SELECT @id = CONVERT(NVARCHAR(MAX), ID) FROM #deleted	 	EXEC InsertIntoAuditLog 'SecurityGroup', 'ID', @id, '1'			DROP TABLE #deleted ENDGO SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO CREATE TRIGGER ApplicationRole_AuditInsert    ON  ApplicationRole   AFTER INSERTAS BEGIN		SET NOCOUNT ON;		SELECT * INTO #inserted FROM inserted 	DECLARE @id NVARCHAR(MAX)			SELECT @id = CONVERT(NVARCHAR(MAX), ID) FROM #inserted		EXEC InsertIntoAuditLog 'ApplicationRole', 'ID', @id, '0', '1'		DROP TABLE #inserted ENDGO SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO CREATE TRIGGER ApplicationRole_AuditUpdate    ON  ApplicationRole   AFTER UPDATEAS BEGIN		SET NOCOUNT ON;		SELECT * INTO #deleted  FROM deleted	SELECT * INTO #inserted FROM inserted 	DECLARE @id NVARCHAR(MAX)			SELECT @id = CONVERT(NVARCHAR(MAX), ID) FROM #deleted			EXEC InsertIntoAuditLog 'ApplicationRole', 'ID', @id		DROP TABLE #inserted	DROP TABLE #deleted ENDGO SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO CREATE TRIGGER ApplicationRole_AuditDelete   ON  ApplicationRole   AFTER DELETEAS BEGIN		SET NOCOUNT ON;		SELECT * INTO #deleted FROM deleted			DECLARE @id NVARCHAR(MAX)			SELECT @id = CONVERT(NVARCHAR(MAX), ID) FROM #deleted	 	EXEC InsertIntoAuditLog 'ApplicationRole', 'ID', @id, '1'			DROP TABLE #deleted ENDGO SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO CREATE TRIGGER CalculatedMeasurement_AuditInsert    ON  CalculatedMeasurement   AFTER INSERTAS BEGIN		SET NOCOUNT ON;		SELECT * INTO #inserted FROM inserted 	DECLARE @id NVARCHAR(MAX)			SELECT @id = CONVERT(NVARCHAR(MAX), ID) FROM #inserted		EXEC InsertIntoAuditLog 'CalculatedMeasurement', 'ID', @id, '0', '1'		DROP TABLE #inserted ENDGO SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO CREATE TRIGGER CalculatedMeasurement_AuditUpdate    ON  CalculatedMeasurement   AFTER UPDATEAS BEGIN		SET NOCOUNT ON;		SELECT * INTO #deleted  FROM deleted	SELECT * INTO #inserted FROM inserted 	DECLARE @id NVARCHAR(MAX)			SELECT @id = CONVERT(NVARCHAR(MAX), ID) FROM #deleted			EXEC InsertIntoAuditLog 'CalculatedMeasurement', 'ID', @id		DROP TABLE #inserted	DROP TABLE #deleted ENDGO SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO CREATE TRIGGER CalculatedMeasurement_AuditDelete   ON  CalculatedMeasurement   AFTER DELETEAS BEGIN		SET NOCOUNT ON;		SELECT * INTO #deleted FROM deleted			DECLARE @id NVARCHAR(MAX)			SELECT @id = CONVERT(NVARCHAR(MAX), ID) FROM #deleted	 	EXEC InsertIntoAuditLog 'CalculatedMeasurement', 'ID', @id, '1'			DROP TABLE #deleted ENDGO SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO CREATE TRIGGER Company_AuditInsert   ON  Company   AFTER INSERTAS BEGIN		SET NOCOUNT ON;		SELECT * INTO #inserted FROM inserted 	DECLARE @id NVARCHAR(MAX)			SELECT @id = CONVERT(NVARCHAR(MAX), ID) FROM #inserted		EXEC InsertIntoAuditLog 'Company', 'ID', @id, '0', '1'		DROP TABLE #inserted ENDGO SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO CREATE TRIGGER Company_AuditUpdate    ON  Company   AFTER UPDATEAS BEGIN		SET NOCOUNT ON;		SELECT * INTO #deleted  FROM deleted	SELECT * INTO #inserted FROM inserted 	DECLARE @id NVARCHAR(MAX)			SELECT @id = CONVERT(NVARCHAR(MAX), ID) FROM #deleted			EXEC InsertIntoAuditLog 'Company', 'ID', @id		DROP TABLE #inserted	DROP TABLE #deleted ENDGO SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO CREATE TRIGGER Company_AuditDelete   ON  Company   AFTER DELETEAS BEGIN		SET NOCOUNT ON;		SELECT * INTO #deleted FROM deleted			DECLARE @id NVARCHAR(MAX)			SELECT @id = CONVERT(NVARCHAR(MAX), ID) FROM #deleted	 	EXEC InsertIntoAuditLog 'Company', 'ID', @id, '1'			DROP TABLE #deleted ENDGO SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO CREATE TRIGGER CustomActionAdapter_AuditInsert   ON  CustomActionAdapter   AFTER INSERTAS BEGIN		SET NOCOUNT ON;		SELECT * INTO #inserted FROM inserted 	DECLARE @id NVARCHAR(MAX)			SELECT @id = CONVERT(NVARCHAR(MAX), ID) FROM #inserted		EXEC InsertIntoAuditLog 'CustomActionAdapter', 'ID', @id, '0', '1'		DROP TABLE #inserted ENDGO SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO CREATE TRIGGER CustomActionAdapter_AuditUpdate    ON  CustomActionAdapter   AFTER UPDATEAS BEGIN		SET NOCOUNT ON;		SELECT * INTO #deleted  FROM deleted	SELECT * INTO #inserted FROM inserted 	DECLARE @id NVARCHAR(MAX)			SELECT @id = CONVERT(NVARCHAR(MAX), ID) FROM #deleted			EXEC InsertIntoAuditLog 'CustomActionAdapter', 'ID', @id		DROP TABLE #inserted	DROP TABLE #deleted ENDGO SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO CREATE TRIGGER CustomActionAdapter_AuditDelete   ON  CustomActionAdapter   AFTER DELETEAS 

Lines 1–500 of 1968. Load more