Controllo e sincronizzazione dei dati in cross database utilizzando un trigger
Di
Questa è una versione molto ridotta di un codice che abbiamo impostato su un precedente sito client. Avevano due database molto diversi su server diversi (cliente e dialer) che dovevano sincronizzare determinati dati in tempo reale.
C'erano un paio di modi per farlo, la replica o le procedure memorizzate collegate a un lavoro o trigger, nel loro esempio doveva essere un lavoro, perché non possedevamo il codice sorgente per uno dei database, tuttavia il mio preferito il metodo utilizzerebbe trigger con qualcosa del genere ...
SQL
USE ClaytabaseAcadamyGOCREATE 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È stato utile?
Quindi ora abbiamo creato alcune tabelle molto semplici che possono memorizzare i dati dei clienti. Successivamente creeremo alcune stored procedure per gestire l'aggiornamento dei record sul lato dialer.
Il motivo per cui l'ho fatto come stored procedure è per mantenere basso il volume dei dati (non voglio che il server 1 invii record di database al server 2), in effetti sta pizzicando ciò che normalmente verrebbe fatto in un ambiente web.
SQL
USE ClaytabaseAcadamyGOCREATE PROC DiallerUpdate(@CustomerID INT,@CustomerName NVARCHAR(100),@CustomerStatus INT) AS BEGINUPDATE Dialler SET CustomerStatus=@CustomerStatus,CustomerName=@CustomerNameWHERE CustomerID=@CustomerIDENDGOCREATE PROC DiallerInsert(@CustomerID INT,@CustomerName NVARCHAR(100),@CustomerStatus INT) AS BEGININSERT INTO Dialler(CustomerID,CustomerName,CustomerStatus,DiallerStatus)SELECT @CustomerID,@CustomerName,@CustomerStatus,0ENDGOCREATE PROC DiallerDelete(@CustomerID INT) AS BEGINDELETE FROM DiallerWHERE CustomerID=@CustomerIDENDGOÈ stato utile?
Ora che li abbiamo creati, possiamo passare alla creazione di un trigger che gestirà l'invio dei dati e mentre lo stiamo facendo, possiamo anche controllare i nostri record.
SQL
USE ClaytabaseAcadamyGOCREATE TRIGGER CustomerInsert ON Customer AFTER INSERTAS BEGINDECLARE @CustomerID INT,@CustomerName NVARCHAR(100),@CustomerStatus INT--Get Record DetailsSELECT @CustomerID=CustomerID,@CustomerName=CustomerName,@CustomerStatus=CustomerStatus FROM inserted--Add to AuditINSERT INTO CustomerAudit(CustomerAuditType,CustomerID,CustomerName,CustomerStatus)SELECT 'Record Created',@CustomerID,@CustomerName,@CustomerStatus--Call Insert ProcedureEXEC dbo.DiallerInsert @CustomerID,@CustomerName,@CustomerStatusENDGOCREATE TRIGGER CustomerUpdate ON Customer AFTER UpdateAS BEGINDECLARE @CustomerID INT,@CustomerName NVARCHAR(100),@CustomerStatus INT--Get Record DetailsSELECT @CustomerID=CustomerID,@CustomerName=CustomerName,@CustomerStatus=CustomerStatus FROM inserted--Add to AuditINSERT INTO CustomerAudit(CustomerAuditType,CustomerID,CustomerName,CustomerStatus)SELECT 'Record Updated',@CustomerID,@CustomerName,@CustomerStatus--Call Update ProcedureEXEC dbo.DiallerUpdate @CustomerID,@CustomerName,@CustomerStatusENDGO CREATE TRIGGER CustomerDelete ON Customer AFTER DELETEAS BEGINDECLARE @CustomerID INT,@CustomerName NVARCHAR(100),@CustomerStatus INT--Get Record DetailsSELECT @CustomerID=CustomerID,@CustomerName=CustomerName,@CustomerStatus=CustomerStatus FROM deleted--Add to AuditINSERT INTO CustomerAudit(CustomerAuditType,CustomerID,CustomerName,CustomerStatus)SELECT 'Record Deleted',@CustomerID,@CustomerName,@CustomerStatus--Call Delete ProcedureEXEC dbo.DiallerDelete @CustomerIDENDGOÈ stato utile?
E questo è praticamente tutto, ora abbiamo un controllo dei dati e i record su entrambi i lati verranno sincronizzati in pochi millisecondi... Se è necessario farlo su più server, cambia il comando EXEC in {servername}.{databasename} .{schema}.DiallerDelete ecc/
Qui possiamo testarlo.
SQL
USE ClaytabaseAcadamyGO--Insert DataINSERT INTO Customer(CustomerName,CustomerStatus) SELECT ' Name 1',0INSERT INTO Customer(CustomerName,CustomerStatus) SELECT ' Name 2',0INSERT INTO Customer(CustomerName,CustomerStatus) SELECT ' Name 3',0UPDATE Customer SET CustomerStatus=2 WHERE CustomerID=1UPDATE Customer SET CustomerName=' Name 4' WHERE CustomerID=2DELETE FROM Customer WHERE CustomerID=3--Review DataSELECT * FROM DiallerSELECT * FROM CustomerSELECT * FROM CustomerAuditResults
| 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 |
