May 23, 2008 at 1:12 pm
i'm looking for a way to extrapolate the DATABASE name
from the EVENTDATA function in a DDL trigger and I've tried
to get it with substring & charindex, but using the 2 together
is throwing me off. Perhaps it's the wrong approach, but wondering
if there is a better way.
really just trying to capture the space between 'database' & 'on'
CREATE DATABASE [newDataBase] ON PRIMARY
--CREATE TRIGGER.--------------------------------------
create trigger MyTrigDDL
on all server
for create_database
as
print 'create database issued.'
set nocount on
declare @object varchar(25)
set @object = '[' + (select substring (eventdata().value
('(/event_instance/tsqlcommand/commandtext)[1]','nvarchar(max)'),14, 20)) + ']'
select substring (eventdata().value
('(/event_instance/tsqlcommand/commandtext)[1]','nvarchar(max)'),13, 20) as 'dbname'
--basically select space starting at the 13'th character position and ending
--at the 20'th character position. this will not work obviously so thought of
--maybe including a CHARINDEX ((value, 'go')-2) not sure if thats correct,
--but don't know what else to try. am i even getting close? is this the right approach?
raiserror ('DDL trig test.', 16, 1)
rollback
print @object
go
--TEST THE TRIGGER.--------------------------------------
use master
go
CREATE DATABASE [newDataBase] ON PRIMARY
( NAME = N'newDataBase', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATAewDataBase.mdf'
, SIZE = 3072KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'newDataBase_log', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATAewDataBase_log.ldf'
, SIZE = 1024KB , FILEGROWTH = 10%)
GO
--DROP THE TRIGGER.--------------------------------------
drop trigger MyTrigDDL
on all server
go
--DROP THE DATABASE.--------------------------------------
drop database newDataBase
go
May 23, 2008 at 2:36 pm
Please try with this:
Create trigger MyTrigDDL
on all server
for create_database
as
print 'create database issued.'
set nocount on
declare @object varchar(100)
set @object = '[' +(select substring (eventdata().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]'
,'nvarchar(max)'),17,
CHARINDEX('ON',(eventdata().value
('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')))-18)) + ']'
raiserror ('DDL trig test.', 16, 1)
rollback
print @object
go
Hope it works for you.
Manu Jaidka
May 24, 2008 at 10:01 pm
This trigger text should be able to extract it for any Server-level trigger:
select
CAST(eventdata().query('/EVENT_INSTANCE/DatabaseName[1]/text()') as NVarchar(128))
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 3, 2008 at 9:21 am
thanks for the feedback! they both worked great!
September 24, 2018 at 3:30 pm
Can we get the datetime () and user login details as well from the EventData()?
Thanks.
September 24, 2018 at 4:12 pm
SQL-DBA-01 - Monday, September 24, 2018 3:30 PMCan we get the datetime () and user login details as well from the EventData()?
What's wrong with GETDATE and/or SYSDATETIME, etc and CURRENT_USER and SYSTEM_USER?
P.S. 10 year old topic, so none of these users may be active anymore. 😉
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 24, 2018 at 4:17 pm
SQL-DBA-01 - Monday, September 24, 2018 3:30 PMCan we get the datetime () and user login details as well from the EventData()?
You can get the login name and the post date from most events but what you can get depends on the event. You can find the xml definition by searching on a server, PC with SQL Server installed and look for event.xsd for the schema definition. Or refer to:
XML schema for EventData function
Sue
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply