Re: TRIGGER (AFTER INSERT) and Store Procedure ... insert failed !

From: Herve MAILLARD (rvkeops-nospam-_at_hotmail.com)
Date: 09/07/04 

Date: Tue, 7 Sep 2004 10:44:50 +0200

Hi Hugo,

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

"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> a écrit dans le message de
news:k7cpj0djfhpmrql1snp00reh3cu4ac3a1k@4ax.com...
> On Mon, 6 Sep 2004 16:54:52 +0200, Herve MAILLARD wrote:
>
> >Hi,
> >
> >Well, I've got the error "ODBC insert failed" (from access front end). If
I
> >try from Enterprise Manager then I have no error at all.
> >I can see that the counter field is not incremented..(stay blank) and
when I
> >try to refresh the table then my record has been removed.
> >
> >I agree to post the SP code but it's 2 or 3 pages of T-SQL code.
> >I'm trying to remove block by block the code to why wich instruction the
> >problem is comming from.
> >
> >I have no insert into the CR_OF, only some updates.
> >
> >thanks for your help,
> >
> >Herve.
>
> Hi Herve,
>
> Try executing the insert that causes problems in Query Analyzer. Both
> Enterprise Manager and many front end apps have a tendency to swallow the
> real error message and give you something else instead. Or in the case of
> EM even no message at all, as you've already experienced.
>
> Also, if you want help finding what's wrong, you'll have to post some code
> that can be used to recreate your situation and reproduce the error. To be
> precies, you need to post all of the following:
>
> 1. Table structure, as DDL (CREATE TABLE statements; irrelevant columns
> may be omitted but do include all constraint);
> 2. Sample data, as INSERT statements (so that we can copy, paste & run
> instead of spending half an hour typing);
> 1/2a. Test your DDL and INSERTS. Create an empty database on your server
> and run your code. If you get errors, fix them, test again, then post the
> fixed code;
> 3. Expected output, based on sample data;
> 4. The code you are currently using;
> 5. The results you see when you run your code (Do you get an error
> message? If so, copy and paste it. Do you get wrong ouput? If so, what do
> you get and why is that wrong? etc.)
> 6. A short and concise description of the business problem you're trying
> to solve.
>
> I know that this is a lot of information to post. Most of it is probably
> not needed to solve the problem. But you'll never know in advance which
> parts will be needed and which parts not; therefor, I do ask you to post
> it all.
>
> Some tips and tools to help you in providing the data for a post can be
> found here: http://www.aspfaq.com/etiquette.asp?id=5006
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Relevant Pages

  • RE: SQL 2000 instance showing up as version6.5 after running fixre
    ... exec sp_configure N'allow updates', 1 ... DECLARE @username varchar ... FETCH NEXT FROM list_triggers INTO @name, ... create table syssubscriptions (artid int, srvid smallint, dest_db sysname, ...
    (microsoft.public.sqlserver.clustering)
  • RE: Error after Disabling Merge Replication
    ... FETCH NEXT FROM list_pubs INTO @name ... EXEC sp_droppublication @name ... DECLARE list_replicated_tables CURSOR FOR ... SELECT name FROM sysmergearticles ...
    (microsoft.public.sqlserver.replication)
  • Re: Mess with my server
    ... > DECLARE @name varchar ... > FETCH NEXT FROM list_pubs INTO @name ... > EXEC sp_droppublication @name ... > DELETE FROM sysmergesubscriptions ...
    (microsoft.public.sqlserver.replication)
  • Re: ask for standard sp writing
    ... SQL Server Programmer ... > -- Declare the variables to store the values returned by FETCH. ... > OPEN SampleCrsr ...
    (microsoft.public.sqlserver.programming)
  • Re: number of rows in cursor
    ... DECLARE authors_cursor insensitive CURSOR FOR( ... declare @l sysname ... FETCH NEXT FROM authors_cursor into @l ...
    (microsoft.public.sqlserver.programming)