Strange behaviors of INSTEAD OF DELETE trigger on SQL Server 2005

Question

Post reply

Strange behaviors of INSTEAD OF DELETE trigger on SQL Server 2005

Michele Dekleva

Grasshopper

Points: 14
More actions
August 20, 2007 at 9:50 am

#174392

I’m migrating a SS 2000 database to SS 2005.
The most critical incompatibly I’ve found since now, is about INSTEAD OF DELETE triggers.
For simplicity I have created a simpler, test-oriented, db to demonstrate the problem.
The DB has 3 tables:

T_CO table:
CREATE TABLE [dbo].[T_CO](
      [F_ID] [int] IDENTITY(1,1) NOT NULL,
      [F_CODE] [char](18) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [F_DESCRIPTION] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_T_CO] PRIMARY KEY CLUSTERED
(
      [F_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

T_IMG table:
CREATE TABLE [dbo].[T_IMG](
      [F_ID] [int] IDENTITY(1,1) NOT NULL,
      [F_CODE] [char](18) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [F_DESCRIPTION] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [F_ID_T_CO] [int] NULL,
      [F_IMAGE] [int] NULL,
CONSTRAINT [PK_T_IMG] PRIMARY KEY CLUSTERED
(
      [F_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[T_IMG] WITH CHECK ADD CONSTRAINT [FK_T_IMG_T_CO] FOREIGN KEY([F_ID_T_CO])
REFERENCES [dbo].[T_CO] ([F_ID])

T_ART table:
CREATE TABLE [dbo].[T_ART](
      [F_ID] [int] IDENTITY(1,1) NOT NULL,
      [F_CODE] [char](18) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [F_DESCRIPTION] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [F_ID_T_IMG] [int] NULL,
      [F_ID_T_CO] [int] NULL,
CONSTRAINT [PK_T_ART] PRIMARY KEY CLUSTERED
(
      [F_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[T_ART] WITH CHECK ADD CONSTRAINT [FK_T_ART_T_CO] FOREIGN KEY([F_ID_T_CO])
REFERENCES [dbo].[T_CO] ([F_ID])
GO
ALTER TABLE [dbo].[T_ART] WITH CHECK ADD CONSTRAINT [FK_T_ART_T_IMG] FOREIGN KEY([F_ID_T_IMG])
REFERENCES [dbo].[T_IMG] ([F_ID])

Table T_IMG has an INSTEAD OF DELETE trigger:

CREATE TRIGGER [trgDelImg]
   ON [dbo].[T_IMG]
   INSTEAD OF DELETE
AS
BEGIN
      select F_ID, F_ID_T_CO into #ttDelArtImages from deleted
      -- in real db, here a stored procedure is called…
      drop table #ttDelArtImages

      delete T_IMG
      where T_IMG.F_ID in (select F_ID from deleted)
END

Every time the trigger is activated, I need to save a couple of columns from ‘deleted’ table into a new temporary table, just to perform some operations. Than the temporary table is dropped from tempdb.

Let’s assume the every tables are empty.
Now, if you run this command:

           delete T_IMG where f_id = 1

you will receive this error:

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

The same situation on SS 2000 runs without problems.

Trying to investigate, I discovered that if I substitute the INSTEAD OF DELETE with a FOR DELETE triggers:

DROP TRIGGER [trgDelImg]
go

CREATE TRIGGER [trgDelImg]
   ON [dbo].[T_IMG]
   FOR DELETE
AS
BEGIN

      select F_ID, F_ID_T_CO into #ttDelArtImages from deleted
      drop table #ttDelArtImages

--    delete T_IMG
--    where T_IMG.F_ID in (select F_ID from deleted)
END

Than, by running the same command:

           delete T_IMG where f_id = 1

you will receive this the expected, and correct, answer:

(0 row(s) affected)

Since this seems very strange to me, I’ve tried another solution.
My assumption is that the wrong (for SS 2005) sentence is:

      select F_ID, F_ID_T_CO into #ttDelArtImages from deleted

when used within a INSTEAD OF DELETE trigger.

Than I have modified the trigger this way:

DROP TRIGGER [trgDelImg]
go

CREATE TRIGGER [trgDelImg]
   ON [dbo].[T_IMG]
   INSTEAD OF DELETE
AS
BEGIN

      CREATE TABLE #ttDelArtImages (
                        [F_ID] [int] NOT NULL,
                        [F_ID_T_CO] [int] NOT NULL
      ) ON [PRIMARY]

      insert into #ttDelArtImages select F_ID, F_ID_T_CO from deleted
--    select F_ID, F_ID_T_CO into #ttDelArtImages from deleted
      drop table #ttDelArtImages

      delete T_IMG
      where T_IMG.F_ID in (select F_ID from deleted)
END

Essentially I’ve explicitly created the temporary table and than I’ve normally inserted the needed record(s).

Running again the same command:

           delete T_IMG where f_id = 1

now you’ll get the correct answer:

(0 row(s) affected)

It seems that the combination of INSTEAD OF DELETE and SELECT xxx INTO is not admissible in SS 2005 while it was in SS 2000.

Do anyone had a similar experience or know something about this ?

Thank you in advance
ersonName w:st="on" ProductID="Michele Dekleva">Michele DeklevaersonName>

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

can you post the results of this query:

select serverproperty('Edition'), serverproperty('ProductLevel'), serverproperty('ProductVersion')

I ran your scripts and I get no problems on Development Edition (x64) SP2

* Noel

Michele Dekleva Grasshopper Points: 14 More actions · Answer 2

The query returns:

Developer Edition RTM 9.00.1399.06

It seems SP2 is not installed, although i'm sure i've installed it.