question

Dimitrije avatar image
Dimitrije asked

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]')
sql-server-2008sql-server-2005trigger
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Do you have CASCADE DELETE and/or CASCADE UPDATE set to something else than "Do nothing" on the FK in SQL2005?
1 Like 1 ·
Sorry Hakan I didn't see you'd already said pretty much the same thing :)
0 Likes 0 ·
Matt Whitfield avatar image
Matt Whitfield answered

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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Alvin Ramard avatar image
Alvin Ramard answered

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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

dave ballantyne avatar image
dave ballantyne answered

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

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.