Solved

MS SQL + insert triggers - "too many rows"

Posted on 2000-03-20
8
525 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
 
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

910 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now