1

Is there a way to trigger an event when other user(s) in network either update or insert a record into SQL table?

I could read the table from the database every few seconds then compare the data to see if there are differences, but that would seem resource intensive.

Perhaps, I should have mentioned I'm using C#/WinForm and SQL Database, although I've tagged it.

I've already looked at SQL Trigger, but doesn't it also require reading the trigger table, which can be resource intensive? I was looking more of small program/service running in background that when users makes changes to the database, trigger it. Foreground app can then check the service to see if change was made. Am I thinking overly complicated?

itchibahn
  • 65
  • 8
  • 1
    Yes, you can create triggers to tables. You can check the accepted answer to this question: http://stackoverflow.com/questions/741414/insert-update-trigger-how-to-determine-if-insert-or-update – mako Mar 26 '17 at 05:31
  • What do you want to do in the trigger .. just check and log the changes or prevent the changes? – Aamir Masood Mar 26 '17 at 05:32
  • @mako, the link is for VB, and also it's similar in concept as I stated in my question, which I'm trying to avoid doing. – itchibahn Mar 26 '17 at 06:25
  • @Aamir various things such as refresh the gridview to display new updated data, check for change before saving a record, etc. – itchibahn Mar 26 '17 at 06:27
  • 1
    If you would like to get notified of any change then you can use SqlDependency in ADO .NET. This way you can update the gridview etc. But if you want to prevent a change based on some criteria then you can use triggers in sql server – Aamir Masood Mar 26 '17 at 06:34

3 Answers3

1

If the processing to be done can be written entirely in SQL (or in other languages, but inside in the database) then you can use a trigger.

Trigger code is executed on the database on insertion/update/deletion of data. What can be done exactly and which language can be used depends on the specific database, but most databases support the notion of triggers.

6502
  • 112,025
  • 15
  • 165
  • 265
1

It's redundant to open a connection with the server and read every few seconds. Besides you will have a insane traffic when you have multiple users who will all try to connect with your db every few seconds ...

As 6502 said, triggers are the way to go. There is a nice little article in msdn.

Archimedes
  • 231
  • 1
  • 2
  • 13
0

After reading up on SqlDependency and Trigger, they both seems to be relying on periodically polling the database. I think this can eventually hammer the performance. So I've decided to study code on Windows Services. Hoping to write Windows Service that I can store information about record(s) just saved. And than I can poll it to see which table I need to reload. I hope I'm not over complicating things....

Thanks, +1 everyone for your replies.

itchibahn
  • 65
  • 8