SQL Server 2005: how to publish trigger from assembly

1. Reconfigure server state for CLR assembly use

sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

2. Enable TRUSTWORTHY property of database

ALTER DATABASE database_name SET TRUSTWORTHY ON

3. Register assembly in database

CREATE ASSEMBLY assembly_name
FROM assembly_full_path
WITH PERMISSION_SET = UNSAFE;
GO

4.  Create trigger  on the table
USE database_name
IF EXISTS (SELECT * FROM sys.triggers WHERE parent_class = 0 AND name = 'trigger_name') DROP TRIGGER trigger_name ON DATABASE
GO
CREATE TRIGGER trigger_name ON table_name AFTER INSERT, UPDATE, DELETE
AS
EXTERNAL NAME assembly_name.trigger_class_name.trigger_method
GO

Trigger in SQL Server Error: MSDTC on server is unavailable

I found decision here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=54705

---

On the server where the trigger resides, you need to turn the MSDTC service on. You can this by clicking START > SETTINGS > CONTROL PANEL > ADMINISTRATIVE TOOLS > SERVICES. Find the service called 'Distributed Transaction Coordinator' and RIGHT CLICK (on it and select) > Start.

Test the trigger and see if it works. If it still does not work, wrap you trigger in the following transaction code (found below in bold):


SET XACT_ABORT ON
BEGIN DISTRIBUTED TRANSACTION

-- Put all queries in here (SELECT, INSERT, UPDATE, and DELETE)
select * from [SERVER2].[DBASE].[OWNER].[TABLENAME]
update [SERVER2].[DBASE].[OWNER].[TABLENAME]
set [column] = value
where [condition(s)]

COMMIT TRANSACTION
SET XACT_ABORT OFF