SQL सर्वर डेटा ऑडिटिंग के लिए संदर्भ ट्रिगर का उपयोग करना

SQL सर्वर 2008, आसान डेटा ऑडिटिंग - SQL तालिका में अपडेट ट्रेस करने के लिए ट्रिगर और संदर्भ का उपयोग करना

उद्देश्य

अपने डेटा का ऑडिट करना कभी-कभी एक बुरे सपने जैसा हो सकता है। मैंने हाल ही में एक स्वचालित डायलर के लिए एक डेटाबेस स्थापित किया है, इसे कई स्रोतों से अद्यतन करने की आवश्यकता है, जिसका अर्थ है कि त्रुटियों के मामले में यह देखना बहुत कठिन होगा कि किस प्रक्रिया ने परिवर्तन किया था।

मैंने हाल ही में एसक्यूएल में प्रक्रियाओं के बीच डेटा पास करने के तरीके के रूप में संदर्भ का उपयोग करने के बारे में पढ़ा था, और भाग्य के रूप में, ट्रिगर के साथ, इसकी आवश्यकता थी।

सबसे पहले हम दो टेबल बनाएंगे, एक हमारे इच्छित डेटा के साथ, फिर दूसरा समान कॉलम और एक पहचान कॉलम, दिनांक समय और प्रक्रिया नाम के साथ हमारे सभी परिवर्तनों को संग्रहीत करने के लिए।

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))

क्या ये सहायक था?

आगे हम टेबल पर एक ट्रिगर बनाएंगे। हम इसे किसी भी इंसर्ट या अपडेट के बाद चलाना चाहते हैं। यह ट्रिगर तब ऑडिट ऑडिट तालिका में एक पंक्ति जोड़ देगा जो रिकॉर्ड करेगा कि नया डेटा क्या है, परिवर्तन की तारीख और समय और प्रक्रिया (यदि हमने इसे डाला है) या कनेक्शन आईडी। ट्रिगर्स पर अधिक 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

क्या ये सहायक था?

अब हम टेबल को अपडेट करने के लिए कुछ स्टेटमेंट दे सकते हैं। पहले हम एक सामान्य इंसर्ट करते हैं, फिर एक इंसर्ट और एक अपडेट जिसमें संदर्भ पॉप्युलेट होता है।

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

परिणाम

आपके चयन विवरण में नीचे के अनुसार तीन पंक्तियाँ होनी चाहिए।

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

परिणाम

मैंने पाया कि इसका उपयोग करने का सबसे आसान तरीका संग्रहित प्रक्रियाओं में अद्वितीय प्रक्रिया नाम संलग्न करना था जो तालिकाओं को अद्यतन करते हैं, इस तरह आप ट्रैक कर सकते हैं कि डेटा परिवर्तन कहां से आया है।