sql数据库级触发器的应用

比眉伴天荒 2022-08-06 02:07 154阅读 0赞

sql数据库级触发器的应用

适用范围：Sql2005及以上

1.记录数据库结构的所有变化  
CREATE TABLE \[dbo\].\[tblAuditLog\](  
\[ID\] \[int\] IDENTITY(1,1) NOT NULL,  
\[Command\] \[nvarchar\](1000) NULL,  
\[PostTime\] \[nvarchar\](24) NULL,  
\[HostName\] \[nvarchar\](100) NULL,  
\[LoginName\] \[nvarchar\](50) NULL,  
\[CreateTime\] \[datetime\] NOT NULL CONSTRAINT \[DF\_AuditLog\_CreateTime\] DEFAULT (getdate())  
) ON \[PRIMARY\]  
GO

CREATE TRIGGER Audit ON DATABASE  
FOR DDL\_DATABASE\_LEVEL\_EVENTS  
AS  
DECLARE @data xml  
DECLARE @cmd NVARCHAR(1000)  
DECLARE @posttime NVARCHAR(24)  
DECLARE @spid NVARCHAR(6)  
DECLARE @loginname NVARCHAR(100)  
DECLARE @hostname NVARCHAR(100)  
SET @data=EVENTDATA()  
SET @cmd= @data.value('(/EVENT\_INSTANCE/TSQLCommand/CommandText)\[1\]','NVARCHAR(1000)')  
SET @cmd=LTRIM(RTRIM(REPLACE( @cmd,'','')))  
SET @posttime= @data.value('(/EVENT\_INSTANCE/PostTime)\[1\]','NVARCHAR(24)')  
SET @spid= @data.value('(/EVENT\_INSTANCE/SPID)\[1\]','nvarchar(6)')  
SET @loginname= @data.value('(/EVENT\_INSTANCE/LoginName)\[1\]','NVARCHAR(100)')  
SET @hostname=HOST\_NAME()  
INSERT INTO dbo.tblAuditLog(Command,PostTime,HostName,LoginName)  
VALUES( @cmd, @posttime, @hostname, @loginname)  
GO

2.禁止在数据库上增删改表结构和存储过程  
CREATE TRIGGER STOP\_DDL\_on\_Table\_and\_PROC  
ON DATABASE  
FOR CREATE\_TABLE,DROP\_TABLE,ALTER\_TABLE,CREATE\_PROCEDURE,ALTER\_PROCEDURE,DROP\_PROCEDURE  
AS  
  
SELECT EVENTDATA().value('(/EVENT\_INSTANCE/TSQLCommand/CommandText)\[1\]','nvarchar(max)')  
PRINT 'You are not allowed to CREATE,ALTER and DROP any Tables and Procedures'  
ROLLBACK;

3.禁止在服务器上增删改数据库  
CREATE TRIGGER STOP\_DDL\_on\_Table\_and\_PROC  
ON ALL SERVER FOR CREATE\_DATABASE,ALTER\_DATABASE,DROP\_DATABASE  
AS  
PRINT 'You are not allowed to CREATE,ALTER and DROP any Databases'  
ROLLBACK;