INSTEAD OF INSERT: cannot insert into trigger table

INSTEAD OF INSERT: cannot insert into trigger table

Post by P » Tue, 11 Oct 2005 06:50:14


I have attatched an INSTEAD OF INSERT trigger to table foo. The trigger is
coded to insert an exact copy of whats in the Insert table into foo, as well
as data into other tables. When fired the trigger successfully inserts data
into all tables _except_ foo. No errors are displayed. Per the BOL, I've
verified that table foo doesnt contain an identity or computed columns, and
I also cannot use a FOR INSERT because table foo contains a field of type
text. I cannot change the table schema. SQL Server 2000. It would take
some time to untangle the DDL for posting here, so I wanted to throw this
out real quick to make sure there wasn't anything obvious i was missing.
Why does the trigger insert data into all tables, except for table foo?
 
 
 

INSTEAD OF INSERT: cannot insert into trigger table

Post by Brian Selz » Tue, 11 Oct 2005 07:02:47

Is there an INSERT foo within the body of the INSTEAD OF trigger?

 
 
 

INSTEAD OF INSERT: cannot insert into trigger table

Post by P P » Tue, 11 Oct 2005 07:56:37

Yes. i use:

INSERT INTO foo (....)
SELECT .... FROM Inserted

*** Sent via Developersdex http://www.yqcomputer.com/ ***
 
 
 

INSTEAD OF INSERT: cannot insert into trigger table

Post by Brian Selz » Tue, 11 Oct 2005 08:48:13

What I would do to troubleshoot this is to add a FOR INSERT trigger that
simply contains a SELECT * FROM inserted, to determine whether or not the
INSERT statement within the body of the INSTEAD OF trigger is actually
executing. I've never seen a problem like the one you're describing. You
should at a minimum receive an error message of some kind if the INSERT
fails.

Perhaps you should post the exact DDL of the tables, including constraints
and triggers, and some sample data that can be used to recreate the problem.
 
 
 

INSTEAD OF INSERT: cannot insert into trigger table

Post by P » Wed, 12 Oct 2005 00:30:18

hanks for your help Brian. I've pulled out the DDL and cleaned it up to
include only the part that's causing problems. I've verified that the issue
still exists with this cleaned up DDL. Sample data follows. I can cause
the insert to occur if the insert stmt is in the trigger, but as soon as I
move it into the sp, the insert doesn't occur. Any help would be
appreciated.

IF EXISTS(SELECT name
FROM sysobjects
WHERE name = N'ip_COE'
AND type = 'U')
DROP TABLE ip_COE
GO
CREATE TABLE [ip_COE] (
[COE_ID] [int] NOT NULL ,
[Event_Name] [nvarchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cli_Number] [int] NULL ,
[cli_Name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cli_Related] [bit] NOT NULL CONSTRAINT [DF_ip_COE_cli_Related] DEFAULT
(0),
[Contact_ID] [int] NULL ,
[Contact_Name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Start_Date] [datetime] NULL ,
[End_Date] [datetime] NULL ,
[Market] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Location] [nvarchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Price_Information] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Key_Term] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address1] [nvarchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address2] [nvarchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Zip] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Website] [nvarchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Additional_Info] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Directions] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Submitter_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Submitter_Email] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Start_Time] [datetime] NULL ,
[End_Time] [datetime] NULL ,
[Start_Display_Date] [datetime] NULL ,
[Start_Display_Time] [datetime] NULL ,
[End_Display_Date] [datetime] NULL ,
[End_Display_Time] [datetime] NULL ,
[Priority] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AllDay_Event] [bit] NOT NULL CONSTRAINT [DF_ip_COE_AllDay_Event] DEFAULT
(0),
[Always_Display] [bit] NOT NULL CONSTRAINT [DF_ip_COE_Always_Display]
DEFAULT (0),
CONSTRAINT [PK_ip_COE] PRIMARY KEY NONCLUSTERED
(
[COE_ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'spInsertEvent_Test'
AND type = 'P')
DROP PROCEDURE spInsertEvent_Test
GO
CREATE PROCEDURE spInsertEvent_Test
AS
INSERT INTO ip_COE(COE_ID,
Event_Name,
cli_Number,
cli_Name,
cli_Related,
Contact_ID,
Contact_Name,
Phone,
Start_Date,
End_Date,
Market,
Location,
Price_Information,
Notes,
Key_Term,
Address1,
Address2,
City,
State,
Zip,
Website,
Additional_Info,
Directions,
Submitter_Name,
Submitter_Email,
Start_Time,
End_Time,
Start_Display_Date,
Sta
 
 
 

INSTEAD OF INSERT: cannot insert into trigger table

Post by Brian Selz » Wed, 12 Oct 2005 01:12:24

hen I run the command, I get the following error:

Server: Msg 570, Level 16, State 1, Procedure spInsertEvent_Test, Line 3
INSTEAD OF triggers do not support direct recursion. Trigger execution
failed.

An INSERT within the body of the INSTEAD OF INSERT trigger will not cause
the trigger to fire again. Moving that INSERT into a stored procedure won't
work because the execution plan of the stored procedure is designed to be
cached, so the INSERT within the stored procedure will try to fire the
trigger a second time, causing the system to spit out the above error.

You should bite the bullet and place the INSERT statement within the body of
the trigger.

"P" < XXXX@XXXXX.COM > wrote in message
news:ugPmH% XXXX@XXXXX.COM ...


 
 
 

INSTEAD OF INSERT: cannot insert into trigger table

Post by P » Wed, 12 Oct 2005 01:38:57

t's very strange that I don't get that error. however, your explanation
makes perfect sense, and I will move the insert into the body of the
trigger. Thank you for your assistance.

"Brian Selzer" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...