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

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Herve MAILLARD (rvmaillard_at_newsgroup.nospam)
Date: 09/07/04 

Date: Tue, 7 Sep 2004 14:17:48 +0200

Hi Hugo,

Thanks a lot for your help,

I'm happy to see that you get the same result as me.
I'm not really worry about the performance. The monitoring system will send
no more than 7 or 8 lines in a day. Also, at the end of my SP, I have a SQL
query to remove all the records when code_mvt =0 (fabrication order ended).
So the CR_OF table will not have more than 7 or 8 lines. As you 've seen, it
works fine with the query analyzer....Same if you run the SP from the
debugger (from Visual studio).

I have try what you suggest (removing the print and add some @variable after
FETCH NEXT ) but it didn't change anything for me. I can insert data in
access (if I modify the SP or not) but only if [code_mvt] is < 0.

I really don't know how the monitoring system will manage with that. All I
know is that it works through an ODBC driver.
I haven't try yet because this monitoring system is in my client office.
Anyway, I don't know what I can do if the insert failed from the monitoring
system. Is there any other solutions ?

What I want to do :

1. The monitoring system send a line into the cr_of table.
2. SQL server detect that a line has been added into the cr_of table
    - The data as to be inserted into histo_of table if the code_mvt is >= 0
    - The Time and the weight are counters. So to get the real value they
have to be recalculated from the previous line in CR_OF(if exist).
    - Data will be inserted into histo_of
    - Code_mvt is set to [value] - 10 (we don't want to manage this record
next time)

Of course the "real" tables are bigger that the one you've seen (I have 40
columns to manage).

Thanks again,

Herve MAILLARD

"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> a écrit dans le message de
news:qo0rj0pnm4oi7ml7ol5mpp9dh0slkb9afb@4ax.com...
> On Tue, 7 Sep 2004 10:44:50 +0200, Herve MAILLARD wrote:
>
> >Hi Hugo,
> >
> >You will find here after some code in order to generate the 2 tables and
> >the SP you need to test:
>
> (snip code)
>
> >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 !
>
> When I tried this (Access 97, Dutch version), I got an error "ODBC error"
> and nothing was inserted in either of the tables. I couldn't reproduce the
> behaviour you are seeing. I did find a way to correct this behaviour and
> to be able to use Access/ODBC for data entry. See below.
>
>
> >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.
>
> I was able to enter data in Enterprise Manager just fine. If I enter one
> row (the first), it gets refreshed after trigger excecution automatically.
> After entering subsequent rows, the refreshing stops. I can't explain
> this, but you have to remember that EM is not intended as a data entry
> tool. Oh, and I saw no "counter" at all; not sure what you mean by that.
>
>
> Before investigating the Access problems, I ran an INSERT with the same
> data from Query Analyser and inspected the output. I found a result set
> was returned, plus the text END SP was printed. Expecially the returning
> of a result set is deadly for Access - I don't know the exact details, but
> somehow, Access will interpret unexpected output (like the returning of a
> result set on an INSERT operation) as an error condition, issue a ROLLBACK
> and display a vague error message.
>
> The returned result set was caused by a FETCH NEXT without INTO @variables
> in the stored procedure. I added an INTO @variables clause to that FETCH
> statement and commented out the PRINT 'END SP' command. After that, I was
> able to insert data through Access. When I enter a row, the data is
> automatically refreshed after trigger execution. The same holds for
> subsequent rows, UNTIL I change the code_mvt on one of the existing rows
> to a positive value and enter a new row - the updated row will be
> refreshed and the new row will be displayed as #Deleted (or rather, the
> Dutch translation of #Deleted) and so will all rows entered after that.
> However, closing and reopening the table shows taht the data is there; it
> is not a SQL Server problem, but a problem in how Access handles data over
> an ODBC connection that gets changed during the transaction. A theory on
> the cause is that the identity value is not yet known when Access sends
> the data to SQL Server; after the INSERT has excecuted Access tried to
> obtain the value of that column by looking for a row that matches the data
> entered in the other columns, but because one of those columnhs has
> changed, it can't find a matching row. However, this is just a guess as
> I'm no Access expert. You could try to get a better answer (and maybe even
> a workaround) in one of the Access groups.
>
>
> >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.
>
> I don't know your monitoring system, but I guess that removing the result
> set and the 'END SP' from the output will solve this problem for now. BUT
> (and it's a very big but) - you'll run into much bigger problems later!
>
> Each time one row is added to the table, all existing rows are processed
> by the procedure, not just the row(s) inserted (plus related rows); this
> means that performance will degrade when the table grows. The processing
> of all these rows is cursor-based; this will degrade the performance even
> more. Most monitoring systems tend to produce lots of data at relatively
> high speed; I expect that your database will soon be unable to keep up
> with the monitoring system.
>
> You really should replace the stored procedure by code in the trigger that
> will use the inserted pseudotable to find out what row(s) were inserted
> and to operate on only those rows. If updates to existing rows do occur as
> well, you should add an UPDATE trigger to deal with those instead of
> waiting for the next insert to deal with the newly inserted plus all
> updated rows, like you are doing now.
> And you really should remove the cursor and make set-based SQL instead.
>
> If you need help rewriting your code, then please refer back to my
> previous post indicating the information needed to help you. Don't forget
> point 6 in that list (the description of the business problem); as it is,
> I have no idea what you are trying to accomplish and why.
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)