Solved

MS SQL + insert triggers - "too many rows"

Posted on 2000-03-20
8
529 Views
Last Modified: 2008-03-06
MS SQL Server 7.0 - VB 6.0 - ADO 2.1
When I try to insert a record into a table, which has a trigger on it - I get the error "too many rows!". The trigger inserts values into a child table. Any ideas why this might be happening?
0
Comment
Question by:dd021197
8 Comments
 
LVL 3

Expert Comment

by:ahoor
ID: 2635930
Myabe it 's a user defined trigger... can you get the trigger code?
Maybe a cardinality is defined?
0
 

Expert Comment

by:ritterd
ID: 2635974
Without seeing a copy of your SQL statement I would say that you are using an insert/values statement which would only expect one row instead of an insert/select which is batch.  The trigger should be something like

Create Trigger MyTrigger
On ParentTable
For Insert
as
Insert ChildTable
 (Fld1,
  Fld2)
Select
 Fld1,
 Fld2
From Inserted
0
 
LVL 3

Expert Comment

by:ahoor
ID: 2639384
insert/values would give a syntax error if you tried inserting using the inserted/deleted table. I think there's a check in the trigger that says
if more than 1 (or more) rows are found, give an error.
I installed that myself sometime ago somewhere...
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 1

Author Comment

by:dd021197
ID: 2640245
That's what I'm doing at the moment.

Here is the trigger in question....

INSERT INTO Class_Assessment_Element
      (Class_ID,
      Assessment_Desc,
      Assessment_Type_ID,
      Weighting,
      Academic_ID)
SELECT      a.Class_ID,
      b.Assessment_Desc,
      b.Assessment_Type_ID,
      b.Weighting,
      a.Academic_ID
FROM inserted a, Assessment_Element b
WHERE a.Unit_ID = b.Unit_ID

This trigger works okay in SQL Server e.g. Query Analyser but when
a record is added through VB/ADO data control an error message
appears saying too many rows.

The Assessment_Element table can contain 0 to many rows.

Why should this work through SQL Server, but fail through
ADO 2.1 under VB?
0
 
LVL 3

Expert Comment

by:ahoor
ID: 2640564
Where does the error come from? It definetaly is not an MS_sql
error, don't think it's a vb or ado error either, looks user defined...
an application error, that is. so you must be able to find where this
error is stated.

Are you sure the insert statement is where it goes wrong? This is
only a part of the trigger... maybe it happens before or after...

Maybe somewhere is a check that compares the # of rows affected to
the # of rows inserted? In this case you can get a difference if you insert
1 row and the trigger inserts 2 or more...

Is there a trigger or constraint on the class_assessment_element table?

0
 
LVL 6

Expert Comment

by:Jon_Raymond
ID: 2643109
Can you post the ADO code?
0
 
LVL 7

Accepted Solution

by:
tchalkov earned 200 total points
ID: 2643543
Insert the following line in the beginning of every trigger you use

SET NOCOUNT ON

I had the same problem with ADO and this fixed the problem. It seems that when you have some TSQL statement in the trigger it by default returns the rows affected, which is not a problem for SQL Server, but is a problem for ADO and VB.
0
 
LVL 1

Author Comment

by:dd021197
ID: 2644238
superb!!!
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question