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;