SQL 2000 - Problem with Triggers

[JulesDBQ]

Hi. I am new to Triggers and SQL, so any assistance you can offer would be much appreciated.

I need to write a simple trigger that fires whenever the ID field in my table is updated. This trigger would update another table that contains the same information in it, so...

If Table1.ID was updated

Any of Table2.ID's that match the original Table1.ID would be updated to the new value.

These tables are not related to one another, so I can't create a relationship between them.

How can I do this easily? Will the trigger fire if I am linking to SQL 2000 via an Access Project? Do I need to do anything special to activate triggers on my server? Thanks again in advance.

 

[cehepker]

Hello,

If you put a trigger on the SQL table it will fire EVERYTIME you update the table. You can use the columns_updated() test in the trigger to see if it was the ID column that got updated. Look in Books on Line under Create Trigger(T-SQL).

My point with the EVERYTIME is that whether you change the field from SQL Enterprise Manager, or Query Analyzer or from Access, the trigger will fire.

The only thing you need to do is to open the table in design view and build the trigger. You can include any update statement in the trigger, so you should be able to update the second table as needed. You do have a choice for the trigger to fire on any combination of DELETE, UPDATE, and/or INSERT.

Carla

Documentation: A Shaft of light into a Coded world

 

[JulesDBQ]

Thank you very much for responding. I will try what you suggested.