SQLCLR 触发器或存储过程用于审计跟踪的可行性？
免责声明：我无法在应用程序中正确实现这一点，因为我正在开发的应用程序不能以一致的方式进行数据访问，并且重构工作对于项目范围和即将到来的截止日期来说太大了。
我将如何实施用于审计跟踪的 SQLCLR 触发器？我希望它尽可能简单，并且尽可能容易地在以后通过适当的实现来删除和替换。
我计划将审计写入单个表（数据库的写入量不是很大），其中包含以下列：
- 时间戳（
datetime） - 更改何时发生？ - 用户名 (
varchar) - 谁进行了更改？ - AffectedTableName (
varchar) - 哪个表受到影响？ - AffectedRowKey (
varchar) - 这将是简单键或复合键，例如 (Id=42,A=4,B=2 - ) (
char(1)) -I、U或D分别用于插入、更新和删除。 - InsertedXml (
xml) - xml 序列化行 (SELECT * FROM INSERTED FOR XML AUTO) - DeletedXml(
xml) - xml 序列化行 (< code>SELECT * FROM DELETED FOR XML AUTO）
我计划在应用程序中查询这些数据并将其解析为用户可读的形式。我计划将其实现为使用 SQLCLR 编写的数据库触发器。我可以看到两种可能的方法：
- 完全将其实现为 SqlTrigger 方法：
- 将其实现为带有参数的 SqlProcedure 方法：
- 架构名称
- 表名
- 插入Xml
- 已删除Xml
任何建设性的批评和建议。我的限制是我必须使用触发器在数据库级别实现审核，并且我希望它尽可能可维护（读取：可移动和可替换）。另外，理想情况下，我不想拥有数百个具有完全相同主体的触发器，以防我必须修改它们。
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)) - eitherI,UorDfor 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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
SQLCLR 触发器中有一个严重的限制，将阻止您在 SQLCLR 中实现审核触发器：您无法找到从 SQLCLR 触发器内部更改了哪个父对象。即，如果您有一个 SQLCLR 触发器例程注册到多个表，您将无法找到哪个表被更新/插入/删除。乍一看，@@procID 可能看起来很有用，但是当从 SQLCLR 触发器内部调用时，@@procid 返回相同的值，无论哪个表受到影响。我在网上搜索并进行了很多尝试，但没有找到解决方案。我发现更多人有同样的问题。其中一些消息可以追溯到 2006 年。
我已在 Microsoft Connect 上向 Microsoft 创建了功能请求。请登录并按向上箭头来实现它，以便您实际上可以使用 SQLCLR 触发器来实现您的目的：https://connect.microsoft.com/SQLServer/feedback/details/768358/a-sqlclr-trigger-should-be-given-the-parent-object-in-the-sqltriggercontext
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
我已经使用此脚本的变体从我的一些项目创建审核触发器有一段时间了，效果很好：
http://www.simple-talk.com/sql/database-administration/pop-rivetts-sql-server-faq-no.5-pop-on-the-audit-trail/
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/