Solved

MS SQL + insert triggers - "too many rows"

Posted on 2000-03-20
8
521 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
Comment Utility
Myabe it 's a user defined trigger... can you get the trigger code?
Maybe a cardinality is defined?
0
 

Expert Comment

by:ritterd
Comment Utility
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
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 3

Expert Comment

by:ahoor
Comment Utility
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
Comment Utility
Can you post the ADO code?
0
 
LVL 7

Accepted Solution

by:
tchalkov earned 200 total points
Comment Utility
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
Comment Utility
superb!!!
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

771 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

7 Experts available now in Live!

Get 1:1 Help Now