x

Differences between SQL Server 2005 and 2008 when updating values

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:

SET ANSI_NULLS ON; GO SET QUOTED_IDENTIFIER ON; GO CREATE TABLE zReplikacija (  ID INT IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,  Tabela VARCHAR (MAX),  Transakcija VARCHAR (MAX) ) ON [PRIMARY]; GO CREATE TABLE [dbo].[FA1] (  [Kon_broj] INT NOT NULL,  [Tip_sif] INT NULL,  [Kon_sint] INT NOT NULL,  [RedosledVreme] TIMESTAMP NULL,  [Redosled] INT IDENTITY (1, 1) NOT NULL,  PRIMARY KEY CLUSTERED ([Kon_broj] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]; GO CREATE TRIGGER [dbo].[trAB_FA1_Update]  ON [dbo].[FA1]  FOR UPDATE  AS IF EXISTS (SELECT *  FROM inserted AS i  INNER JOIN  deleted AS d  ON i.Kon_broj != d.Kon_broj)  BEGIN  INSERT INTO zReplikacija (Tabela, Transakcija)  SELECT 'FA1', 'WHERE Kon_broj = ''' + Kon_broj + ''''  FROM deleted;  END GO CREATE TABLE [dbo].[FAStavka] (  [TipStavkeF] int NOT NULL,  [R_brojF] int NOT NULL,  [Duguje] MONEY NULL,  [Kon_broj] int NULL,  [RedosledVreme] TIMESTAMP NULL,  CONSTRAINT [PK__FAStavka__7A3223E8] PRIMARY KEY NONCLUSTERED ([TipStavkeF] ASC, [R_brojF] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]; GO ALTER TABLE [dbo].[FAStavka] WITH CHECK  ADD FOREIGN KEY ([Kon_broj]) REFERENCES [dbo].FA1 ON DELETE NO ACTION ON UPDATE CASCADE; GO CREATE TRIGGER [dbo].[trAB_FAStavka_Update]  ON [dbo].[FAStavka]  FOR UPDATE  AS IF EXISTS (SELECT *  FROM inserted AS i  INNER JOIN  deleted AS d  ON i.R_brojF != d.R_brojF  OR i.TipStavkeF != d.TipStavkeF)  BEGIN  INSERT INTO zReplikacija (Tabela, Transakcija)  SELECT 'FAStavka', 'WHERE R_brojF = ' + CAST (R_brojF AS VARCHAR) + ' AND TipStavkeF = ''' + CONVERT([varchar], TipStavkeF) + ''''  FROM deleted;  END GO INSERT INTO [dbo].[FA1] ([Kon_broj], [Kon_sint]) SELECT [object_id], 0 FROM [sys].[objects] GO INSERT INTO [dbo].[FAStavka] ([TipStavkeF], [R_brojF], [Kon_broj]) SELECT [object_id], [column_id], [object_id] FROM [sys].[columns] GO UPDATE [dbo].[FA1] SET [Kon_broj] = OBJECT_ID('[dbo].[FA1]') WHERE [Kon_broj] = OBJECT_ID('[dbo].[FA1]') 
more ▼

asked Nov 05 '09 at 06:03 AM in Default

Dimitrije gravatar image

Dimitrije
21 1 1 1

Do you have CASCADE DELETE and/or CASCADE UPDATE set to something else than "Do nothing" on the FK in SQL2005?
Nov 05 '09 at 06:20 AM Håkan Winther
Sorry Hakan I didn't see you'd already said pretty much the same thing :)
Nov 05 '09 at 06:22 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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.

more ▼

answered Nov 05 '09 at 06:22 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

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
It's possible too that the database options 'recursive triggers enabled' or 'nested triggers enabled' might be affecting this, if the original operation is started by a trigger.
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)
10|1200 characters needed characters left

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.

more ▼

answered Nov 05 '09 at 11:35 AM

Alvin Ramard gravatar image

Alvin Ramard
207 2

(comments are locked)
10|1200 characters needed characters left

Although it looks like an issue , is it really a problem ?. Is the data correct in both circumstances ?

more ▼

answered Nov 06 '09 at 05:02 PM

dave ballantyne gravatar image

dave ballantyne
928 1 1 4

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)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1933
x1816
x114

asked: Nov 05 '09 at 06:03 AM

Seen: 2695 times

Last Updated: Nov 06 '09 at 01:14 PM