November 8, 2010 at 12:24 pm
Hi All
I have created DDL trigger on my SQL 2008 R2 SQL instances, this are database level triggers to capture changed that are been made on the objects, information has been stored on a separate database on a table.
When I created my trigger I am finding a statement which enables and then disables the trigger as you can see on the trigger below, is this the normal behaviour, or am I missing something obvious
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [DDLTrg] ON Database -- Create Database DDL Trigger
FOR DDL_DATABASE_LEVEL_EVENTS -- Trigger will raise when creating a Table
AS
SET NOCOUNT ON
DECLARE @xmlEventData XML
-- Capture the event data that is created
SET @xmlEventData = eventdata()
INSERT INTO DBTest.dbo.DBLOG
(EventTime,EventType,ServerName,LoginName,DatabaseName,SchemaName,ObjectType,ObjectName,CommandText,SPID)
SELECT REPLACE(CONVERT(VARCHAR(50), @xmlEventData.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' ') ,
CONVERT(VARCHAR(max), @xmlEventData.query('data(/EVENT_INSTANCE/EventType)')),
CONVERT(VARCHAR(max), @xmlEventData.query('data(/EVENT_INSTANCE/ServerName)')),
CONVERT(VARCHAR(max), @xmlEventData.query('data(/EVENT_INSTANCE/LoginName)')),
CONVERT(VARCHAR(max), @xmlEventData.query('data(/EVENT_INSTANCE/DatabaseName)')),
CONVERT(VARCHAR(max), @xmlEventData.query('data(/EVENT_INSTANCE/SchemaName)')),
CONVERT(VARCHAR(max), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectType)')),
CONVERT(VARCHAR(max), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectName)')),
CONVERT(varchar(max), @xmlEventData.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)')) ,
CONVERT(VARCHAR(max), @xmlEventData.query('data(/EVENT_INSTANCE/SPID)'))
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
DISABLE TRIGGER [DDLTrg] ON DATABASE
GO
Enable TRIGGER [DDLTrg] ON DATABASE
Thanks in Advance for all your help
Cheers 🙂
November 8, 2010 at 12:27 pm
It's normal for the "script as" on DDL triggers.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 8, 2010 at 1:04 pm
November 8, 2010 at 2:14 pm
pavan_srirangam (11/8/2010)
you are missing Go.
No. There are no missing "go" separators in there.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 8, 2010 at 2:52 pm
Thanks Guys. Thanks a lot for clearing my doubts
Cheers
🙂
November 8, 2010 at 4:42 pm
Don't just rely on the ddl trigger taking for granted it is active.
Set up a sqlserver event monitoring, so you detect when the trigger is being disabled ("disable trigger" currently is not a ddl event).
We noticed our devs using VS2010 being able to disable ddl triggers - because they are dbo but are only alowed to create and modify sprocs and functions -
Now we've setup this event monitoring which re-enables our ddl triggers (specifically by name), unless the executor is sysadmin member.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 9, 2010 at 4:50 am
That is a good hint mate, thanks, I dint think through to that extent 🙂 .
Cheers 😀
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply