Auditoría y sincronización en base de datos usando un desencadenante
Acerca de
Esta es una versión muy simplificada de un código que instalamos en el sitio de un cliente en el pasado. Tenían dos bases de datos muy diferentes en diferentes servidores (cliente y marcador) que necesitaban tener ciertos datos sincronizados en tiempo real.
Había algunas formas de hacerlo, replicación, o procedimientos almacenados vinculados a un trabajo o desencadenantes, en su ejemplo tenía que ser un trabajo, porque no teníamos el código fuente de una de las bases de datos, sin embargo mi método preferido sería el uso de desencadenantes con algo como esto...
SQL
CREATE TABLE Customer(
CustomerID INT IDENTITY(1,1) CONSTRAINT PK_CustomerID PRIMARY KEY,
CustomerName NVARCHAR(100),
CustomerStatus INT
--,Other Customer Data...
)
CREATE TABLE CustomerAudit(
CustomerAuditID INT IDENTITY(1,1) CONSTRAINT PK_CustomerAuditID PRIMARY KEY,
CustomerAuditType NVARCHAR(100),
CustomerAuditDate DATETIME DEFAULT GETDATE(),
CustomerID INT,
CustomerName NVARCHAR(100),
CustomerStatus INT)
CREATE TABLE Dialler(
CustomerID INT CONSTRAINT PK_DiallerCustomerID PRIMARY KEY,
CustomerStatus INT,
CustomerName NVARCHAR(100),
DiallerStatus INT--Other Dialler Records
)
GO
Así que ahora hemos creado algunas tablas muy básicas que pueden almacenar datos de los clientes. A continuación vamos a crear algunos procedimientos almacenados para manejar la actualización de registros en el lado del marcador.
La razón por la que he hecho esto como procedimientos almacenados es para mantener el volumen de datos bajo (no quiero que el servidor 1 envíe registros de base de datos al servidor 2), es en efecto pellizcar lo que normalmente se haría en un entorno web.
SQL
CREATE PROC DiallerUpdate(@CustomerID INT,@CustomerName NVARCHAR(100),@CustomerStatus INT) AS BEGIN
UPDATE Dialler SET CustomerStatus=@CustomerStatus,CustomerName=@CustomerName
WHERE CustomerID=@CustomerID
END
GO
CREATE PROC DiallerInsert(@CustomerID INT,@CustomerName NVARCHAR(100),@CustomerStatus INT) AS BEGIN
INSERT INTO Dialler(CustomerID,CustomerName,CustomerStatus,DiallerStatus)
SELECT @CustomerID,@CustomerName,@CustomerStatus,0
END
GO
CREATE PROC DiallerDelete(@CustomerID INT) AS BEGIN
DELETE FROM Dialler
WHERE CustomerID=@CustomerID
END
GOAhora que hemos creado estos, podemos pasar a la creación de un desencadenante que se encargará de enviar los datos y como lo estamos haciendo, también podemos auditar nuestros registros.
SQL
CREATE TRIGGER CustomerInsert ON Customer AFTER INSERT
AS BEGIN
DECLARE @CustomerID INT,@CustomerName NVARCHAR(100),@CustomerStatus INT
--Get Record Details
SELECT @CustomerID=CustomerID,@CustomerName=CustomerName,@CustomerStatus=CustomerStatus FROM inserted
--Add to Audit
INSERT INTO CustomerAudit(CustomerAuditType,CustomerID,CustomerName,CustomerStatus)
SELECT 'Record Created',@CustomerID,@CustomerName,@CustomerStatus
--Call Insert Procedure
EXEC dbo.DiallerInsert@CustomerID,@CustomerName,@CustomerStatus
END
GO
CREATE TRIGGER CustomerUpdate ON Customer AFTER Update
AS BEGIN
DECLARE @CustomerID INT,@CustomerName NVARCHAR(100),@CustomerStatus INT
--Get Record Details
SELECT @CustomerID=CustomerID,@CustomerName=CustomerName,@CustomerStatus=CustomerStatus FROM inserted
--Add to Audit
INSERT INTO CustomerAudit(CustomerAuditType,CustomerID,CustomerName,CustomerStatus)
SELECT 'Record Updated',@CustomerID,@CustomerName,@CustomerStatus
--Call Update Procedure
EXEC dbo.DiallerUpdate@CustomerID,@CustomerName,@CustomerStatus
END
GO
CREATE TRIGGER CustomerDelete ON Customer AFTER DELETE
AS BEGIN
DECLARE @CustomerID INT,@CustomerName NVARCHAR(100),@CustomerStatus INT
--Get Record Details
SELECT @CustomerID=CustomerID,@CustomerName=CustomerName,@CustomerStatus=CustomerStatus FROM deleted
--Add to Audit
INSERT INTO CustomerAudit(CustomerAuditType,CustomerID,CustomerName,CustomerStatus)
SELECT 'Record Deleted',@CustomerID,@CustomerName,@CustomerStatus
--Call Delete Procedure
EXEC dbo.DiallerDelete@CustomerID
END
GOY eso es todo, ahora tenemos una auditoría de datos, y los registros en ambos lados se sincronizarán en milisegundos... Si se necesita hacer cross server, cambiar el comando EXEC a {servername}.{databasename}.{schema}.DiallerDelete etc/
Aquí podemos probarlo.
SQL
-- Insert Data
INSERT INTO Customer(CustomerName,CustomerStatus) SELECT ' Name 1',0
INSERT INTO Customer(CustomerName,CustomerStatus) SELECT ' Name 2',0
INSERT INTO Customer(CustomerName,CustomerStatus) SELECT ' Name 3',0
UPDATE Customer SET CustomerStatus=2 WHERE CustomerID=1
UPDATE Customer SET CustomerName=' Name 4' WHERE CustomerID=2
DELETE FROM Customer WHERE CustomerID=3
--Review Data
SELECT * FROM Dialler
SELECT * FROM Customer
SELECT * FROM CustomerAuditResultados
| Dialler Records | |||
| CustomerID | CustomerStatus | CustomerName | DiallerStatus |
| 1 | 2 | Name 1 | 0 |
| 2 | 0 | Name 4 | 0 |
| Customer Records | ||
| CustomerID | CustomerName | CustomerStatus |
| 1 | Name 1 | 2 |
| 2 | Name 4 | 0 |
| Audit Records | ||||
| CustomerAuditID | CustomerAuditType | CustomerID | CustomerName | CustomerStatus |
| 1 | Record Created | 1 | Name 1 | 0 |
| 2 | Record Created | 2 | Name 2 | 0 |
| 3 | Record Created | 3 | Name 3 | 0 |
| 4 | Record Updated | 1 | Name 1 | 2 |
| 5 | Record Updated | 2 | Name 4 | 0 |
| 6 | Record Deleted | 3 | Name 3 | 0 |
