Re: TRIGGER (AFTER INSERT) and Store Procedure ... insert failed !
From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 09/07/04
- Next message: Diego F.: "Export database"
- Previous message: Peter The Spate: "Re: Shrinking or reseting log file"
- In reply to: Herve MAILLARD: "Re: TRIGGER (AFTER INSERT) and Store Procedure ... insert failed !"
- Next in thread: Herve MAILLARD: "Re: TRIGGER (AFTER INSERT) and Store Procedure ... insert failed !"
- Reply: Herve MAILLARD: "Re: TRIGGER (AFTER INSERT) and Store Procedure ... insert failed !"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 07 Sep 2004 12:21:42 +0200
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)
- Next message: Diego F.: "Export database"
- Previous message: Peter The Spate: "Re: Shrinking or reseting log file"
- In reply to: Herve MAILLARD: "Re: TRIGGER (AFTER INSERT) and Store Procedure ... insert failed !"
- Next in thread: Herve MAILLARD: "Re: TRIGGER (AFTER INSERT) and Store Procedure ... insert failed !"
- Reply: Herve MAILLARD: "Re: TRIGGER (AFTER INSERT) and Store Procedure ... insert failed !"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
