Utilizzo di un trigger di contesto per il controllo dei dati di SQL Server
Scopo
Controllare i tuoi dati a volte può essere un incubo. Di recente ho creato un database per un combinatore automatico, doveva essere aggiornato da più fonti, il che significa che in caso di errori sarebbe stato molto difficile vedere quale processo avesse apportato la modifica.
Di recente avevo letto dell'utilizzo di Context come un modo per passare i dati tra i processi in SQL e, per fortuna, questo era ciò che era necessario, insieme a un trigger.
Per prima cosa creeremo due tabelle, una con i dati che vogliamo, poi un'altra con le stesse colonne e una colonna identità, data ora e nome processo per memorizzare tutte le nostre modifiche.
SQL
CREATE TABLE Audit(AuditID INT IDENTITY(1,1) CONSTRAINT PK_AuditID PRIMARY KEY,AuditData NVARCHAR(100))CREATE TABLE AuditAudit(AuditAuditID INT IDENTITY(1,1) CONSTRAINT PK_AuditAuditID PRIMARY KEY,AuditAuditDateTime DATETIME DEFAULT GETDATE(),AuditAuditProcess NVARCHAR(128),AuditID INT,AuditData NVARCHAR(100))
È stato utile?
SQL
CREATE TRIGGER AuditUpdated ON Audit AFTER INSERT, UPDATE AS BEGINDECLARE @Cont VARCHAR(128) =(SELECT CAST(CONTEXT_INFO() as varchar(128)))--Retrieve Context InfoINSERT INTO AuditAudit(AuditAuditProcess,AuditID,AuditData)SELECT ISNULL(@Cont,CAST(@@Spid AS VARCHAR(20))),AuditID,AuditData FROM Inserted--Insert process id if there is not context addedENDGO
È stato utile?
SQL
--insert with no contextINSERT INTO Audit(AuditData)SELECT '1'--insert with contextGODECLARE @Context_Info varbinary(128)SELECT @Context_Info = CAST('Insert'+SPACE(128) AS VARBINARY(128))--Declare the name for the contextSET CONTEXT_INFO @Context_Info--Update Context info to aboveINSERT INTO Audit(AuditData)--Run your codeSELECT '2'--update with contextGODECLARE @Context_Info varbinary(128)SELECT @Context_Info = CAST('Update'+SPACE(128) AS VARBINARY(128))--Declare the name for the contextSET CONTEXT_INFO @Context_Info--Update Context info to aboveUPDATE Audit SET AuditData='3' WHERE AuditID=1--Run your code--Select Records from the audit tableGOSELECT * FROM AuditAudit
Risultati
La tua dichiarazione selezionata dovrebbe avere tre righe come di seguito.
AuditID | AuditDateTime | AuditProcess | AuditId | AuditData |
1 | 2013-01-29 18:21:23.097 | 51 | 1 | 1 |
2 | 2013-01-29 18:21:27.433 | Insert | 2 | 2 |
3 | 2013-01-29 18:21:30.710 | Update | 1 | 3 |
Risultati
Ho scoperto che il modo più semplice per utilizzarlo era allegare nomi di processo univoci alle procedure memorizzate che aggiornano le tabelle, in modo da poter tenere traccia dell'origine delle modifiche ai dati.