SQL Server uses triggers to capture the session information of DML operations [instance] and triggers dml

SQL Server uses triggers to capture the session information of DML operations [instance] and triggers dml

Background

When we met such a requirement last week, the maintenance personnel found that the data of a table was often modified due to historical reasons, lack of documents, and frequent changes of maintenance personnel, as a result, they are not completely familiar with the system and business, and they are not completely clear which systems and applications will operate on the Data in this table. Now they want to find out which servers and which applications will INSERT and UPDATE the table. Now, how can we solve this problem?

Solution

Since the database version is the standard version, we chose to use triggers to capture information about sessions for DML operations, such as Host_Name and Program_Name. The trigger is selected simply and directly. First, create a table named TEST. Suppose we want to monitor which application servers are there and which applications will perform INSERT and UPDATE operations on the table TEST.

USE [AdventureWorks2014]GO IF NOT EXISTS (SELECT 1 FROM sys.sysobjects WHERE id=object_id(N'[dbo].[TEST]') AND OBJECTPROPERTY(id, N'IsTable')=1 )BEGINCREATE TABLE [dbo].[TEST](  [OBJECT_ID] [INT] NOT NULL,  [NAME] [VARCHAR](8) NULL,  CONSTRAINT PK_TEST  PRIMARY KEY (OBJECT_ID)) ENDGO INSERT INTO dbo.TESTSELECT 1, 'kerry' UNION ALLSELECT 2, 'jimmy'

Then we will add several fields [HOST_NAME], [PROGRAM_NAME], and LOGIN_NAME in the table to record the session information for the last modification of the record. In addition, we will create the trigger TRG_TEST to update these fields.

ALTER TABLE TEST ADD [HOST_NAME] NVARCHAR(256)ALTER TABLE TEST ADD [PROGRAM_NAME] NVARCHAR(256);ALTER TABLE TEST ADD LOGIN_NAME NVARCHAR(256); CREATE TRIGGER TRG_TEST ON dbo.TEST AFTER INSERT,UPDATEAS  IF (EXISTS(SELECT 1 FROM INSERTED))BEGIN   UPDATE dbo.TEST  SET   dbo.TEST.[HOST_NAME] = ( SELECT host_name                   FROM  sys.dm_exec_sessions                   WHERE session_id = @@SPID                  ) ,      dbo.TEST.PROGRAM_NAME = ( SELECT  program_name                   FROM   sys.dm_exec_sessions                   WHERE   session_id = @@SPID                  ) ,      dbo.TEST.LOGIN_NAME = ( SELECT login_name                  FROM  sys.dm_exec_sessions                  WHERE  session_id = @@SPID                 )  FROM  dbo.TEST t      INNER JOIN INSERTED i ON t.OBJECT_ID = i.OBJECT_IDENDGO

Next, let's perform a simple test, as shown below, to insert and update a record in the distribution.

INSERT INTO dbo.TEST(OBJECT_ID,NAME)SELECT 3,'ken' UPDATE dbo.TEST SET NAME='Richard' WHERE OBJECT_ID=2;

As shown below, because I only use SSMS to update and insert data, Microsoft SQL Server Management Studio-Query is captured.

This method also has a drawback, that is, if the SQL of the application is not robust enough, adding a field will cause problems in the previous application. For example, the application has the following SQL statement. When a field is added, an error is reported.

INSERT INTO dbo.TESTSELECT 3,'ken'

Therefore, this solution is not feasible and increases the risk of bugs in applications. In fact, we can create a new table. When the original table TEST has an INSERT or UPDATE operation, the session process information is captured through the trigger and then inserted into the table (note, the new table contains the primary key field of the source table. For example, if the primary key field of TEST is OBJECT_ID, then OBJECT_ID is included in the following table)

USE [AdventureWorks2014]GO DROP TABLE dbo.[TRG_TEST_SESSION_INFO];GO IF NOT EXISTS (SELECT 1 FROM sys.sysobjects WHERE id=object_id(N'[dbo].[TRG_TEST_SESSION_INFO]') AND OBJECTPROPERTY(id, N'IsTable')=1 )BEGINCREATE TABLE [TRG_TEST_SESSION_INFO](  [ID]        INT NOT NULL IDENTITY(1,1),  [OBJECT_ID]    INT,  [HOST_NAME]    NVARCHAR(256),  [PROGRAM_NAME]   NVARCHAR(256),  [LOGIN_NAME]    NVARCHAR(256),  CONSTRAINT PK_TRG_TEST_SESSION_INFO  PRIMARY KEY (ID)) ENDGO CREATE TRIGGER TRG_TEST_SESSION ON dbo.TESTAFTER INSERT ,UPDATEAS IF (EXISTS(SELECT 1 FROM INSERTED))BEGIN   /*  INSERT INTO dbo.[TRG_TEST_SESSION_INFO]  SELECT (SELECT I.OBJECT_ID FROM INSERTED I), HOST_NAME,program_name,login_name                   FROM  sys.dm_exec_sessions                   WHERE session_id = @@SPID*/  INSERT INTO dbo.[TRG_TEST_SESSION_INFO]  SELECT I.OBJECT_ID, S.HOST_NAME,S.PROGRAM_NAME,S.LOGIN_NAME                   FROM  sys.dm_exec_sessions s,                      Inserted i                   WHERE session_id = @@SPID  ENDGO

After running for a short period of time, if you have found out which servers and applications will operate on these tables, you must immediately delete these tables and triggers to avoid long running, performance is affected.

The above SQL Server implementation uses triggers to capture the session information for DML operations [instance] is all the content shared by Alibaba Cloud. I hope you can give us a reference and support for the help house.