Solución:
Básicamente, está buscando rastrear / auditar cambios en una tabla mientras mantiene la tabla principal de tamaño pequeño.
Hay varias formas de resolver este problema. Los pros y los contras de cada método se analizan a continuación.
1 – Auditoría de la mesa con disparadores.
Si está buscando auditar la tabla (inserciones, actualizaciones, eliminaciones), consulte cómo evitar transacciones no deseadas: presentación de diapositivas de SQL Saturday con código: http://craftydba.com/?page_id=880. El activador que llena la tabla de auditoría puede contener información de varias tablas, si lo desea, ya que los datos se guardan como XML. Por lo tanto, puede anular la eliminación de una acción si es necesario analizando el XML. Realiza un seguimiento de quién y qué hizo el cambio.
Opcionalmente, puede tener la tabla de auditoría en su propio grupo de archivos.
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 – Fecha efectiva de los registros
Si nunca va a purgar los datos de la tabla de auditoría, ¿por qué no marcar la fila como eliminada y mantenerla para siempre? Muchos sistemas, como el de las personas, utilizan las citas efectivas para mostrar si un registro ya no está activo. En el mundo de BI, esto se denomina tabla dimensional de tipo 2 (dimensiones que cambian lentamente). Consulte el artículo del instituto de almacenamiento de datos. http://www.bidw.org/datawarehousing/scd-type-2/ Cada registro tiene una fecha de inicio y finalización.
Todos los registros activos tienen una fecha de finalización nula.
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 – Captura de datos modificados (función empresarial).
Micorsoft SQL Server 2008 introdujo la función de captura de datos modificados. Si bien esto rastrea el cambio de datos (CDC) usando un lector de LOG después del hecho, carece de cosas como quién y qué hizo el cambio. Detalles de MSDN: http://technet.microsoft.com/en-us/library/bb522489(v=sql.105).aspx
Esta solución depende de los trabajos de CDC que se estén ejecutando. Cualquier problema con el agente SQL provocará retrasos en la visualización de los datos.
Consulte las tablas de captura de datos modificados. 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 – Cambiar la función de seguimiento (todas las versiones).
Micorsoft SQL Server 2008 introdujo la función de seguimiento de cambios. A diferencia de CDC, viene con todas las versiones; Sin embargo, viene con un montón de funciones TSQL a las que debe llamar para averiguar qué sucedió.
Fue diseñado con el propósito de sincronizar una fuente de datos con el servidor SQL a través de una aplicación. Hay todo un trabajo de marco de sincronización en 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
A diferencia de CDC, usted especifica cuánto tiempo duran los cambios en la base de datos antes de ser depurados. Además, las inserciones y eliminaciones no registran datos. Las actualizaciones solo registran qué campo cambió.
Dado que está sincronizando la fuente del servidor SQL con otro destino, esto funciona bien. No es bueno para la auditoría a menos que escriba un trabajo periódico para averiguar los cambios.
Aún tendrá que almacenar esa información en algún lugar.
Description:
Enable change tracking
Cons:
Not a good auditing solution
Las primeras tres soluciones funcionarán para su auditoría. Me gusta la primera solución ya que la uso mucho en mi entorno.
Atentamente
John
Fragmento de código de la presentación (base de datos de 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];
** Aspecto y sensación de la tabla de auditoría **
El registro de cambios es algo que generalmente hago usando activadores en una tabla base para registrar cambios en una tabla de registro. La tabla de registro tiene columnas adicionales para registrar el usuario de la base de datos, la acción y la fecha / hora.
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"
Los activadores de registro siempre deben configurarse para que se activen en último lugar. De lo contrario, un desencadenante posterior puede revertir la transacción original, pero la tabla de registro ya se habrá actualizado. Este es un estado de cosas confuso.
Las versiones recientes de SQL Server (2016+ y Azure) tienen tablas temporales que brindan la funcionalidad exacta solicitada, como una característica de primera clase. https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables
Alguien de Microsoft probablemente haya leído esta página. 🙂