Solved

MS SQL + insert triggers - "too many rows"

Posted on 2000-03-20
8
527 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

777 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