1

Disclaimer: I am unable to implement this properly in the application, as the application I'm working on doesn't do data access in a consistent way, and refactoring effort would be too great for the scope of the project and coming deadline.

How would I go with implementing a SQLCLR Trigger for Audit Trail? I would like it to be as simple as possible, and as easy to remove and replace with proper implementation later as possible.

I'm planning to write my audit to a single table (the database is not very write heavy), having columns like:

  • Timestamp (datetime) - when the change happened?
  • Username (varchar) - who made the change?
  • AffectedTableName (varchar) - which table has been affected?
  • AffectedRowKey (varchar) - this will be either a simple or compound key like (Id=42, A=4,B=2)
  • OperationType (char(1)) - either I, U or D for insert, update and delete respectively.
  • InsertedXml (xml) - xml-serialized row (SELECT * FROM INSERTED FOR XML AUTO)
  • DeletedXml(xml) - xml-serialized row (SELECT * FROM DELETED FOR XML AUTO)

I'm planning to query and resolve this data to a user-readable form in the application. I'm planning to implement this as a database trigger, written using SQLCLR. I can see 2 possible approaches:

  • Implement this entirely as SqlTrigger method:
  • Implement this as a SqlProcedure method taking parameters:
    • schemaName
    • tableName
    • insertedXml
    • deletedXml

I will appreciate any constructive criticism and suggestions. My limitation is that I have to implement the audit at the database level using triggers, and I want it to be as maintainable (read: removable and replacable) as possible. Also ideally, I don't want to have hundreds of triggers with exactly the same body, in case I have to modify them.

Improve this question

2 Answers 2

Reset to default
2

There is a serious restriction in SQLCLR triggers that will prevent you from implementing your audit triggers in SQLCLR: you can not find which parent object that was changed from inside a SQLCLR trigger. i.e. if you have a single SQLCLR trigger routine registered to multiple tables, you can not find which table got updated/inserted into/deleted from. At first sight @@procID may look usefull, however when called from inside a SQLCLR trigger, @@procid returns the same value, no matter which table was affected. I have searched the internet and experimented a lot and I have not found a solution. I have found more people having the same issue. Some of the messages date back as far as 2006.

I have created a feature request with Microsoft on Microsoft Connect. Please log in and press the UP arrow to get it implemented so you actually can use a SQLCLR trigger for your purpose: https://connect.microsoft.com/SQLServer/feedback/details/768358/a-sqlclr-trigger-should-be-given-the-parent-object-in-the-sqltriggercontext

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

I've been using a variation of this script to create audit triggers from some of my projects for awhile now with great results:

http://www.simple-talk.com/sql/database-administration/pop-rivetts-sql-server-faq-no.5-pop-on-the-audit-trail/

Improve this answer

1 Comment

move the @sql just outside the while loop and replace it with variables for both insert and delete, then exicute the @sql only once after the while loop, using your insert and delete variables like your schema above. sorry, but i don't have time for more than this little hint... good luck.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.