Re: TRIGGER (AFTER INSERT) and Store Procedure ... insert failed !
From: Herve MAILLARD (rvmaillard_at_newsgroup.nospam)
Date: 09/07/04
- Next message: Oded Kovach: "Re: Shrinking or reseting log file"
- Previous message: Dejan Sarka: "Re: Shrinking or reseting log file"
- In reply to: Herve MAILLARD: "TRIGGER (AFTER INSERT) and Store Procedure ... insert failed !"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 7 Sep 2004 11:27:48 +0200
Hi,
I only post this message for Microsoft support :
I have a trigger on a table "CR_OF". :
CREATE TRIGGER Manage_hist ON dbo.cr_of AFTER INSERT AS
EXEC Manage_Data
If my SP is empty (juste keep DECLARE instructions) I have no problem to
insert records in my table.
The problem is that if I remove the comments in my SP then the insert failed
every time...
I really don't understand why ?
I have tested my SP in debug mode and it works well.
Also, my trigger is "after inster" so the data should be already in the
table. No ? Even if I've got something wrong in my SP, how can it failed the
insert who has been alrerady done...
You will find here after some code in order to generate the 2 tables and
the SP you need to test:
---------------- TABLE HISTO_OF ---------
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[histo_of]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[histo_of]
GO
CREATE TABLE [dbo].[histo_of] (
[num_evt_of] [int] IDENTITY (1, 1) NOT NULL ,
[num_of] [smallint] NULL ,
[code_mvt] [smallint] NULL ,
[pds_circ1] [real] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[histo_of] ADD
CONSTRAINT [PK_histo_of] PRIMARY KEY CLUSTERED
(
[num_evt_of]
) ON [PRIMARY]
GO
---------------- SP TEST ---------
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[TEST]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TEST]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE TEST AS
--tag from CR_OF --
DECLARE @num_cr_of int
DECLARE @num_of smallint
DECLARE @code_mvt smallint
DECLARE @pds_circ1 real
-- tag to HISTO_OF --
DECLARE @num_cr_of_OF int
DECLARE @num_of_OF smallint
DECLARE @code_mvt_OF smallint
DECLARE @pds_circ1_OF real
---------------------------------------------------------------------------
----------------
-- step 1 : Manage table
--
---------------------------------------------------------------------------
----------------
DECLARE OF_LIST SCROLL CURSOR FOR
-- Query to select records from table CR_OF --
SELECT num_cr_of, num_of, code_mvt, pds_circ1
FROM cr_of;
-- open cursor --
OPEN OF_LIST
FETCH NEXT FROM OF_LIST
INTO
@num_cr_of, @num_of, @code_mvt,@pds_circ1
WHILE @@FETCH_STATUS = 0 --While records existt
BEGIN
IF @code_mvt >=0 --Records have not been manage
BEGIN
-- Manage tags to be inserted into table HISTO_OF --
SET @num_of_OF = @num_OF
SET @code_mvt_OF = @code_mvt
SET @pds_circ1_OF = @pds_circ1
-- update code_mvt
SET @code_mvt = @code_mvt - 10
UPDATE cr_of SET code_mvt =@code_mvt WHERE num_cr_of=@num_cr_of;
---Search for previous record in CR_OF (Calculate time and weight)
FETCH PRIOR FROM OF_LIST
INTO
@num_cr_of, @num_of, @code_mvt,@pds_circ1
-- Testif record exist --
IF @@FETCH_STATUS = 0
BEGIN
-- Test if OF is the same --
IF @Num_of_OF = @Num_OF
BEGIN
-- calculate weight
SET @pds_circ1_OF = @pds_circ1_OF - @pds_circ1
END
END
-- Move cursor to next record
FETCH NEXT FROM OF_LIST
--intsert into table HISTO_OF --
INSERT INTO histo_of ( num_of , code_mvt, pds_circ1 )
SELECT
@num_of_OF, @code_mvt_OF, @pds_circ1_OF
END --FIN Test Code_MVT >= 0
--Search for next record to manage in table CR_OF --
FETCH NEXT FROM OF_LIST
INTO
@num_cr_of, @num_of, @code_mvt, @pds_circ1
END
CLOSE OF_LIST
DEALLOCATE OF_LIST
PRINT 'END SP'
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
---------------- TABLE CR_OF ---------
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Traitement_histo]') and OBJECTPROPERTY(id, N'IsTrigger')
= 1)
drop trigger [dbo].[Traitement_histo]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[cr_of]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[cr_of]
GO
CREATE TABLE [dbo].[cr_of] (
[num_cr_of] [int] IDENTITY (1, 1) NOT NULL ,
[num_of] [smallint] NULL ,
[code_mvt] [smallint] NULL ,
[pds_circ1] [real] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[cr_of] ADD
CONSTRAINT [PK_cr_of] PRIMARY KEY CLUSTERED
(
[num_cr_of]
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER Traitement_histo ON dbo.cr_of AFTER INSERT AS
EXEC TEST
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-------------------------------------------------------
Now the only thing you have to do to see my problem is to link the table
CR_OF into ACCESS (any version)
Then try to insert this record :
num_of code_mvt pds_circ1
2834 2 200
You will see that the record is "deleted" by access. But if you have a look
into SQL server, the record exist anyway !
The value in code_mvt should be -8 which is correct.
A record should be added into histo_of.
Then you can try from Enterprise Manager :
First : Change the value -8 by 2.
Then copy / paste the line.
You should see that the field "counter" has not been incremented.
If you refresh the data you will see the record...
If you remove the trigger then you can insert data without any problems.
My problem is that I have to insert data from an odbc link (data are sending
from a monitoring system). I really don't know how to solve this problem.
Thanks a lot for your help.
Herve MAILLARD
- Next message: Oded Kovach: "Re: Shrinking or reseting log file"
- Previous message: Dejan Sarka: "Re: Shrinking or reseting log file"
- In reply to: Herve MAILLARD: "TRIGGER (AFTER INSERT) and Store Procedure ... insert failed !"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
