Триггер для sql 2005

автор: автор автор
16 декабря, 2015

Создаем таблицу под лог

USE [master]
GO
/****** Объект:  Table [dbo].[DDLTriggerAlterLogin]    Дата сценария: 02/25/2013 15:22:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DDLTriggerAlterLogin](
	[EventType] [nvarchar](100) NULL,
	[ObjectName] [nvarchar](2000) NULL,
	[ObjectType] [nvarchar](2000) NULL,
	[LoginName] [nvarchar](max) NULL,
	[LoginType] [nvarchar](max) NULL,
	[SPID] [nvarchar](max) NULL,
	[PostTime] [datetime] NULL,
	[host] [nvarchar](128) NULL
) ON [PRIMARY]

Создаём триггер для ALTERDATABASE

/****** Object:  DdlTrigger [DDLTriggerAlterDatabase]    Script Date: 03/28/2016 10:59:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================

-- 1) исключение срабатываний для служебной учетной записи head_offive\mssqlserver, log shipping

-- =============================================
CREATE TRIGGER [DDLTriggerAlterDatabase] ON ALL SERVER AFTER ALTER_DATABASE AS

DECLARE @eventdata NVARCHAR(MAX)
DECLARE @loginname NVARCHAR(MAX)

SET @eventdata = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(max)'))
SET @loginname = 'HEAD_OFFICE\MSSQLServer'
IF ( @eventdata!=@loginname )
BEGIN
INSERT INTO master.dbo.DDLTriggerAlterLogin
SELECT 
EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') 'EventType' ,
EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(2000)') 'ObjectName' ,
EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(2000)') 'ObjectType' ,
EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(max)') 'LoginName',
EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') 'LoginType',
EVENTDATA().value('(/EVENT_INSTANCE/SPID)[1]','nvarchar(max)') 'SPID',
EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]','datetime') 'PostTime',
HOST_NAME() AS 'host'
END



GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

ENABLE TRIGGER [DDLTriggerAlterDatabase] ON ALL SERVER
GO

Тригер для ALTERLOGIN

CREATE TRIGGER DDLTriggerAlterLogin ON ALL SERVER AFTER ALTER_LOGIN AS
INSERT INTO master.dbo.DDLTriggerAlterLogin
SELECT 
EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') 'EventType' ,
EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(2000)') 'ObjectName' ,
EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(2000)') 'ObjectType' ,
EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(max)') 'LoginName',
EVENTDATA().value('(/EVENT_INSTANCE/LoginType)[1]','nvarchar(max)') 'LoginType',
EVENTDATA().value('(/EVENT_INSTANCE/SPID)[1]','nvarchar(max)') 'SPID',
EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]','datetime') 'PostTime',
host_name() as 'host'
GO
CREATE trigger DML_DDLTriggerAlterLogin ON master.dbo.DDLTriggerAlterLogin AFTER INSERT AS
EXECUTE msdb.dbo.sp_send_dbmail
   @profile_name  = 'atrans',        
   @recipients  = 'email',
   @subject  = 'сработал триггре DDLTriggerAlterLogin',
	@body = 'select 
EventType
,ObjectName
,ObjectType
,LoginName
,LoginType
,SPID
,posttime
,host 
from master.dbo.DDLTriggerAlterLogin'

Тригер для CRATEDATABASE

/****** Object:  DdlTrigger [DDLTriggerCreateDatabase]    Script Date: 03/28/2016 11:01:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [DDLTriggerCreateDatabase] ON ALL SERVER AFTER CREATE_DATABASE AS
INSERT INTO master.dbo.DDLTriggerAlterLogin
SELECT 
EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') 'EventType' ,
EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(2000)') 'ObjectName' ,
EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(2000)') 'ObjectType' ,
EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(max)') 'LoginName',
EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') 'LoginType',
EVENTDATA().value('(/EVENT_INSTANCE/SPID)[1]','nvarchar(max)') 'SPID',
EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]','datetime') 'PostTime',
HOST_NAME() AS 'host'


GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

ENABLE TRIGGER [DDLTriggerCreateDatabase] ON ALL SERVER
GO

Тригер для DROPDATABASE

/****** Object:  DdlTrigger [DDLTriggerDropDatabase]    Script Date: 03/28/2016 11:03:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [DDLTriggerDropDatabase] ON ALL SERVER AFTER DROP_DATABASE AS
INSERT INTO master.dbo.DDLTriggerAlterLogin
SELECT 
EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') 'EventType' ,
EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(2000)') 'ObjectName' ,
EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(2000)') 'ObjectType' ,
EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(max)') 'LoginName',
EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') 'LoginType',
EVENTDATA().value('(/EVENT_INSTANCE/SPID)[1]','nvarchar(max)') 'SPID',
EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]','datetime') 'PostTime',
HOST_NAME() AS 'host'


GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

ENABLE TRIGGER [DDLTriggerDropDatabase] ON ALL SERVER
GO

отключение триггера

disable trigger DML_DDLTriggerAlterLogin ON master.dbo.DDLTriggerAlterLogin
go
enable trigger DML_DDLTriggerAlterLogin ON master.dbo.DDLTriggerAlterLogin

disable trigger DDLTriggerAlterLogin ON ALL SERVER
go
enable trigger DDLTriggerAlterLogin ON ALL SERVER

Триггер для sql 2005

Similar Posts:

Добавить комментарий Отменить ответ