DeadLock Issue

I serveral triggers in a table that is accessed by mutilple users in the application I am writing. I have come across a deadlock issue and have tried to resolve the issue by breaking down the triggers into many much smaller trans with no success. In general terms, can some one suggest some technique I am missing that I can try to avoid this issue .There are 3 techniques that can be used to help you avoid deadlocks. Number one is to ensure the same order of access to objects within a transaction. This comes from the definition of the deadlock itself:

TableA has a lock placed by user1 who's trying to access TableB,while user2 has a lock on TableB and trying to access tableA.

To break this vicious circle you need to structure all transactions in such a way that TableA is always accessed first.

Number two is to make your transactions as short as possible, without violation business logic and business requirements of course.

Number three - and here there will be a lot of screaming, yelling, calling names, - but reality will prove them all wrong,- lower transaction isolation level.|||rdjabarov and I disagree on that last point. I keep insisting on correct answers, he prefers easy answers.

In reality, if what you need is a quick answer the rand() function will often get you there orders of magnitude faster than you can possibly get the correct answer, with no locking issues at all.

-PatP|||Number three - and here there will be a lot of screaming, yelling, calling names, - but reality will prove them all wrong,- lower transaction isolation level.

When the users are screaming that: WE CAN'T COMPLETE OUR WORK, IT'S TOO SLOW AND WHATE THE HECK IS THIS timeout-expired IN OUR APPLICATION?
I don't see anything rising in the PERFMON at my dbserver but LOCKREQUEST/SEC (constantly above thousand figures) and high processor queue length due to blocking.
What now!
Same is the case when there's deadlock error, sp_lock contains more than hundereds of rows.
I'm just venting and cryin for the "Number Three advice"

One more thing that's rather strange and confusing:
why isn't there any READPAST isolation level. I guess it's by default in ORACLE.
[user1]
BEGIN TRAN
UPDATE mytable SET a=b
..
[user2]
select * from mytable
all previously committed rows r returned.

But in SQL, user2 waits :confused: , rather have to put select * from with (readpast) from mytable.
I guess [readpast] is better than [nolock]

Howdy! ;)|||OK, Pat, it's all circumstantial, let's agree at least on something (otherwise one of us will have to choose a different forum, and that's not what I intend to do, nor do I encourage for you to consider). When my app references Countries+States+Zips tables while displaying information to 100+ simalteneous users at the call center, don't you think it would be an overkill to use READ COMMITTED default isolation level for SQL? What are the chances that a call is placed to a person that lives in a country that has just been discovered, recorded in Countries table, associated States entries made, and their postal system delivered zip mapping to the call center, probably along with a list citizens that immediately received telephone service and became potential customers? It borders with absurdity, but that's what I feel when I see so much passion in your postings about NOLOCK.

EDITED: And how can RAND() help you?|||Setting connection level lock handling to allow permissive locking (lock handling like what NOLOCK gives) is one thing, to override the locking for a specific table within a transaction (actually using NOLOCK) is entirely different even though both situations affect the handling of locks.

Setting the transaction isolation level for the spid says that locking isn't important for this thread (spid) and is usually quite safe. You won't inadvertantly clobber important data due to a mangled or misprocessed read/write combination either within or between threads. This is a fine solution for the kind of problem you proposed where multiple users might need browse access to the same data.

Setting the lock handling for a specific table to a lower level than the thread that uses it requires a lot of knowledge about the entire system involved in order to have a prayer of being safe. I've had to do this before, but it isn't anything I'm comfortable with and wouldn't recommend for anyone that I didn't really, really hate. It takes ongoing monitoring to prevent a rogue process from upsetting the delicate balance that it depends upon.

I don't have any problem with setting the transaction isolation level, but am really, really wary of using NOLOCK explicitly. There are cases where it can be quite necessary... These are not the kind of solutions that I recommend for the long haul. They might fill a particular need under very specific circumstances, but they are dangerous.

-PatP|||And how about EXPLICITLY specifying READPAST in queries, ain't it better.?|||And how about EXPLICITLY specifying READPAST in queries, ain't it better.?I can't see any way that it is better from the standpoint of avoiding lost data... You can do as you wish, just be forewarned that I've done exactly what you are describing and really, REALLY regret it. Indiscriminantly ignoring locking is an easy solution, but like most easy solutions, it can be VERY dangerous.

If you set the transaction isolation level down, then everything in that transaction is processed at the lower level. I don't know of any way to hurt yourself that way. Setting some (even one) table to a lower locking level than the transaction isolation level is dangerous from the standpoint of "lost" data.

-PatP|||Pat, relax, there are more important things in life...like beer ;) Go get one, willya?!|||If you set the transaction isolation level down, then everything in that transaction is processed at the lower level. I don't know of any way to hurt yourself that way. Setting some (even one) table to a lower locking level than the transaction isolation level is dangerous from the standpoint of "lost" data.

No doubt the teacher is always right

and guru, i love you! :)|||Pat, relax, there are more important things in life...like beer ;) Go get one, willya?!No can do this week. I'm at training, so I'm always the designated driver. This week needs "Commando Driving 301" skill almost all the time in Chicago and the burbs. They've done some quite "creative" things on both 88 and 294, making getting from place to place a whole new experience!

Last night I actually made better time on Ogden (from 294 to Naperville Road!) than a friend did on I-88 !!!

-PatP

Deadlock & probably other problem

Hi,

We are having sql server 7. But recently when we executing a batch of SQL in that some updates were there & those updates causes triggers needs to be fired. But batch gets teminated in between. We checked the error logs & found that Few other processes are running continously like SPid 14 etc which are opening & closing the datafiles for pubs, & northwind database. Why? Is this a server settings problem Or Virus?

For deadlock is nested triggeres becomes problem after 3-4 level deep on multiple tables?

ThanksThere are a number of things which can make deadlocks more frequent.
One is long running transactions. Triggers cause the transaction to extend for the length of the trigger and also make the actual update less likely to be efficient so can cause the problem - nested triggers even more so.

>> opening & closing the datafiles
Which version do you have? It sounds like these databases are set to autoclose which is a bit odd - I would have said maybe it's a checkpoint but spid 14 doesn't sound like a system spid. Do you have anything monitoring databases or maybe backup software.

Try running this sp
http://www.nigelrivett.net/sp_nrSpidByStatus.html
it will tell you what the last statement executed by a spid is.
You could also use the profiler to see what is happenning but that will also slow down the system.|||Hi
Thanks For Reply.
For Triggers i reduced the transactions time & batch size.

For opening closing datafile the content in log files were like this

2003-07-08 08:39:08.00 spid33 Starting up database 'Northwind'.
2003-07-08 08:39:08.00 spid33 Opening file d:\SQLData\DATA\northwnd.mdf.
2003-07-08 08:39:08.12 spid33 Opening file d:\SQLData\DATA\northwnd.ldf.
2003-07-08 08:39:08.39 spid33 Closing file d:\SQLData\DATA\northwnd.mdf.
2003-07-08 08:39:08.43 spid33 Closing file d:\SQLData\DATA\northwnd.ldf.
2003-07-08 08:39:08.45 spid33 Starting up database 'pubs'.
2003-07-08 08:39:08.45 spid33 Opening file d:\SQLData\DATA\pubs.mdf.
2003-07-08 08:39:08.46 spid33 Opening file d:\SQLData\DATA\pubs_log.ldf.
2003-07-08 08:39:08.62 spid33 Closing file d:\SQLData\DATA\pubs.mdf.
2003-07-08 08:39:08.65 spid33 Closing file d:\SQLData\DATA\pubs_log.ldf.

This happening continously. In this case the SPID is 33.

Thanks|||If you run the following command in Query Analyzer:

sp_dboption pubs

Does the output include AutoClose?|||Just set the databases to not autoclose and it should get rid of that.

Use profiler to log any accesses to those databases and you will see why it's happenning.

Sounds like there is some monitoring going on somewhere - maybe someone keeps clicking on things in enterprise manager or has a gui which tries to get info from the databases.

DDL triggers on create Logins

Hi all,
I have setup a DDL trigger for server logins. If I drop a user my trigger fires off an email with the EVENTDATA().value. If I create a new user the Eventdata is null. I am using the code below:
TIA,
Joe

DROP TRIGGER ddl_trig_login
ON ALL SERVER
GO
CREATE TRIGGER ddl_trig_login
ON ALL SERVER
FOR DDL_LOGIN_EVENTS
AS
declare @.user varchar(100),@.event varchar(1000), @.subj varchar(100)
select @.user = SUSER_SNAME()
Select @.subj = 'Login Event Issued from '+@.user
select @.event = @.user+' committed the following event on ENSQLD1_2005: '+
isnull((SELECT EVENTDATA().value'(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')),' ')+' Please email the group with the SIR# for this action.'
EXEC msdb.dbo.sp_send_dbmail
@.profile_name = 'SQL2005mail',
@.recipients = 'j.f@.myco.com',
@.body = @.event,
@.subject = @.subj
;

TSQLCommand text is not available for CREATE LOGIN, so you won't be able to see the data. But you can always see other useful information through EVENTDATA.value(). For example

select @.event = @.user+' committed the following event on ENSQLD1_2005: '+
EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)') +
EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)') +
...

|||Thank you,
I was able to trap the new user name and the create login eventtype. Now I have the triggers emailing me on any drop of user or create of one.

Do you know what event gets fired off when I change the permissions of a user? I thought it would be Alter_login, but when I grant rights to a DB to a user I dont get an email.

Thanks again!
Joe|||

> Do you know what event gets fired off when I change the permissions of a

> user?

Well, did you try running profiler and inspect the commands sent?

DDl Triggers for tables...

Hi,

I have a scenario in which I need to restrict schema changes for around 5 tables only in a database. When changes are done to the other tables, it should be allowed. I am planning to use DDL Trigger. But DDL Trigger is having only 2 options in the ON Clause like ON DATABASE and ON ALL SERVERS. If i use On Database I will not be able to make modifications in the other tables.

Is there any way i could achieve my requirement?

Regards,

Swapna.B.

Move the thread to "SQL Server Database Engine" forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=93&SiteID=1|||

If you define your trigger on the DDL_TABLE_EVENTS event group then, within the trigger, you can parse the EventData function's value to return the following values when an ALTER TABLE statement is issued:

<EVENT_INSTANCE>
 <EventType>type</EventType>
 <PostTime>date-time</PostTime>
 <SPID>spid</SPID>
 <ServerName>name</ServerName>
 <LoginName>name</LoginName>
 <UserName>name</UserName>
 <DatabaseName>name</DatabaseName>
 <SchemaName>name</SchemaName>
 <ObjectName>name</ObjectName>
 <ObjectType>type</ObjectType>
 <TSQLCommand>command</TSQLCommand>
</EVENT_INSTANCE>

Inside the trigger's code you should check to see if the ObjectName and SchemaName values match those of any of the tables that you want to preserve and then issue a rollback command if necessary.

Check out the EVENTDATA Function topic in BOL for more info.

Chris

|||

Hi,

Thanks a lot for the help provided. I have achieved the requirement by using eventdata function and validating the values in a separate sp.

I have the DDL trigger which calls a stored procedure. This SP does the segregating of values from eventdata function , validating those values and commits or rollsback according to the objectname retrieved. Now its working fine. But i face one error like the one below.

When ever the DDL Trigger is executed, it throws an error like

Msg 3609, Level 16, State 2, Line 1

The transaction ended in the trigger. The batch has been aborted.

The transactions are getting completed successfully but this error occurs everytime we try to make changes to any table in the database.

What is the reason for this error? Kindly let me know the way to avoid it.

One more point here is when we try to execute a batch of alter statements without go command in the database in which the DDL trigger is present, Only the first one gets executed other statements are not getting considered. Is this anyway related to the error above?

Thanks and Regards,

Swapna.B.

|||

Could you post the definitions of both your trigger and your stored proc?

Chris

|||

Hi Chris,

Here is the definition of my trigger and Stored Procedure.

Trigger :

USE [Jeux]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create trigger [DDL_TRG_DB] on database for ALTER_TABLE as

set ANSI_NULLS ON

set ANSI_PADDING ON

set ANSI_WARNINGS ON

set ARITHABORT ON

set CONCAT_NULL_YIELDS_NULL ON

set NUMERIC_ROUNDABORT OFF

set QUOTED_IDENTIFIER ON

declare @.EventData xml

set @.EventData=EventData()

exec sp_Sample @.EventData, 1

GO

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

ENABLE TRIGGER [DDL_TRG_DB] ON DATABASE

Stored Procedure :

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[sp_Sample]

(

@.EventData xml

,@.procmapid int

)

AS

begin

set nocount on

if is_member('db_owner') <> 1

begin

raiserror (21050, 16, -1)

return (1)

end

-- validate the procmapid

if @.procmapid not in (1,2,3,4)

begin

raiserror(15021, 16, -1, '@.procmapid')

Rollback Transaction

Return (1);

end

declare @.object_name sysname

,@.object_owner sysname

,@.qual_object_name nvarchar(512) --qualified 3-part-name

,@.objid int

,@.objecttype varchar(32)

,@.encrypted nvarchar(32)

,@.pass_through_scripts nvarchar(max)

,@.eventDoc int

,@.db_name sysname

,@.targetobject nvarchar(51)

set @.targetobject=N''

-- parse event data

select @.object_name = event_instance.value('ObjectName[1]', 'sysname')

,@.object_owner = event_instance.value('SchemaName[1]', 'sysname')

,@.objecttype = event_instance.value('ObjectType[1]', 'varchar(32)')

,@.encrypted = event_instance.value('(TSQLCommand/SetOptions/@.ENCRYPTED)[1]', 'nvarchar(32)')

,@.pass_through_scripts = event_instance.value('(TSQLCommand/CommandText)[1]', 'nvarchar(max)')

,@.targetobject = event_instance.value('TargetObjectName[1]', 'nvarchar(512)')

FROM @.EventData.nodes('/EVENT_INSTANCE') as R(event_instance)

select @.qual_object_name = QUOTENAME(@.object_owner) + N'.' + QUOTENAME(@.object_name)

select @.objid = object_id(@.qual_object_name)

select @.db_name=db_name()

select @.pass_through_scripts = sys.fn_replgetparsedddlcmd(@.pass_through_scripts

,N'ALTER'

,@.objecttype

,@.db_name

,@.object_owner

,@.object_name

,@.targetobject)

if UPPER(@.objecttype) != N'TABLE' and UPPER(@.objecttype) != N'TRIGGER'

begin

select @.pass_through_scripts = N'ALTER ' + @.objecttype + N' '

+ @.qual_object_name + N' '

+ @.pass_through_scripts

end

If (@.procmapid = 1)

begin

IF(@.object_name in (Select Article from MSsubscription_articles))

begin

Print 'Alter table Statements are not allowed in this table.'

Rollback Transaction

end

Else

begin

Commit Transaction

Print 'Transaction Commited!!!!!'

end

end

end

GO

Kindly check and let me know.

Thanks and Regards,

Swapna.B.

|||

Try removing 'COMMIT TRANSACTION' from the second BEGIN END block at the end of your stored proc, see below, leave ROLLBACK TRANSACTION in place.

Chris

If (@.procmapid = 1)

BEGIN

IF(@.object_name in (SELECT Article from MSsubscription_articles))

BEGIN

Print 'Alter table Statements are not allowed in this table.'

Rollback Transaction

end

--Else

--BEGIN

--Commit Transaction

--Print 'Transaction Commited!!!!!'

--end

end

DDL Triggers

Hi,
I am trying to write a DDL Trigger so that whenever someone creates or drops
Database I store information somewhere.
My Trigger is working, now I am trying to make it more useful by extracting:
* "Name" of the database being dropped or created
* Name of the User performing the action ( login account)
* And time the action was performed.
CREATE TRIGGER ddl_trig_database
ON ALL SERVER
FOR CREATE_DATABASE
AS
PRINT 'Database Created.'
INSERT INTO AuditDB.dbo.dbAudit (userName, dbName, actionDate, action)
values (', ', ', 'DB Created')
GO
How do Iobtain userName, dbName, actionDate within the trigger ?
ThanksIt is a little bit more complicated than that. Take a look at the Eventdata
function in BOL and some of the examples there. Eventdata is used to return
the information you are looking for.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"news.microsoft.com" wrote:
> Hi,
> I am trying to write a DDL Trigger so that whenever someone creates or drops
> Database I store information somewhere.
> My Trigger is working, now I am trying to make it more useful by extracting:
> * "Name" of the database being dropped or created
> * Name of the User performing the action ( login account)
> * And time the action was performed.
> CREATE TRIGGER ddl_trig_database
> ON ALL SERVER
> FOR CREATE_DATABASE
> AS
> PRINT 'Database Created.'
> INSERT INTO AuditDB.dbo.dbAudit (userName, dbName, actionDate, action)
> values (', ', ', 'DB Created')
> GO
> How do Iobtain userName, dbName, actionDate within the trigger ?
> Thanks
>
>
>

DDL Triggers

I know that DDL_LOGIN_EVENTS is the same as CREATE LOGIN, ALTER LOGIN and DROP LOGIN combined but where is this documented?

I have some code here (http://sqlservercode.blogspot.com/2006/08/ddl-trigger-events-revisited.html) that basically shows that you can combine events

But where is this info in BOL?

For example if I do this:

create a trigger and I use DDL_VIEW_EVENTS

CREATE TRIGGER ddlTestEvents
ON DATABASE
FOR DDL_VIEW_EVENTS
AS
PRINT 'You must disable Trigger "ddlTestEvents" to drop, create or alter Views!'
ROLLBACK;
GO

After that I would check the sys.triggers and sys.trigger_events views to see what was inserted

SELECT name,te.type,te.type_desc
FROM sys.triggers t
JOIN sys.trigger_events te on t.object_id = te.object_id
WHERE t.parent_class=0
AND name IN('ddlTestEvents')
ORDER BY te.type,te.type_desc

In this case 3 rows were inserted

DDL_VIEW_EVENTS
-
41 CREATE_VIEW
42 ALTER_VIEW
43 DROP_VIEW

So here is the complete list for who wants it

DDL_VIEW_EVENTS
-
41 CREATE_VIEW
42 ALTER_VIEW
43 DROP_VIEW

DDL_USER_EVENTS
-
131 CREATE_USER
132 ALTER_USER
133 DROP_USER

DDL_XML_SCHEMA_COLLECTION_EVENTS
-
177 CREATE_XML_SCHEMA_COLLECTION
178 ALTER_XML_SCHEMA_COLLECTION
179 DROP_XML_SCHEMA_COLLECTION

DDL_VIEW_EVENTS
-
41 CREATE_VIEW
42 ALTER_VIEW
43 DROP_VIEW

DDL_TRIGGER_EVENTS
-
71 CREATE_TRIGGER
72 ALTER_TRIGGER
73 DROP_TRIGGER

DDL_USER_EVENTS
-
131 CREATE_USER
132 ALTER_USER
133 DROP_USER

DDL_TYPE_EVENTS
-
91 CREATE_TYPE
93 DROP_TYPE

DDL_TABLE_EVENTS
-
21 CREATE_TABLE
22 ALTER_TABLE
23 DROP_TABLE

DDL_SYNONYM_EVENTS
-
34 CREATE_SYNONYM
36 DROP_SYNONYM

DDL_STATISTICS_EVENTS
--
27 CREATE_STATISTICS
28 UPDATE_STATISTICS
29 DROP_STATISTICS

DDL_SERVICE_EVENTS

161 CREATE_SERVICE
162 ALTER_SERVICE
163 DROP_SERVICE

DDL_SCHEMA_EVENTS

141 CREATE_SCHEMA
142 ALTER_SCHEMA
143 DROP_SCHEMA

DDL_ROUTE_EVENTS

164 CREATE_ROUTE
165 ALTER_ROUTE
166 DROP_ROUTE

DDL_ROLE_EVENTS
-
134 CREATE_ROLE
135 ALTER_ROLE
136 DROP_ROLE

DDL_REMOTE_SERVICE_BINDING_EVENTS
--
174 CREATE_REMOTE_SERVICE_BINDING
175 ALTER_REMOTE_SERVICE_BINDING
176 DROP_REMOTE_SERVICE_BINDING

DDL_QUEUE_EVENTS

157 CREATE_QUEUE
158 ALTER_QUEUE
159 DROP_QUEUE

DDL_PROCEDURE_EVENTS
-
51 CREATE_PROCEDURE
52 ALTER_PROCEDURE
53 DROP_PROCEDURE

DDL_PARTITION_SCHEME_EVENTS

194 CREATE_PARTITION_SCHEME
195 ALTER_PARTITION_SCHEME
196 DROP_PARTITION_SCHEME

DDL_PARTITION_FUNCTION_EVENTS

191 CREATE_PARTITION_FUNCTION
192 ALTER_PARTITION_FUNCTION
193 DROP_PARTITION_FUNCTION

DDL_EVENT_NOTIFICATION_EVENTS
-
74 CREATE_EVENT_NOTIFICATION
76 DROP_EVENT_NOTIFICATION

DDL_ASSEMBLY_EVENTS
--
101 CREATE_ASSEMBLY
102 ALTER_ASSEMBLY
103 DROP_ASSEMBLY

DDL_CONTRACT_EVENTS
--
154 CREATE_CONTRACT
156 DROP_CONTRACT

DDL_FUNCTION_EVENTS

61 CREATE_FUNCTION
62 ALTER_FUNCTION
63 DROP_FUNCTION

DDL_INDEX_EVENTS

24 CREATE_INDEX
25 ALTER_INDEX
26 DROP_INDEX
206 CREATE_XML_INDEX

DDL_MESSAGE_TYPE_EVENTS

151 CREATE_MESSAGE_TYPE
152 ALTER_MESSAGE_TYPE
153 DROP_MESSAGE_TYPE

Denis The SQL Menace

http://sqlservercode.blogspot.com

Hi Denis,

This information is documented in the Books Online topic "Event Groups for Use with DDL Triggers.

http://msdn2.microsoft.com/en-us/library/ms191441.aspx

Regards,

Gail

|||

Thank you, however I would prefer text over an image (So that I can work my copy and paste magic!!)

Denis the SQL Menace

http://sqlservercode.blogspot.com/

DDL Triggers

I know that DDL_LOGIN_EVENTS is the same as CREATE LOGIN, ALTER LOGIN and DROP LOGIN combined but where is this documented?

I have some code here (http://sqlservercode.blogspot.com/2006/08/ddl-trigger-events-revisited.html) that basically shows that you can combine events

But where is this info in BOL?

For example if I do this:

create a trigger and I use DDL_VIEW_EVENTS

CREATE TRIGGER ddlTestEvents
ON DATABASE
FOR DDL_VIEW_EVENTS
AS
PRINT'You must disable Trigger "ddlTestEvents" to drop, create or alter Views!'
ROLLBACK;
GO

After that I would check the sys.triggers and sys.trigger_events views to see what was inserted

SELECT name,te.type,te.type_desc
FROMsys.triggers t
JOINsys.trigger_events te on t.object_id = te.object_id
WHERE t.parent_class=0
AND name IN('ddlTestEvents')
ORDER BY te.type,te.type_desc

In this case 3 rows were inserted

DDL_VIEW_EVENTS
-
41 CREATE_VIEW
42 ALTER_VIEW
43 DROP_VIEW

So here is the complete list for who wants it

DDL_VIEW_EVENTS
-
41 CREATE_VIEW
42 ALTER_VIEW
43 DROP_VIEW

DDL_USER_EVENTS
-
131 CREATE_USER
132 ALTER_USER
133 DROP_USER

DDL_XML_SCHEMA_COLLECTION_EVENTS
-
177 CREATE_XML_SCHEMA_COLLECTION
178 ALTER_XML_SCHEMA_COLLECTION
179 DROP_XML_SCHEMA_COLLECTION

DDL_VIEW_EVENTS
-
41 CREATE_VIEW
42 ALTER_VIEW
43 DROP_VIEW

DDL_TRIGGER_EVENTS
-
71 CREATE_TRIGGER
72 ALTER_TRIGGER
73 DROP_TRIGGER

DDL_USER_EVENTS
-
131 CREATE_USER
132 ALTER_USER
133 DROP_USER

DDL_TYPE_EVENTS
-
91 CREATE_TYPE
93 DROP_TYPE

DDL_TABLE_EVENTS
-
21 CREATE_TABLE
22 ALTER_TABLE
23 DROP_TABLE

DDL_SYNONYM_EVENTS
-
34 CREATE_SYNONYM
36 DROP_SYNONYM

DDL_STATISTICS_EVENTS
--
27 CREATE_STATISTICS
28 UPDATE_STATISTICS
29 DROP_STATISTICS

DDL_SERVICE_EVENTS

161 CREATE_SERVICE
162 ALTER_SERVICE
163 DROP_SERVICE

DDL_SCHEMA_EVENTS

141 CREATE_SCHEMA
142 ALTER_SCHEMA
143 DROP_SCHEMA

DDL_ROUTE_EVENTS

164 CREATE_ROUTE
165 ALTER_ROUTE
166 DROP_ROUTE

DDL_ROLE_EVENTS
-
134 CREATE_ROLE
135 ALTER_ROLE
136 DROP_ROLE

DDL_REMOTE_SERVICE_BINDING_EVENTS
--
174 CREATE_REMOTE_SERVICE_BINDING
175 ALTER_REMOTE_SERVICE_BINDING
176 DROP_REMOTE_SERVICE_BINDING

DDL_QUEUE_EVENTS

157 CREATE_QUEUE
158 ALTER_QUEUE
159 DROP_QUEUE

DDL_PROCEDURE_EVENTS
-
51 CREATE_PROCEDURE
52 ALTER_PROCEDURE
53 DROP_PROCEDURE

DDL_PARTITION_SCHEME_EVENTS

194 CREATE_PARTITION_SCHEME
195 ALTER_PARTITION_SCHEME
196 DROP_PARTITION_SCHEME

DDL_PARTITION_FUNCTION_EVENTS

191 CREATE_PARTITION_FUNCTION
192 ALTER_PARTITION_FUNCTION
193 DROP_PARTITION_FUNCTION

DDL_EVENT_NOTIFICATION_EVENTS
-
74 CREATE_EVENT_NOTIFICATION
76 DROP_EVENT_NOTIFICATION

DDL_ASSEMBLY_EVENTS
--
101 CREATE_ASSEMBLY
102 ALTER_ASSEMBLY
103 DROP_ASSEMBLY

DDL_CONTRACT_EVENTS
--
154 CREATE_CONTRACT
156 DROP_CONTRACT

DDL_FUNCTION_EVENTS

61 CREATE_FUNCTION
62 ALTER_FUNCTION
63 DROP_FUNCTION

DDL_INDEX_EVENTS

24 CREATE_INDEX
25 ALTER_INDEX
26 DROP_INDEX
206 CREATE_XML_INDEX

DDL_MESSAGE_TYPE_EVENTS

151 CREATE_MESSAGE_TYPE
152 ALTER_MESSAGE_TYPE
153 DROP_MESSAGE_TYPE

Denis The SQL Menace

http://sqlservercode.blogspot.com

Hi Denis,

This information is documented in the Books Online topic "Event Groups for Use with DDL Triggers.

http://msdn2.microsoft.com/en-us/library/ms191441.aspx

Regards,

Gail

|||

Thank you, however I would prefer text over an image (So that I can work my copy and paste magic!!)

Denis the SQL Menace

http://sqlservercode.blogspot.com/

DDL Triggers

Hi,
I am trying to write a DDL Trigger so that whenever someone creates or drops
Database I store information somewhere.
My Trigger is working, now I am trying to make it more useful by extracting:
* "Name" of the database being dropped or created
* Name of the User performing the action ( login account)
* And time the action was performed.
CREATE TRIGGER ddl_trig_database
ON ALL SERVER
FOR CREATE_DATABASE
AS
PRINT 'Database Created.'
INSERT INTO AuditDB.dbo.dbAudit (userName, dbName, actionDate, action)
values (?, ?, ?, 'DB Created')
GO
How do Iobtain userName, dbName, actionDate within the trigger ?
Thanks
It is a little bit more complicated than that. Take a look at the Eventdata
function in BOL and some of the examples there. Eventdata is used to return
the information you are looking for.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"news.microsoft.com" wrote:

> Hi,
> I am trying to write a DDL Trigger so that whenever someone creates or drops
> Database I store information somewhere.
> My Trigger is working, now I am trying to make it more useful by extracting:
> * "Name" of the database being dropped or created
> * Name of the User performing the action ( login account)
> * And time the action was performed.
> CREATE TRIGGER ddl_trig_database
> ON ALL SERVER
> FOR CREATE_DATABASE
> AS
> PRINT 'Database Created.'
> INSERT INTO AuditDB.dbo.dbAudit (userName, dbName, actionDate, action)
> values (?, ?, ?, 'DB Created')
> GO
> How do Iobtain userName, dbName, actionDate within the trigger ?
> Thanks
>
>
>

DDL Triggers

Hi,
I am trying to write a DDL Trigger so that whenever someone creates or drops
Database I store information somewhere.
My Trigger is working, now I am trying to make it more useful by extracting:
* "Name" of the database being dropped or created
* Name of the User performing the action ( login account)
* And time the action was performed.
CREATE TRIGGER ddl_trig_database
ON ALL SERVER
FOR CREATE_DATABASE
AS
PRINT 'Database Created.'
INSERT INTO AuditDB.dbo.dbAudit (userName, dbName, actionDate, action)
values (', ', ', 'DB Created')
GO
How do Iobtain userName, dbName, actionDate within the trigger ?
ThanksIt is a little bit more complicated than that. Take a look at the Eventdata
function in BOL and some of the examples there. Eventdata is used to return
the information you are looking for.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"news.microsoft.com" wrote:

> Hi,
> I am trying to write a DDL Trigger so that whenever someone creates or dro
ps
> Database I store information somewhere.
> My Trigger is working, now I am trying to make it more useful by extractin
g:
> * "Name" of the database being dropped or created
> * Name of the User performing the action ( login account)
> * And time the action was performed.
> CREATE TRIGGER ddl_trig_database
> ON ALL SERVER
> FOR CREATE_DATABASE
> AS
> PRINT 'Database Created.'
> INSERT INTO AuditDB.dbo.dbAudit (userName, dbName, actionDate, action)
> values (', ', ', 'DB Created')
> GO
> How do Iobtain userName, dbName, actionDate within the trigger ?
> Thanks
>
>
>