Trigger to Update inserted record works on single insert not on bulk insert

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Trigger to Update inserted record works on single insert not on bulk insert

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-31 : 08:44:12

Fardin writes "/*I use the following trigger to update the inserted record with a sequence number that I call from stored procedure
The Trigger works when you add one record at a time but when I use query to insert many records, the subquery gives me the following error
Subquery returned more than one value. This is not permitted when subquery follows =,!= .....

*/

CREATE TRIGGER [Insert_New_Sequence] ON Table1
FOR INSERT
AS
Declare @REC_NO int

select @REC_NO=INVTRANS_RECNO from inserted

if @REC_NO is NULL
Begin
Declare @SEQ_ID int
exec Get_Next_Sequence 'Table1' ,@SEQ_ID output
Update Table1 set RECNO=@SEQ_ID
Where ID = (Select ID from Inserted)
End"

ToddV
Posting Yak Master

218 Posts

Posted - 2002-01-31 : 09:02:36

Change this:
Update Table1 set RECNO=@SEQ_ID
Where ID = (Select ID from Inserted)

TO This
Update Table1
set RECNO=@SEQ_ID
FROM Table1 A JOIN INserted B ON A.ID = B.ID

This will be able to handle more than one row inserts.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources