How do you pass a parameter to a CLR trigger?

Question

Post reply

How do you pass a parameter to a CLR trigger?

EITCL

SSCrazy

Points: 2301
More actions
March 24, 2010 at 4:39 am

#231119

I have a CLR trigger set up on several tables. The trigger is created like this:
CREATE TRIGGER tr_Title_IUD ON Title
FOR INSERT, UPDATE, DELETE
AS EXTERNAL NAME MyClr.clsTriggers.MyAuditTrigger
This works fine, and I intend to use this as a generic auditing trigger. But what would like to do is pass the object_id (or at least the @@PROCID) of the triggering table into the CLR code. Is there a way to do this?
Can I do something like?
AS EXTERNAL NAME MyClr.clsTriggers.MyAuditTrigger(@Tablename)
Does anyone have advice about this?
The other way I think I can achieve what I want is to set up the auditing as a CLR procedure (not a trigger), and call that procedure from the triggers:
CREATE TRIGGER tr_Title_IUD ON Title
FOR INSERT, UPDATE, DELETE
AS
EXEC pClrAuditTrigger @CallingTrigger = @@PROCID

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

Why would you use a CLR trigger here? A T-SQL trigger seems a better idea.

Or upgrade to 2008 and use Change Data Capture.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

EITCL SSCrazy Points: 2301 More actions · Answer 2

The reason for using a CLR trigger is make the auditing generic. The generic CLR trigger inserts any changed fields into one standard auditing table. Also, by calling the generic CLR trigger, all triggers are essentially identical: all the logic is contained within the CLR code and does not appear many times in each trigger (as would be the case if this was implemented in T-SQL).

We'd love to upgrade to 2008, but unfortunately that is not an option for many, many months.

Paul White SSC Guru Points: 150467 More actions · Answer 3

A laudable goal, but limited in its application since you cannot get details of the object that caused the trigger to fire, as I think you are in the process of discovering. CLR triggers are pretty close to useless for this reason. You might like to vote for Adam Machanic's Connect Item related to enhancing CLR triggers.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi