On table update, trigger an action in my .NET code

Question

I'm wondering whether this is possible. We want a function to work in our .NET code when a value in a specific table is updated. This could be upon a record insert or update. Is this possible? If not, is there an alternative process?

Answer 1

You need to ask a couple of questions.

Do you want any to none of your business logic at the db level? Obviously a db trigger could do this (perform some action when a value is changed, even if very specific value only).

I've seen some systems that are db trigger heavy. Their 'logic' resides deeply and highly coupled with the db platform. There are some advantages to that, but most people would probably say the disadvantages are too great (coupling, lack of encapuslation/reusability).

Depending on what you are doing and your leanings you could:

1. Make sure all DAO/BusinessFunctoin objects call your 'event' object.function to do what you want when a certain value change occurs.

2. Use a trigger to call your 'event' object.function when a certain value change occurs.

3. Your trigger does everything.

I personally would lean towards Option 2 where you have a minimal trigger (which simply fires the event call to your object.function) so you don't deeply couple your db to your business logic.

Option 1 is fine, but may be a bit of a hassle unless you have a very narrow set of BF/DAO's that talk to this db table.field you want to watch.

Option 3 is imho the worst choice as you couple logic to your db and reduce its accessibility to your business logic layer.

Given that, here is some information toward accomplishing this via Options 2:

Using this example from MSDN: http://msdn.microsoft.com/en-us/library/938d9dz2.aspx.

This shows how to have a trigger run and call a CLR object in a project.

Effectively, in your project, you create a trigger and have it call your class.

Notice the line: [SqlTrigger(Name="UserNameAudit", Target="Users", Event="FOR INSERT")]

This defines when the code fires, then within the code, you can check your constraint, then fire the rest of the method (or not), or call another object.method as needed.

The primary difference between going directly to the db and adding a trigger is this gives you access to all the objects in your project when deployed together.

Answer 2

I have never tried this but it is possible. You can write a CLR assembly and call that from your table trigger.

You can see an example here.

But you should post your problem and you may find a better work around.