create trigger hangs Resolved - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

create trigger hangs Resolved

Author

Topic

lappin
Posting Yak Master

182 Posts

Posted - 2012-02-10 : 05:50:38

I have made a new trigger, tested it on a test server and it works fine, tested it on a test database on live server and that works fine, but on the live database it just hangs when running create statement. Can't see any errors in Logs. Any theories? I have a DDL_DATABASE_LEVEL_EVENTS trigger on the live database, would this block normal triggers? Do I need to disable the DDL trigger?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-10 : 06:31:46

blocked by a currently running insert / update / delete?

Post the code?

not really enough information here.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

lappin
Posting Yak Master

182 Posts

Posted - 2012-02-10 : 06:59:23

Don't think there is a bug in trigger since it works on other databases /servers, so I tried a simple trigger which just displays a message, and it hangs also:
CREATE trigger TRG_test
on myTable
for insert
as
begin

print 'Row Inserted'

end

Here is my DDL Trigger based on: http://www.tek-tips.com/viewthread.cfm?qid=1549583

CREATE TRIGGER [trgLogDDLEvent] ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @data XML
SET @data = EVENTDATA()
IF @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')
<> 'CREATE_STATISTICS'
AND @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')
<> 'UPDATE_STATISTICS'
INSERT INTO MyDB.dbo.DDLChangeLog
(
EventType,
ObjectName,
ObjectType,
[tsql],
Session_IPAddress
)

SELECT @data.value('(/EVENT_INSTANCE/EventType)[1]',
'nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/ObjectName)[1]',
'nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/ObjectType)[1]',
'nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]',
'nvarchar(max)') ,
client_net_address FROM sys.dm_exec_connections WHERE session_id=@@SPID
;

GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

DISABLE TRIGGER [trgLogDDLEvent] ON DATABASE
GO

ENABLE TRIGGER [trgLogDDLEvent] ON DATABASE
GO

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-10 : 07:01:56

if you try to create the trigger then go another window in ssms and type
EXEC sp_who2

You will probably see that it is blocked by another process. Probably an insert that is in progress.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

lappin
Posting Yak Master

182 Posts

Posted - 2012-02-10 : 07:13:13

You were right, a job running is blocking it. Thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources