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] ([Kon_broj]) 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, 2009 at 06:03 AM in Default

avatar image

Dimitrije
21 1 1 3

Do you have CASCADE DELETE and/or CASCADE UPDATE set to something else than "Do nothing" on the FK in SQL2005?

Nov 05, 2009 at 06:20 AM Håkan Winther

Sorry Hakan I didn't see you'd already said pretty much the same thing :)

Nov 05, 2009 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, 2009 at 06:22 AM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

(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, 2009 at 11:35 AM

avatar image

Alvin Ramard
1.4k 2 5

(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, 2009 at 05:02 PM

avatar image

dave ballantyne
928 1 3 6

(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.

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:

x2072
x2016
x139

asked: Nov 05, 2009 at 06:03 AM

Seen: 3383 times

Last Updated: Nov 06, 2009 at 01:14 PM

Copyright 2016 Redgate Software. Privacy Policy