The situation is this: I am making system for synchronizing databases between separate locations. So I created program that creates triggers for update and delete operations on those tables that are defined to be subject of synchronization. The inserts are taken care of with timestamp.
This works perfectly with SQL Server 2008, however users of our ERP system also use SQL Server 2000 and SQL Server 2005. So I tested my system in those environments also, and to my surprise, I found out that updating a primary key or a part of the primary key with the same value, triggers my triggers on tables that have this key as their foreign key. This does not happen in SQL Server 2008.
Just in case I did not made myself clear, say table1 has a primary key pk1 and Table2 has pk2 but also fk1 which references the pk1 from t1. Both have triggers on update and on delete. If I write update table1 set pk1='123' where pk1='123'
In sql server 2008 I only get
(1 row(s) affected)
In sql server 2005 I get :
(3194 row(s) affected) (1 row(s) affected)
where 3194 was a number of rows in table2 that had 123 as its foreign key.
Does anybody knows why this happens this way?
Matt W edit -> posting of re-pro code from below:
I doubt very much that this is cause by a difference in how SQL 2005 and SQL 2008 function.
I suspect it's in your code or settings. Check all your code and all your server/database/table settings. There has to be a difference somewhere.
answered Nov 05 '09 at 11:35 AM
Although it looks like an issue , is it really a problem ?. Is the data correct in both circumstances ?
answered Nov 06 '09 at 05:02 PM