适用于MS SQL的库触发器,理论上是通用的
/*
额,不怎么会用这个些文章,感觉有点麻烦。
创建日期:2021年11月22日16:19:09
触发器·库触发器·通用
注意:触发器不适用于数据量非常大的表,可能会影响效率。
建议:借鉴写自己更好的,别人的不一定适合自己实际,自己弄出更好的才最适合自己。
*/
/*修改记录的表,先执行。
CREATE TABLE [dbo].[KXGJL]( [ID] [int] IDENTITY(1,1) NOT NULL, [ip] [varchar](100) NULL, [EventType] [varchar](300) NULL, [TSQLCommand] [varchar](max) NULL,[TSQL2] [varchar](max) NULL, [PostTime] [datetime] NULL, [ServerName] [varchar](150) NULL, [LoginName] [varchar](150) NULL, [UserName] [varchar](100) NULL, [SchemaName] [varchar](100) NULL, [DatabaseName] [varchar](100) NULL, [ObjectName] [varchar](100) NULL, [ObjectType] [varchar](100) NULL,
[EventData] [xml] NULL, [createdate] [datetime] NULL DEFAULT (getdate())
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
--
*/
USE master
--定义变量
DECLARE @DBNAME VARCHAR(MAX)
--为游标赋值
DECLARE DBNAME CURSOR FOR SELECT NAME FROM sys.sysdatabases WHERE DBID =5 --系统库固定占用1~4,用户数据库从5开始
OPEN DBNAME
--游标赋值给变量
FETCH NEXT FROM DBNAME INTO @DBNAME
WHILE( @@FETCH_STATUS <>-1 )
BEGIN
EXEC('USE '+@DBNAME +'
EXEC(''
CREATE TRIGGER [trg_dll_'+@DBNAME+'] ON DATABASE
WITH ENCRYPTION
FOR ALTER_PROCEDURE,DROP_PROCEDURE,ALTER_FUNCTION,DROP_FUNCTION,ALTER_TABLE,DROP_TABLE,CREATE_TABLE
AS
DECLARE @DI TABLE(ET VARCHAR(200),PT VARCHAR(200),EI VARCHAR(MAX))
INSERT INTO @DI EXEC(''''DBCC INPUTBUFFER(@@SPID)'''')
DECLARE @EventData AS XML;
SELECT @EventData = EVENTDATA();
declare @ip varchar(100)
select @ip=CLIENT_NET_ADDRESS FROM SYS.DM_EXEC_CONNECTIONS WHERE SESSION_ID = @@SPID
INSERT INTO CHBASE.dbo.KXGJL(
IP,
ServerName,EventType,TSQLCommand,TSQL2,PostTime,LoginName,UserName,SchemaName,DatabaseName,ObjectName,ObjectType,[EventData]
)
VALUES(
@ip,
@EventData.value(''''(/EVENT_INSTANCE/ServerName)[1]'''',''''varchar(50)''''),
@EventData.value(''''(/EVENT_INSTANCE/EventType)[1]'''',''''varchar(100)''''),
@EventData.value(''''(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]'''',''''varchar(max)''''),
(SELECT TOP 1 ISNULL(EI,'''''''') FROM @DI),
@EventData.value(''''(/EVENT_INSTANCE/PostTime)[1]'''',''''datetime''''),
@EventData.value(''''(/EVENT_INSTANCE/LoginName)[1]'''',''''varchar(150)''''),
@EventData.value(''''(/EVENT_INSTANCE/UserName)[1]'''',''''varchar(100)''''),
@EventData.value(''''(/EVENT_INSTANCE/SchemaName)[1]'''',''''varchar(100)''''),
@EventData.value(''''(/EVENT_INSTANCE/DatabaseName)[1]'''',''''varchar(100)''''),
@EventData.value(''''(/EVENT_INSTANCE/ObjectName)[1]'''',''''varchar(100)''''),
@EventData.value(''''(/EVENT_INSTANCE/ObjectType)[1]'''',''''varchar(100)''''),
@EventData
)
'')
')
--游标继续下一个,如果没有这个语句,会死循环
FETCH NEXT FROM DBNAME INTO @DBNAME
END
--关闭,释放游标
CLOSE DBNAME
DEALLOCATE DBNAME