Utilizzo di un trigger di contesto per il controllo dei dati di SQL Server

SQL Server 2008, controllo dei dati semplice: utilizzo di un trigger e di un contesto per tracciare gli aggiornamenti in una tabella SQL

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?

Successivamente creeremo un trigger sulla tabella. Vogliamo eseguirlo dopo eventuali INSERT o UPDATE.. Questo trigger aggiungerà quindi una riga alla tabella AuditAudit registrando quali sono i nuovi dati, la data e l'ora della modifica e il processo (se lo abbiamo inserito) o l'ID della connessione. Ulteriori informazioni sui trigger sono disponibili su MSDN .

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?

Ora possiamo alcune affermazioni per aggiornare la tabella. Per prima cosa eseguiamo un normale INSERT, quindi un INSERT e un UPDATE con il contesto popolato.

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.