How does one properly implement a trigger in MS SQL 2005 / 2008?

Question

This may be a trivial question, but I will ask it anyway: can updates in three or four tables in one database trigger updates to one or two tables in another database (in MS SQL 2005?) I am happy to create any amount of T-SQL necessary, and I also have VS 2008 with C# ability but have never written a trigger like that before.

Essentially, I had the "GetProducts()" of the data repository call my stored procedure anytime any data was loaded with what scope they needed, and I physically changed the "cached" data. Everything in life was good.

Now, they don't want the data update as part of the repository at all. They feel that it is external to the project and should be handled without interaction.

Anyone have any suggestions on what to pursue? Links to ideas already out there would be fantastic.

Answer 1

Are you asking if you update these three tables, then fire a trigger? But if you only update two of the three tables do not fire the trigger?

Your triggers can update any number of tables, they can also cause other triggers to fire, and if you like to live on the dangerous side you can even have these be recursive causing the original trigger to fire again.

However, nothing exists that can cordinate what I think you described. Not to say it can't be done.

Answer 2

A trigger only kicks off when one table is updated,inserted or deleted. If you havea specific order the tables must be inserted, you could put the trigger on the last one.

Alternatively you could write the trigger to examine the other tables as well to ensure all have records. Or you could write one trigger for each table. Or if real time updates are not required, you could have a job that runs periodically to handle the changes needed. Not knowing exactly what you want to do, it is hard to say what is the best way to handle your particular situation. Whatever you do with the triggers, remember triggers operate on sets of data not one row at a time. They should always be written to handle multiple row inserts,updates or deletes or sooner or later your trigger will cause data integrity problems. Do not do this in a cursor unless you like having your production tables locked for hours at a time when someone needs to put in 3,000,000 new records for a new client.

Answer 3

if this is what you want:

check database A for updates of table1, table2, table3, and or table4    
then update database B table5 and/or table6

you need to use a stored procedure to encapsulate all of the necessary logic and transactions for the original updates in database A and the resulting updates in database B