Как хранить исторические записи в таблице истории в SQL Server
У меня есть 2 таблицы, Table-A
и Table-A-History
.
-
Table-A
содержит текущие строки данных.
-
Table-A-History
содержит исторические данные
Я хотел бы иметь самую последнюю строку моих данных в Table-A
и Table-A-History
, содержащую исторические строки.
Я могу думать о 2 способах достижения этого:
-
всякий раз, когда доступна новая строка данных, переместите текущую строку от Table-A
до Table-A-History
и обновите строку Table-A
последними данными (через insert into select
или select into table
)
или
-
всякий раз, когда доступна новая строка данных, обновите строку Table-A
и вставьте новую строку в Table-A-History
.
Что касается производительности, то лучше метод 1 или 2? Есть ли другой способ сделать это?
Ответы
Ответ 1
Изменения в протоколе - это то, что я обычно делал с помощью триггеров в базовой таблице для записи изменений в таблице журналов. В таблице журнала есть дополнительные столбцы для записи пользователя базы данных, действия и даты/времени.
create trigger Table-A_LogDelete on dbo.Table-A
for delete
as
declare @Now as DateTime = GetDate()
set nocount on
insert into Table-A-History
select SUser_SName(), 'delete-deleted', @Now, *
from deleted
go
exec sp_settriggerorder @triggername = 'Table-A_LogDelete', @order = 'last', @stmttype = 'delete'
go
create trigger Table-A_LogInsert on dbo.Table-A
for insert
as
declare @Now as DateTime = GetDate()
set nocount on
insert into Table-A-History
select SUser_SName(), 'insert-inserted', @Now, *
from inserted
go
exec sp_settriggerorder @triggername = 'Table-A_LogInsert', @order = 'last', @stmttype = 'insert'
go
create trigger Table-A_LogUpdate on dbo.Table-A
for update
as
declare @Now as DateTime = GetDate()
set nocount on
insert into Table-A-History
select SUser_SName(), 'update-deleted', @Now, *
from deleted
insert into Table-A-History
select SUser_SName(), 'update-inserted', @Now, *
from inserted
go
exec sp_settriggerorder @triggername = 'Table-A_LogUpdate', @order = 'last', @stmttype = 'update'
Триггеры регистрации всегда должны быть установлены для последнего. В противном случае последующий триггер может откатить исходную транзакцию, но таблица журналов уже будет обновлена. Это запутанное положение дел.
Ответ 2
В основном вы ищете отслеживание/аудит изменений в таблице при сохранении размера основной таблицы.
Существует несколько способов решения этой проблемы. Концы и плюсы каждого из способов обсуждаются ниже.
1 - Аудит таблицы с триггерами.
Если вы хотите провести аудит таблицы (вставки, обновления, удаления), посмотрите, как отменить нежелательные транзакции - субботняя слайд-панель SQL с кодом - http://craftydba.com/?page_id=880. Триггер, заполняющий таблицу аудита, может содержать информацию из нескольких таблиц, если вы выберете, поскольку данные сохраняются как XML. Таким образом, вы можете удалить ненужное действие, проанализировав XML. Он отслеживает, кто и что внес изменения.
Необязательно, вы можете иметь таблицу аудита в своей собственной группе файлов.
Description:
Table Triggers For (Insert, Update, Delete)
Active table has current records.
Audit (history) table for non-active records.
Pros:
Active table has smaller # of records.
Index in active table is small.
Change is quickly reported in audit table.
Tells you what change was made (ins, del, upd)
Cons:
Have to join two tables to do historical reporting.
Does not track schema changes.
2 - Эффективное датирование записей
Если вы никогда не собираетесь очищать данные из таблицы аудита, почему бы не пометить строку как удаленную, но сохранить ее навсегда? Многие системы, такие как люди, используют эффективные знакомства, чтобы показать, что запись больше не активна. В мире BI это называется размерной таблицей типа 2 (медленно изменяющиеся размеры). См. Статью института хранилища данных. http://www.bidw.org/datawarehousing/scd-type-2/ Каждая запись имеет дату начала и окончания.
Все активные записи имеют дату окончания null.
Description:
Table Triggers For (Insert, Update, Delete)
Main table has both active and historical records.
Pros:
Historical reporting is easy.
Change is quickly shown in main table.
Cons:
Main table has a large # of records.
Index of main table is large.
Both active & history records in same filegroup.
Does not tell you what change was made (ins, del, upd)
Does not track schema changes.
3 - Изменить захват данных (функция предприятия).
Micorsoft SQL Server 2008 внедрил функцию захвата данных изменений. Хотя это отслеживает изменение данных (CDC) с помощью считывателя LOG после факта,
ему не хватает таких вещей, как кто и что изменило ситуацию. Подробности MSDN - http://technet.microsoft.com/en-us/library/bb522489(v=sql.105).aspx
Это решение зависит от выполняемых заданий CDC. Любые проблемы с агентом sql могут привести к задержкам с отображением данных.
См. таблицы сбора данных изменений.
http://technet.microsoft.com/en-us/library/bb500353(v=sql.105).aspx
Description:
Enable change data capture
Pros:
Do not need to add triggers or tables to capture data.
Tells you what change was made (ins, del, upd) the _$operation field in
<user_defined_table_CT>
Tracks schema changes.
Cons:
Only available in enterprise version.
Since it reads the log after the fact, time delay in data showing up.
The CDC tables do not track who or what made the change.
Disabling CDC removes the tables (not nice)!
Need to decode and use the _$update_mask to figure out what columns changed.
4 - Функция отслеживания изменений (все версии).
Micorsoft SQL Server 2008 представил функцию отслеживания изменений. В отличие от CDC, он поставляется со всеми версиями; Однако он поставляется с множеством функций TSQL, которые вы должны вызвать, чтобы выяснить, что произошло.
Он был разработан с целью синхронизации одного источника данных с SQL-сервером через приложение. На TechNet существует целая структура синхронизации.
http://msdn.microsoft.com/en-us/library/bb933874.aspx
http://msdn.microsoft.com/en-us/library/bb933994.aspx
http://technet.microsoft.com/en-us/library/bb934145(v=sql.105).aspx
В отличие от CDC, вы указываете, как долго последние изменения в базе данных перед очисткой. Кроме того, вставки и удаления не записывают данные. Обновления записывают только то, что изменилось.
Поскольку вы синхронизируете источник SQL-сервера с другой целью, это работает отлично.
Это не хорошо для аудита, если вы не пишете периодическое задание для определения изменений.
Вам все равно придется хранить эту информацию где-нибудь.
Description:
Enable change tracking
Cons:
Not a good auditing solution
Первые три решения будут работать для вашего аудита. Мне нравится первое решение, так как я широко использую его в своей среде.
С уважением
Джон
Фрагмент кода из презентации (база данных Autos)
--
-- 7 - Auditing data changes (table for DML trigger)
--
-- Delete existing table
IF OBJECT_ID('[AUDIT].[LOG_TABLE_CHANGES]') IS NOT NULL
DROP TABLE [AUDIT].[LOG_TABLE_CHANGES]
GO
-- Add the table
CREATE TABLE [AUDIT].[LOG_TABLE_CHANGES]
(
[CHG_ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[CHG_DATE] [datetime] NOT NULL,
[CHG_TYPE] [varchar](20) NOT NULL,
[CHG_BY] [nvarchar](256) NOT NULL,
[APP_NAME] [nvarchar](128) NOT NULL,
[HOST_NAME] [nvarchar](128) NOT NULL,
[SCHEMA_NAME] [sysname] NOT NULL,
[OBJECT_NAME] [sysname] NOT NULL,
[XML_RECSET] [xml] NULL,
CONSTRAINT [PK_LTC_CHG_ID] PRIMARY KEY CLUSTERED ([CHG_ID] ASC)
) ON [PRIMARY]
GO
-- Add defaults for key information
ALTER TABLE [AUDIT].[LOG_TABLE_CHANGES] ADD CONSTRAINT [DF_LTC_CHG_DATE] DEFAULT (getdate()) FOR [CHG_DATE];
ALTER TABLE [AUDIT].[LOG_TABLE_CHANGES] ADD CONSTRAINT [DF_LTC_CHG_TYPE] DEFAULT ('') FOR [CHG_TYPE];
ALTER TABLE [AUDIT].[LOG_TABLE_CHANGES] ADD CONSTRAINT [DF_LTC_CHG_BY] DEFAULT (coalesce(suser_sname(),'?')) FOR [CHG_BY];
ALTER TABLE [AUDIT].[LOG_TABLE_CHANGES] ADD CONSTRAINT [DF_LTC_APP_NAME] DEFAULT (coalesce(app_name(),'?')) FOR [APP_NAME];
ALTER TABLE [AUDIT].[LOG_TABLE_CHANGES] ADD CONSTRAINT [DF_LTC_HOST_NAME] DEFAULT (coalesce(host_name(),'?')) FOR [HOST_NAME];
GO
--
-- 8 - Make DML trigger to capture changes
--
-- Delete existing trigger
IF OBJECT_ID('[ACTIVE].[TRG_FLUID_DATA]') IS NOT NULL
DROP TRIGGER [ACTIVE].[TRG_FLUID_DATA]
GO
-- Add trigger to log all changes
CREATE TRIGGER [ACTIVE].[TRG_FLUID_DATA] ON [ACTIVE].[CARS_BY_COUNTRY]
FOR INSERT, UPDATE, DELETE AS
BEGIN
-- Detect inserts
IF EXISTS (select * from inserted) AND NOT EXISTS (select * from deleted)
BEGIN
INSERT [AUDIT].[LOG_TABLE_CHANGES] ([CHG_TYPE], [SCHEMA_NAME], [OBJECT_NAME], [XML_RECSET])
SELECT 'INSERT', '[ACTIVE]', '[CARS_BY_COUNTRY]', (SELECT * FROM inserted as Record for xml auto, elements , root('RecordSet'), type)
RETURN;
END
-- Detect deletes
IF EXISTS (select * from deleted) AND NOT EXISTS (select * from inserted)
BEGIN
INSERT [AUDIT].[LOG_TABLE_CHANGES] ([CHG_TYPE], [SCHEMA_NAME], [OBJECT_NAME], [XML_RECSET])
SELECT 'DELETE', '[ACTIVE]', '[CARS_BY_COUNTRY]', (SELECT * FROM deleted as Record for xml auto, elements , root('RecordSet'), type)
RETURN;
END
-- Update inserts
IF EXISTS (select * from inserted) AND EXISTS (select * from deleted)
BEGIN
INSERT [AUDIT].[LOG_TABLE_CHANGES] ([CHG_TYPE], [SCHEMA_NAME], [OBJECT_NAME], [XML_RECSET])
SELECT 'UPDATE', '[ACTIVE]', '[CARS_BY_COUNTRY]', (SELECT * FROM deleted as Record for xml auto, elements , root('RecordSet'), type)
RETURN;
END
END;
GO
--
-- 9 - Test DML trigger by updating, deleting and inserting data
--
-- Execute an update
UPDATE [ACTIVE].[CARS_BY_COUNTRY]
SET COUNTRY_NAME = 'Czech Republic'
WHERE COUNTRY_ID = 8
GO
-- Remove all data
DELETE FROM [ACTIVE].[CARS_BY_COUNTRY];
GO
-- Execute the load
EXECUTE [ACTIVE].[USP_LOAD_CARS_BY_COUNTRY];
GO
-- Show the audit trail
SELECT * FROM [AUDIT].[LOG_TABLE_CHANGES]
GO
-- Disable the trigger
ALTER TABLE [ACTIVE].[CARS_BY_COUNTRY] DISABLE TRIGGER [TRG_FLUID_DATA];
** Посмотрите и почувствуйте аудиторскую таблицу **
![enter image description here]()
Ответ 3
В последних версиях SQL-сервера (2016+ и Azure) есть временные таблицы, которые обеспечивают требуемую точную функциональность в качестве функции первого класса.
https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables
Кто-то из Microsoft, вероятно, читает эту страницу.:)
Ответ 4
Как насчет метода 3: Сделайте Table-A
представление против Table-A-History
. Вставьте в Table-A-History
и пусть соответствующая логика фильтрации генерирует Table-A
. Таким образом, вы вставляете только одну таблицу.
Ответ 5
Несмотря на то, что он потребляет больше места, наличие таблицы истории, содержащей самую последнюю запись, также избавит вас от необходимости записывать отчеты и видеть, как произошли изменения и когда. Что-то стоит подумать, по-моему.
Что касается производительности, я бы ожидал, что они будут одинаковыми. Но вы, конечно, не захотели бы удалить запись (вариант 1 "move" ) из таблицы non-hist, потому что вы используете ссылочную целостность между двумя таблицами, правильно?
Ответ 6
Вариант 1 в порядке.
Но у вас есть и метод 4:)
-
Вставьте новую запись в таблицу,
-
Перемещение старой записи в архивную таблицу на регулярной основе с использованием планировщика mysql. Вы можете запланировать архивацию данных во время минимальной нагрузки, например, в ночные часы.
Ответ 7
Я бы предпочел метод 1
Кроме того, я также буду поддерживать текущую запись в таблице истории
это зависит от необходимости.
Ответ 8
Существующая база данных выполняет этот запрос.
EXECUTE GenerateAudit 'Audit_DB_Name' , 'dbo', 'Audit_Table_Name' , 0, 1, 1,'',''
Создайте новую базу данных аудита, которая должна хранить историю вставки, обновления и удаления данных в существующей базе данных.
CREATE PROC [dbo].[GenerateAudit] @SchemanameAudit SYSNAME = '' --for other database
,@Schemaname SYSNAME = 'dbo'
,@Tablename SYSNAME
,@GenerateScriptOnly BIT = 1
,@ForceDropAuditTable BIT = 0
,@IgnoreExistingColumnMismatch BIT = 0
,@DontAuditforUsers NVARCHAR(4000) = ''
,@DontAuditforColumns NVARCHAR(4000) = ''
AS
SET NOCOUNT ON
/*
Parameters
@Schemaname - SchemaName to which the table belongs to. Default value 'dbo'.
@Tablename - TableName for which the procs needs to be generated.
@GenerateScriptOnly - When passed 1 , this will generate the scripts alone..
When passed 0 , this will create the audit tables and triggers in the current database.
Default value is 1
@ForceDropAuditTable - When passed 1 , will drop the audit table and recreate
When passed 0 , will generate the alter scripts
Default value is 0
@IgnoreExistingColumnMismatch - When passed 1 , will not stop with the error on the mismatch of existing column and will create the trigger.
When passed 0 , will stop with the error on the mismatch of existing column.
Default value is 0
@DontAuditforUsers - Pass the UserName as comma seperated for whom the audit is not required.
Default value is '' which will do audit for all the users.
@DontAuditforColumns - Pass the ColumnNames as comma seperated for which the audit is not required.
Default value is '' which will do audit for all the users.
*/
DECLARE @SQL VARCHAR(MAX)
DECLARE @SQLTrigger VARCHAR(MAX)
DECLARE @ErrMsg VARCHAR(MAX)
DECLARE @AuditTableName SYSNAME
DECLARE @QuotedSchemaName SYSNAME
DECLARE @QuotedSchemaNameAudit SYSNAME --for other database
DECLARE @QuotedTableName SYSNAME
DECLARE @QuotedAuditTableName SYSNAME
DECLARE @InsertTriggerName SYSNAME
DECLARE @UpdateTriggerName SYSNAME
DECLARE @DeleteTriggerName SYSNAME
DECLARE @QuotedInsertTriggerName SYSNAME
DECLARE @QuotedUpdateTriggerName SYSNAME
DECLARE @QuotedDeleteTriggerName SYSNAME
DECLARE @DontAuditforUsersTmp NVARCHAR(4000)
SELECT @AuditTableName = @Tablename + '_Audit'
SELECT @QuotedSchemaNameAudit = QUOTENAME(@SchemanameAudit) --for other database
SELECT @QuotedSchemaName = QUOTENAME(@Schemaname)
SELECT @QuotedTableName = QUOTENAME(@Tablename)
SELECT @QuotedAuditTableName = QUOTENAME(@AuditTableName)
SELECT @InsertTriggerName = @Tablename + '_Insert'
SELECT @UpdateTriggerName = @Tablename + '_Update'
SELECT @DeleteTriggerName = @Tablename + '_Delete'
SELECT @QuotedInsertTriggerName = QUOTENAME(@InsertTriggerName)
SELECT @QuotedUpdateTriggerName = QUOTENAME(@UpdateTriggerName)
SELECT @QuotedDeleteTriggerName = QUOTENAME(@DeleteTriggerName)
IF LTRIM(RTRIM(@DontAuditforUsers)) <> ''
BEGIN
IF RIGHT(@DontAuditforUsers, 1) = ','
BEGIN
SELECT @DontAuditforUsersTmp = LEFT(@DontAuditforUsers, LEN(@DontAuditforUsers) - 1)
END
ELSE
BEGIN
SELECT @DontAuditforUsersTmp = @DontAuditforUsers
END
SELECT @DontAuditforUsersTmp = REPLACE(@DontAuditforUsersTmp, ',', ''',''')
END
SELECT @DontAuditforColumns = ',' + UPPER(@DontAuditforColumns) + ','
IF NOT EXISTS (
SELECT 1
FROM sys.objects
WHERE Name = @TableName
AND Schema_id = Schema_id(@Schemaname)
AND Type = 'U'
)
BEGIN
SELECT @ErrMsg = @QuotedSchemaNameAudit + '.' + @QuotedSchemaName + '.' + @QuotedTableName + ' Table Not Found '
RAISERROR (
@ErrMsg
,16
,1
)
RETURN
END
----------------------------------------------------------------------------------------------------------------------
-- Audit Create OR Alter table
----------------------------------------------------------------------------------------------------------------------
DECLARE @ColList VARCHAR(MAX)
DECLARE @InsertColList VARCHAR(MAX)
DECLARE @UpdateCheck VARCHAR(MAX)
DECLARE @NewAddedCols TABLE (
ColumnName SYSNAME
,DataType SYSNAME
,CharLength INT
,Collation SYSNAME NULL
,ChangeType VARCHAR(20) NULL
,MainTableColumnName SYSNAME NULL
,MainTableDataType SYSNAME NULL
,MainTableCharLength INT NULL
,MainTableCollation SYSNAME NULL
,AuditTableColumnName SYSNAME NULL
,AuditTableDataType SYSNAME NULL
,AuditTableCharLength INT NULL
,AuditTableCollation SYSNAME NULL
)
SELECT @ColList = ''
SELECT @UpdateCheck = ' '
SELECT @SQL = ''
SELECT @InsertColList = ''
SELECT @ColList = @ColList + CASE SC.is_identity
WHEN 1
THEN 'CONVERT(' + ST.name + ',' + QUOTENAME(SC.name) + ') as ' + QUOTENAME(SC.name)
ELSE QUOTENAME(SC.name)
END + ','
,@InsertColList = @InsertColList + QUOTENAME(SC.name) + ','
,@UpdateCheck = @UpdateCheck + CASE
WHEN CHARINDEX(',' + UPPER(SC.NAME) + ',', @DontAuditforColumns) = 0
THEN 'CASE WHEN UPDATE(' + QUOTENAME(SC.name) + ') THEN ''' + QUOTENAME(SC.name) + '-'' ELSE '''' END + ' + CHAR(10)
ELSE ''
END
FROM SYS.COLUMNS SC
JOIN SYS.OBJECTS SO ON SC.object_id = SO.object_id
JOIN SYS.schemas SCH ON SCH.schema_id = SO.schema_id
JOIN SYS.types ST ON ST.user_type_id = SC.user_type_id
AND ST.system_type_id = SC.system_type_id
WHERE SCH.Name = @Schemaname
AND SO.name = @Tablename
AND UPPER(ST.name) <> UPPER('timestamp')
SELECT @ColList = SUBSTRING(@ColList, 1, LEN(@ColList) - 1)
SELECT @UpdateCheck = SUBSTRING(@UpdateCheck, 1, LEN(@UpdateCheck) - 3)
SELECT @InsertColList = SUBSTRING(@InsertColList, 1, LEN(@InsertColList) - 1)
SELECT @InsertColList = @InsertColList + ',AuditDataState,AuditDMLAction,AuditUser,AuditDateTime,UpdateColumns'
IF EXISTS (
SELECT 1
FROM sys.objects
WHERE Name = @AuditTableName
AND Schema_id = Schema_id(@Schemaname)
AND Type = 'U'
)
AND @ForceDropAuditTable = 0
BEGIN
----------------------------------------------------------------------------------------------------------------------
-- Get the comparision metadata for Main and Audit Tables
----------------------------------------------------------------------------------------------------------------------
INSERT INTO @NewAddedCols (
ColumnName
,DataType
,CharLength
,Collation
,ChangeType
,MainTableColumnName
,MainTableDataType
,MainTableCharLength
,MainTableCollation
,AuditTableColumnName
,AuditTableDataType
,AuditTableCharLength
,AuditTableCollation
)
SELECT ISNULL(MainTable.ColumnName, AuditTable.ColumnName)
,ISNULL(MainTable.DataType, AuditTable.DataType)
,ISNULL(MainTable.CharLength, AuditTable.CharLength)
,ISNULL(MainTable.Collation, AuditTable.Collation)
,CASE
WHEN MainTable.ColumnName IS NULL
THEN 'Deleted'
WHEN AuditTable.ColumnName IS NULL
THEN 'Added'
ELSE NULL
END
,MainTable.ColumnName
,MainTable.DataType
,MainTable.CharLength
,MainTable.Collation
,AuditTable.ColumnName
,AuditTable.DataType
,AuditTable.CharLength
,AuditTable.Collation
FROM (
SELECT SC.Name AS ColumnName
,ST.Name AS DataType
,SC.is_identity AS isIdentity
,SC.Max_length AS CharLength
,SC.Collation_Name AS Collation
FROM SYS.COLUMNS SC
JOIN SYS.OBJECTS SO ON SC.object_id = SO.object_id
JOIN SYS.schemas SCH ON SCH.schema_id = SO.schema_id
JOIN SYS.types ST ON ST.user_type_id = SC.user_type_id
AND ST.system_type_id = SC.system_type_id
WHERE SCH.Name = @Schemaname
AND SO.name = @Tablename
AND UPPER(ST.name) <> UPPER('timestamp')
) MainTable
FULL OUTER JOIN (
SELECT SC.Name AS ColumnName
,ST.Name AS DataType
,SC.is_identity AS isIdentity
,SC.Max_length AS CharLength
,SC.Collation_Name AS Collation
FROM SYS.COLUMNS SC
JOIN SYS.OBJECTS SO ON SC.object_id = SO.object_id
JOIN SYS.schemas SCH ON SCH.schema_id = SO.schema_id
JOIN SYS.types ST ON ST.user_type_id = SC.user_type_id
AND ST.system_type_id = SC.system_type_id
WHERE SCH.Name = @Schemaname
AND SO.name = @AuditTableName
AND UPPER(ST.name) <> UPPER('timestamp')
AND SC.Name NOT IN (
'AuditDataState'
,'AuditDMLAction'
,'AuditUser'
,'AuditDateTime'
,'UpdateColumns'
)
) AuditTable ON MainTable.ColumnName = AuditTable.ColumnName
----------------------------------------------------------------------------------------------------------------------
-- Find data type changes between table
----------------------------------------------------------------------------------------------------------------------
IF EXISTS (
SELECT *
FROM @NewAddedCols NC
WHERE NC.MainTableColumnName = NC.AuditTableColumnName
AND (
NC.MainTableDataType <> NC.AuditTableDataType
OR NC.MainTableCharLength > NC.AuditTableCharLength
OR NC.MainTableCollation <> NC.AuditTableCollation
)
)
BEGIN
SELECT CONVERT(VARCHAR(50), CASE
WHEN NC.MainTableDataType <> NC.AuditTableDataType
THEN 'DataType Mismatch'
WHEN NC.MainTableCharLength > NC.AuditTableCharLength
THEN 'Length in maintable is greater than Audit Table'
WHEN NC.MainTableCollation <> NC.AuditTableCollation
THEN 'Collation Difference'
END) AS Mismatch
,NC.MainTableColumnName
,NC.MainTableDataType
,NC.MainTableCharLength
,NC.MainTableCollation
,NC.AuditTableColumnName
,NC.AuditTableDataType
,NC.AuditTableCharLength
,NC.AuditTableCollation
FROM @NewAddedCols NC
WHERE NC.MainTableColumnName = NC.AuditTableColumnName
AND (
NC.MainTableDataType <> NC.AuditTableDataType
OR NC.MainTableCharLength > NC.AuditTableCharLength
OR NC.MainTableCollation <> NC.AuditTableCollation
)
RAISERROR (
'There are differences in Datatype or Lesser Length or Collation difference between the Main table and Audit Table. Please refer the output'
,16
,1
)
IF @IgnoreExistingColumnMismatch = 0
BEGIN
RETURN
END
END
----------------------------------------------------------------------------------------------------------------------
-- Find the new and deleted columns
----------------------------------------------------------------------------------------------------------------------
IF EXISTS (
SELECT *
FROM @NewAddedCols
WHERE ChangeType IS NOT NULL
)
BEGIN
SELECT @SQL = @SQL + 'ALTER TABLE ' + @QuotedSchemaNameAudit + '.' + @QuotedSchemaName + '.' + @QuotedAuditTableName + CASE
WHEN NC.ChangeType = 'Added'
THEN ' ADD ' + QUOTENAME(NC.ColumnName) + ' ' + NC.DataType + ' ' + CASE
WHEN NC.DataType IN (
'char'
,'varchar'
,'nchar'
,'nvarchar'
)
AND NC.CharLength = - 1
THEN '(max) COLLATE ' + NC.Collation + ' NULL '
WHEN NC.DataType IN (
'char'
,'varchar'
)
THEN '(' + CONVERT(VARCHAR(5), NC.CharLength) + ') COLLATE ' + NC.Collation + ' NULL '
WHEN NC.DataType IN (
'nchar'
,'nvarchar'
)
THEN '(' + CONVERT(VARCHAR(5), NC.CharLength / 2) + ') COLLATE ' + NC.Collation + ' NULL '
ELSE ''
END
WHEN NC.ChangeType = 'Deleted'
THEN ' DROP COLUMN ' + QUOTENAME(NC.ColumnName)
END + CHAR(10)
FROM @NewAddedCols NC
WHERE NC.ChangeType IS NOT NULL
END
END
ELSE
BEGIN
SELECT @SQL = ' IF EXISTS (SELECT 1
FROM sys.objects
WHERE Name=''' + @AuditTableName + '''
AND Schema_id=Schema_id(''' + @Schemaname + ''')
AND Type = ''U'')
DROP TABLE ' + @QuotedSchemaNameAudit + '.' + @QuotedSchemaName + '.' + @QuotedAuditTableName + '
SELECT ' + @ColList + '
,AuditDataState=CONVERT(VARCHAR(10),'''')
,AuditDMLAction=CONVERT(VARCHAR(10),'''')
,AuditUser =CONVERT(SYSNAME,'''')
,AuditDateTime=CONVERT(DATETIME,''01-JAN-1900'')
,UpdateColumns = CONVERT(VARCHAR(MAX),'''')
Into ' + @QuotedSchemaNameAudit + '.' + @QuotedSchemaName + '.' + @QuotedAuditTableName + '
FROM ' + @QuotedSchemaName + '.' + @QuotedTableName + '
WHERE 1=2
ALTER TABLE ' + @QuotedSchemaNameAudit + '.' + @QuotedSchemaName +
'.' + @QuotedAuditTableName + ' ADD AuditId INT IDENTITY(1,1)
'
--imran tag added the above alter table auto identity
END
IF @GenerateScriptOnly = 1
BEGIN
PRINT REPLICATE('-', 200)
PRINT '--Create \ Alter Script Audit table for ' + @QuotedSchemaName + '.' + @QuotedTableName
PRINT REPLICATE('-', 200)
PRINT @SQL
IF LTRIM(RTRIM(@SQL)) <> ''
BEGIN
PRINT 'GO'
END
ELSE
BEGIN
PRINT '-- No changes in table structure'
END
END
ELSE
BEGIN
IF RTRIM(LTRIM(@SQL)) = ''
BEGIN
PRINT 'No Table Changes Found'
END
ELSE
BEGIN
PRINT 'Creating \ Altered Audit table for ' + @QuotedSchemaName + '.' + @QuotedTableName
EXEC (@SQL)
PRINT 'Audit table ' + @QuotedSchemaNameAudit + '.' + @QuotedSchemaName + '.' + @QuotedAuditTableName + ' Created \ Altered succesfully'
END
END
----------------------------------------------------------------------------------------------------------------------
-- Create Insert Trigger
----------------------------------------------------------------------------------------------------------------------
SELECT @SQL = '
IF EXISTS (SELECT 1
FROM sys.objects
WHERE Name=''' + @Tablename + '_Insert' + '''
AND Schema_id=Schema_id(''' + @Schemaname + ''')
AND Type = ''TR'')
DROP TRIGGER ' + @QuotedSchemaName + '.' + @QuotedInsertTriggerName
SELECT @SQLTrigger = '
CREATE TRIGGER ' + @QuotedSchemaName + '.' + @QuotedInsertTriggerName + '
ON ' + @QuotedSchemaName + '.' + @QuotedTableName + '
FOR INSERT
AS
'
IF LTRIM(RTRIM(@DontAuditforUsersTmp)) <> ''
BEGIN
SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + ' IF SUSER_NAME() NOT IN (''' + @DontAuditforUsersTmp + ''')'
SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + ' BEGIN'
END
SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + ' INSERT INTO ' + @QuotedSchemaNameAudit + '.' + @QuotedSchemaName + '.' + @QuotedAuditTableName + CHAR(10) + '(' + @InsertColList + ')' + CHAR(10) + 'SELECT ' + @ColList + ',''New'',''Insert'',SUSER_SNAME(),getdate(),'''' FROM INSERTED '
IF LTRIM(RTRIM(@DontAuditforUsersTmp)) <> ''
BEGIN
SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + ' END'
END
IF @GenerateScriptOnly = 1
BEGIN
PRINT REPLICATE('-', 200)
PRINT '--Create Script Insert Trigger for ' + @QuotedSchemaName + '.' + @QuotedTablename
PRINT REPLICATE('-', 200)
PRINT @SQL
PRINT 'GO'
PRINT @SQLTrigger
PRINT 'GO'
END
ELSE
BEGIN
PRINT 'Creating Insert Trigger ' + @QuotedInsertTriggerName + ' for ' + @QuotedSchemaName + '.' + @QuotedTablename
EXEC (@SQL)
EXEC (@SQLTrigger)
PRINT 'Trigger ' + @QuotedSchemaName + '.' + @QuotedInsertTriggerName + ' Created succesfully'
END
----------------------------------------------------------------------------------------------------------------------
-- Create Delete Trigger
----------------------------------------------------------------------------------------------------------------------
SELECT @SQL = '
IF EXISTS (SELECT 1
FROM sys.objects
WHERE Name=''' + @Tablename + '_Delete' + '''
AND Schema_id=Schema_id(''' + @Schemaname + ''')
AND Type = ''TR'')
DROP TRIGGER ' + @QuotedSchemaName + '.' + + @QuotedDeleteTriggerName + '
'
SELECT @SQLTrigger = '
CREATE TRIGGER ' + @QuotedSchemaName + '.' + @QuotedDeleteTriggerName + '
ON ' + @QuotedSchemaName + '.' + @QuotedTableName + '
FOR DELETE
AS '
IF LTRIM(RTRIM(@DontAuditforUsersTmp)) <> ''
BEGIN
SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + ' IF SUSER_NAME() NOT IN (''' + @DontAuditforUsersTmp + ''')'
SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + ' BEGIN'
END
SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + ' INSERT INTO ' + @QuotedSchemaNameAudit + '.' + @QuotedSchemaName + '.' + @QuotedAuditTableName + CHAR(10) + '(' + @InsertColList + ')' + CHAR(10) + 'SELECT ' + @ColList + ',''Old'',''Delete'',SUSER_SNAME(),getdate(),'''' FROM DELETED'
IF LTRIM(RTRIM(@DontAuditforUsersTmp)) <> ''
BEGIN
SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + ' END'
END
IF @GenerateScriptOnly = 1
BEGIN
PRINT REPLICATE('-', 200)
PRINT '--Create Script Delete Trigger for ' + @QuotedSchemaName + '.' + @QuotedTableName
PRINT REPLICATE('-', 200)
PRINT @SQL
PRINT 'GO'
PRINT @SQLTrigger
PRINT 'GO'
END
ELSE
BEGIN
PRINT 'Creating Delete Trigger ' + @QuotedDeleteTriggerName + ' for ' + @QuotedSchemaName + '.' + @QuotedTableName
EXEC (@SQL)
EXEC (@SQLTrigger)
PRINT 'Trigger ' + @QuotedSchemaName + '.' + @QuotedDeleteTriggerName + ' Created succesfully'
END
----------------------------------------------------------------------------------------------------------------------
-- Create Update Trigger
----------------------------------------------------------------------------------------------------------------------
SELECT @SQL = '
IF EXISTS (SELECT 1
FROM sys.objects
WHERE Name=''' + @Tablename + '_Update' + '''
AND Schema_id=Schema_id(''' + @Schemaname + ''')
AND Type = ''TR'')
DROP TRIGGER ' + @QuotedSchemaName + '.' + @QuotedUpdateTriggerName + '
'
SELECT @SQLTrigger = '
CREATE TRIGGER ' + @QuotedSchemaName + '.' + @QuotedUpdateTriggerName + '
ON ' + @QuotedSchemaName + '.' + @QuotedTableName + '
FOR UPDATE
AS '
IF LTRIM(RTRIM(@DontAuditforUsersTmp)) <> ''
BEGIN
SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + ' IF SUSER_NAME() NOT IN (''' + @DontAuditforUsersTmp + ''')'
SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + ' BEGIN'
END
SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + '
DECLARE @UpdatedCols varchar(max)
SELECT @UpdatedCols = ' + @UpdateCheck + '
IF LTRIM(RTRIM(@UpdatedCols)) <> ''''
BEGIN
INSERT INTO ' + @QuotedSchemaNameAudit + '.' + @QuotedSchemaName + '.' + @QuotedAuditTableName + CHAR(10) + '(' + @InsertColList + ')' + CHAR(10) + 'SELECT ' + @ColList + ',''New'',''Update'',SUSER_SNAME(),getdate(),@UpdatedCols FROM INSERTED
INSERT INTO ' + @QuotedSchemaNameAudit + '.' + @QuotedSchemaName + '.' + @QuotedAuditTableName + CHAR(10) + '(' + @InsertColList + ')' + CHAR(10) + 'SELECT ' + @ColList + ',''Old'',''Update'',SUSER_SNAME(),getdate(),@UpdatedCols FROM DELETED
END'
IF LTRIM(RTRIM(@DontAuditforUsersTmp)) <> ''
BEGIN
SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + ' END'
END
IF @GenerateScriptOnly = 1
BEGIN
PRINT REPLICATE('-', 200)
PRINT '--Create Script Update Trigger for ' + @QuotedSchemaName + '.' + @QuotedTableName
PRINT REPLICATE('-', 200)
PRINT @SQL
PRINT 'GO'
PRINT @SQLTrigger
PRINT 'GO'
END
ELSE
BEGIN
PRINT 'Creating Delete Trigger ' + @QuotedUpdateTriggerName + ' for ' + @QuotedSchemaName + '.' + @QuotedTableName
EXEC (@SQL)
EXEC (@SQLTrigger)
PRINT 'Trigger ' + @QuotedSchemaName + '.' + @QuotedUpdateTriggerName + ' Created succesfully'
END
SET NOCOUNT OFF