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?

Solution 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.

Solution 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.

Comments

[alwaysVBNET]

2) Use a trigger to call your 'event' object.function when a certain value change occurs. The problem is how can we do this?!

[williambq]

Here is an example from MSDN: msdn.microsoft.com/en-us/library/938d9dz2.aspx 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, 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 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.

[alwaysVBNET]

Thats very good actually, might be better than the other solution. You could answer the question and I would accept it. Many thanks

[williambq]

How about if I add that to my answer, on which this is a comment. ;)

[Răzvan Flavius Panda]

@williambq: If you wanted to trigger C# code which is not deployed as a SQL Server Common Language Run-time Integration Project but resides on another machine you would need to call your code remotely (web service for example) from inside the SQL Server Common Language Run-time Integration Project when the tigger happens? Or is ther a shorter way to achieve that?

[williambq]

@razvan. Yes, if you separate your process you want to call into multi-tiered service/bus and can't guarantee it is within the same project/solution, then you would need to have a client within your project that the trigger could call which then called your [remote] service. If the event you are triggering is more global, not related to the data service layer (auditing for example), this could make more sense for re-usability and separation of the concerns.