Audit et synchronisation des données dans une base de données croisée à l'aide d'un déclencheur

Auditer et synchroniser des tables dans différentes bases de données, qui peuvent avoir une structure légèrement différente en créant un déclencheur

About

This is a very stripped down version of some code that we set up at a previous client site. They had two very different databases on different servers (customer and dialler) that needed to have certain data syncronised in real time.

There were a couple of ways of doing it, replication, or stored procedures linked to a job or triggers, in their example it had to be a job, because we didn't own the source code for one of the databases, however my preferred method would be using triggers with something like this...

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

So now we have created some very basic tables that can store customer data. Next we will create some Stored Procedures to handle updating records on the dialler side.

The reason I have done this as stored procedures is to keep the volume of data down (i don't want server 1 sending database records to server 2), it is in effect pinching what would normally be done in a web environment.

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

GO

Now we have created these, we can move onto creating a trigger that will handle sending the data and as we are doing it, we can also auditing our records.

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

GO

And that is pretty much it, we now have an audit of data, and the records on both sides will be syncrised within milliseconds... If it needs to be done cross server, change the EXEC command to {servername}.{databasename}.{schema}.DiallerDelete etc/

Here we can test it.

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 CustomerAudit

Selecting from the tables you can now check that each process has done it's job properly. by creating the different processes, it has also allowed us to be a lot more creative with what we have done.

Results

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

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