CODEWITHSUNDEEP
sql-server-2008sql-server-2008-r2service-broker
kla
kla

Reputation: 271

Audit trail using Service Broker

Looking for pointers to create audit trial for a database that uses service broker. I have seen many examples online on creating one audit table for all the tables being audited. But what I am looking for is one audit table per base table and the use of service broker to accomplish this. Any pointers are appreciated.

Upvotes: 1

Views: 827

Answers (1)

Jānis
Jānis

Reputation: 2266

Sounds like strange idea.. but what you can try:

  • create service broker services and queues (sender and receiver)
  • create triggers for all tables that sends from sender to receiver service rows from inserted and deleted tables in trigger- can use "For XML" clause. to automate trigger creation can use procedure sp_msForEachTable.
  • make activation procedure for receiver service, that does something with those xml messages

thats about it. you could make better performance by using dialog pool (here)

Upvotes: 1

Related Questions