sql server数据库触发器记录 数据库中的创建 删除 的操作日志

sql server数据库触发器记录 数据库中的创建 删除 的操作日志
1.创建记录日志表

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
/******
sql server数据库触发器记录 数据库中的创建 删除 的操作日志
*/

CREATE TABLE [dbo].[Trigger_ddl_record](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[MachineName] [varchar](256) NULL,
	[InstanceName] [varchar](256) NULL,
	[DbName] [varchar](256) NULL,
	[HostName] [varchar](256) NULL,
	[LoginName] [varchar](256) NULL,
	[ObejctType] [varchar](128) NULL,
	[SchemaName] [varchar](128) NULL,
	[ObjectName] [varchar](256) NULL,
	[EventType] [varchar](128) NULL,
	[Text] [text] NULL,
	[IP_Address] [nvarchar](100) NULL,
	[CreateDate] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

2.创建触发器[Trigger_ddl_record]

/****** Object:  DdlTrigger [Trigger_ddl_record]    Script Date: 2024/7/5 13:42:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




create TRIGGER [Trigger_ddl_record]
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
	CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
	CREATE_INDEX, DROP_INDEX,
	CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
	CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER,
	CREATE_VIEW, ALTER_VIEW, DROP_VIEW, 
	CREATE_SYNONYM, DROP_SYNONYM, 
	CREATE_SCHEMA, ALTER_SCHEMA, 
	CREATE_USER, ALTER_USER, 
	CREATE_ROUTE, ALTER_ROUTE --CREATE_ROLE, ALTER_ROLE, CREATE_LOGIN, ALTER_LOGIN 
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @data XML
	DECLARE @CLIENTUSER NVARCHAR(128)
	DECLARE @SPID NVARCHAR(128)
	DECLARE @SERVERNAME NVARCHAR(128)
	DECLARE @DBNAME NVARCHAR(128)
	DECLARE @DBID INT
	DECLARE @OBJNAME NVARCHAR(512)
	DECLARE @client_net_address NVARCHAR(32)
	
	
	
	--获得机器名与数据库实例名
	DECLARE @MachineName NVARCHAR(128)=CONVERT(NVARCHAR(128),SERVERPROPERTY('MachineName')) 
	DECLARE @InstanceName NVARCHAR(128)=CONVERT(NVARCHAR(128),ISNULL(SERVERPROPERTY('InstanceName'),'MSSQLSERVER'))

	

	SELECT @data = EVENTDATA()
	SELECT @SPID = @data.value('(/EVENT_INSTANCE/SPID)[1]','NVARCHAR(128)')
	SELECT @SERVERNAME = @data.value('(/EVENT_INSTANCE/ServerName)[1]','NVARCHAR(256)')
	SELECT @DBNAME = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]','NVARCHAR(128)')
	SELECT @OBJNAME = @data.value('(/EVENT_INSTANCE/ObjectName)[1]','NVARCHAR(128)')

	SELECT @DBID = DBID FROM SYS.SYSDATABASES WHERE NAME = @DBNAME 

	SELECT @CLIENTUSER=HOSTNAME FROM MASTER..SYSPROCESSES WHERE SPID=@SPID
	SELECT @client_net_address=client_net_address FROM sys.dm_exec_connections WHERE session_id = @SPID
	
	--INSERT INTO TMS.dbo.T_DB_Version_Details (POST_COMPUTER_NAME, SYS_DBID, UID,                                                            [SCHEMA],                             POST_TIME, EVENTTYPE,                                                      OBJECTTYPE,                                                     OBJECTNAME, COMMANDTEXT,                                                                 REMARK)
	--                               VALUES (@CLIENTUSER,        @DBID,    @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(256)'), CONVERT(nvarchar(256), CURRENT_USER), getdate(), @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(128)'), @data.value('(/EVENT_INSTANCE/ObjectType)[1]','NVARCHAR(128)'), @OBJNAME,   @data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'), '与此服务器连接的客户端的主机地址: '+@client_net_address)


INSERT INTO dbo.Trigger_ddl_record  (MachineName,InstanceName,DbName,HostName,LoginName,ObejctType,SchemaName,ObjectName,EventType,[Text],IP_Address,CreateDate)
SELECT 
@MachineName AS MachineName,
@InstanceName AS InstanceName,
@DBNAME AS DBNAME,
@CLIENTUSER AS HostName, 
--@DBID AS SYS_DBID, 
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(256)') AS LoginName,
@data.value('(/EVENT_INSTANCE/ObjectType)[1]','NVARCHAR(128)') AS OBJECTTYPE,
--CONVERT(nvarchar(256), CURRENT_USER) AS [SCHEMA],
@data.value('(/EVENT_INSTANCE/SchemaName)[1]','NVARCHAR(128)') AS SchemaName,
@OBJNAME AS OBJECTNAME, 
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(128)') AS EVENTTYPE,
@data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') AS [TEXT],
'客户端主机地址: '+@client_net_address AS IP_Address,
GETDATE() AS CreateDate




END



GO

ENABLE TRIGGER [Trigger_ddl_record] ON DATABASE
GO