1

I have a requirement where whenever there's an entry in the table, I want to trigger an event. I have used EntityListeners (Spring Data JPA concept) for this, which is working perfectly fine; but the issue here is the insert can happen through stored procedures or manual entry. I tried searching online and found the Spring JPA inbound and outbound channel adapter concept, but I think this concept doesn't help me much in what I want to achieve. Can anybody clarify to me if this concept helps me as I have no much idea on this concept or provide me with any solutions on how I can achieve this?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Shinchan
  • 81
  • 2
  • 17
  • 1
    As far as I know, you would need to handle that at DB side. Something like https://stackoverflow.com/a/9556527/4762502 – Gaurav Jeswani Aug 26 '21 at 07:29
  • @GauravJeswani thanks for the link but will this work for MS SQL Server too? – Shinchan Aug 26 '21 at 07:34
  • MySql might have some other syntax for trigger, but they also have triggers. So I believe if you read a little bit you will get MySql triggers syntax also and that will work for you. – Gaurav Jeswani Aug 26 '21 at 07:36

1 Answers1

2

There are no "great" mechanisms for raising events "from the data layer" in SQL Server.

There are three "OK" ones:

  1. Triggers (only arguably OK)

Triggers seem like an obvious solution, but then you have to ask yourself... what will the trigger actually do? If it just writes data into another table, you still haven't gotten yourself outside the database. There are various arcane tricks you could try to use for this, like CLR procedures, or a few extended procedures.

But if you go down that route, you have to start thinking about another consideration: Triggers happen in the same transaction as the DML operation that caused them to fire. If they take time to execute, you'll be slowing down your OLTP workloads. If they do anything that is potentially unreliable they could fail, causing your transaction to roll back.

  1. Triggers plus service broker

Service broker provides a mechanism - perhaps the only even-half-sensible mechanism - to get your data out of SQL and into some kind of listener in a "push" based manner. You still have a trigger, but the trigger writes data to a service broker queue. A listener can use a special waitfor receive statement to listen for data as it appears in the queue. The nice thing about this is that once the trigger has pushed data into a broker queue, its job is done. The "receipt" of that data is decoupled from the transaction that caused it to be enqueued in the first place. This sort of service broker mechanism is what is used by things like the SqlDependency built into dot net.

The two main issues with service broker are complexity and performance. Service broker has a steep learning curve, and it's easy to get things wrong. Performance becomes complex if you need to scale, because while it's "easy" to build xml or json payloads, large set based data changes can mean those payloads are massive.

In any case, if you want to explore this route, you're going to want to read (all of) the excellent articles on the subject by Remus Rusanu

Bear in mind that this is an asynchronous "near real time" mechanism, not a synchronous "real time" mechanism like triggers.

  1. Polling a built in change detection mechanism: CDC or Change Tracking.

Sql server comes with two flavours of technology that can natively "watch" changes that happen in tables, and record them: Change Tracking, and Change Data Capture

Neither of these push data out of the database, they're both "pull" based. What they do is store additional data in the database when changes happen. CDC can provide a complete log of every change, whereas change tracking "points to" rows that have changed via the primary key values. Though both of these involve "polling history", there are significant differences between them, so read the fine print.

Note that CDC is "doubly asynchronous" - the data is read from the transaction log, so recording the data is not part of the original transaction. And then you have to poll the CDC data, it's not pushed out to you. Furthermore, the functions generated by Microsoft when you enable CDC can be unbelievably slow as soon as you ask for something useful, like net changes with mask (which can tell you which columns really changed their value), and your ability to enable CDC comes with a lot of caveats and limitations (again, read the docs for all of this).

As to which of these is "best", well, that's a matter of opinion and circumstance. I have used CDC extensively, service broker rarely, and triggers almost never, as a way of getting events out of SQL. I have never actually used change tracking in a production environment, but if I had the choice again I would probably have chosen change tracking rather than change data capture, at least until or unless there were requirements that mandated the use of CDC because of its additional functionality beyond what change tracking can provide.

One last note: If you need to "guarantee" that the events that get raised have in fact been collected by a listener and successfully forwarded to subscribers, well, you have some work ahead of you! Guaranteed messaging is hard.

allmhuran
  • 4,154
  • 1
  • 8
  • 27
  • I appreciate your efforts I have a doubt here, will we be able to get to know from the middle-ware side if there's some update/insert in the specified table using CDC or CT? – Shinchan Aug 27 '21 at 06:30
  • Yes, but you have to poll the data based on some kind of timing mechanism. Neither mechanism will "push" events out to a client, you have to check periodically to see whether new changes are available. But both mechanisms provide a way of determining that a change is "new". To do so, the client stores a "high water mark" value indicating where the client is up to in terms of the log of changes. – allmhuran Aug 27 '21 at 08:58