March 24, 2010 at 4:39 am
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
March 26, 2010 at 2:31 am
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.
March 26, 2010 at 2:56 am
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.
March 26, 2010 at 3:34 am
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply