MS SQL + insert triggers - "too many rows"

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?
LVL 1
dd021197Asked:
Who is Participating?
 
tchalkovConnect With a Mentor Commented:
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
 
ahoorCommented:
Myabe it 's a user defined trigger... can you get the trigger code?
Maybe a cardinality is defined?
0
 
ritterdCommented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
ahoorCommented:
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
 
dd021197Author Commented:
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
 
ahoorCommented:
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
 
Jon_RaymondCommented:
Can you post the ADO code?
0
 
dd021197Author Commented:
superb!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.