|
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:
(comments are locked)
|
|
Script out the foreign key, it's entirely likely you'll see that the FK has ON UPDATE CASCADE and/or ON DELETE CASCADE set in 2008, but not in the other environments. See my answer on this question for more info. The databases are exactly the same, literally.(both have on update cascade on table2). And this happens regardless of which table it is (there are a lot of tables in database that have primary keys that serve as foreign keys in other tables)
Nov 05 '09 at 06:34 AM
Dimitrije
Ok, next thing to check - are the triggers in the 2008 database disabled? SELECT * FROM [sys].[triggers] WHERE [is_disabled] = 1
Nov 05 '09 at 12:42 PM
Matt Whitfield ♦♦
They are not. In fact they work the same way in both databases when I'm updating table1 with a different value. The difference only occurs when I'm updating with a same value.
Nov 06 '09 at 11:16 AM
Dimitrije
It's annoying that you can't put code in comments, but I've just create a test table on 2008, and when I update the primary key with the same value, the trigger fires as expected. Can you amend your question with some simple SQL that reproduces the issue?
Nov 06 '09 at 11:23 AM
Matt Whitfield ♦♦
As far as I can see, this looks to me like an optimisation in SQL Server 2008 which prevents the update from cascading down when there is no update to be made. However - I have to ask - is this a particular problem? Both of your triggers are coded to ignore the changes anyway, so is it causing you an issue? I will research more on the cause.
Nov 06 '09 at 01:17 PM
Matt Whitfield ♦♦
(comments are locked)
|
|
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.
(comments are locked)
|
|
Although it looks like an issue , is it really a problem ?. Is the data correct in both circumstances ? This is the only reproducible thing that I pinpointed, the problem however, happens on our alfa testers system(a real business environment). It hasn't yet caused actual problem (other than error logs), because of the design of the database all unnecessary t-sql couldn't be executed anyway. So all that has been affected so far are increased bandwidth overhead and multiplication of error logs. But I'm afraid I cannot envision all possible scenarios that can arise.
Nov 13 '09 at 06:04 AM
Dimitrije
Strictly speaking you should "Set nocount on" anyway. This will solve your issues , as the output from both systems will now match, ie there will be NO reporting of (XX Rows Affected). Plus there will a small decrease in network usage.
Nov 13 '09 at 06:20 AM
dave ballantyne
Reporting is not the problem, the triggers triggered is the problem. Besides, in my sp's I do set nocount ON. This was a t-sql typed into SMS. PS. Thanks everyone for the effort, especially Matt and Dave, though we haven't solved the problem, our exchange helped me better understand it.
Nov 16 '09 at 06:43 AM
Dimitrije
(comments are locked)
|


Do you have CASCADE DELETE and/or CASCADE UPDATE set to something else than "Do nothing" on the FK in SQL2005?
Sorry Hakan I didn't see you'd already said pretty much the same thing :)