trigger error

Question

Post reply

trigger error

t0ze

Grasshopper

Points: 15
More actions
June 11, 2010 at 2:31 pm

#222016

Hello, i'm having an error while i try to insert a row. Frist, i create a trigger, (with success) that testing the same trigger with an insert it gives me this error

"Msg 512, Level 16, State 1, Procedure TR_Inscrito_Insert, Line 24

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

Can anyone helpe on this one ?

Line 24 is in;

IF OBJECT_ID('TR_Inscrito_Insert') IS NOT NULL

DROP TRIGGER TR_Inscrito_Insert

GO

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

that's not the line raising the error; paste the whole trigger.

the error line is going to say something like this:



WHERE SomeVariable = (Select SomeVariable from someTable)

the select in parenthesis is returning more than one value.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

t0ze Grasshopper Points: 15 More actions · Answer 2

Here it this. tks for the reply

USE FUTEBOL



GO

IF OBJECT_ID('TR_Inscrito_Insert') IS NOT NULL

DROP TRIGGER TR_Inscrito_Insert

GO

CREATE TRIGGER TR_Inscrito_Insert ON Inscrito INSTEAD OF INSERT

AS

DECLARE @msg VARCHAR(1000),

	@EdicaoDataInicio Date,

	@ContratoDataInicio Date,

	@Equipa char(10),

	@EdicaoDataFim Date,

	@dateRequestEnd	 Date

SET @msg =  'Esse jogador não tem um contrato válido com a equipa' + @Equipa + ' Por favor regularize

			a situação desse jogador.'

SET @Equipa 	= (SELECT Equipa.descricao

					FROM inserted, Equipa

					WHERE inserted.fifaTeamCode = Equipa.fifaTeamCode)

					--GROUP BY dbo.Equipa.descricao)

SET @EdicaoDataInicio  = (SELECT dataInicio FROM Edicao)

SET @ContratoDataInicio = (SELECT Contrato.dataInicio

							FROM Contrato, inserted

							WHERE Contrato.fifaTeamCode = inserted.fifaTeamCode)

SET @EdicaoDataFim  = (SELECT dataFim FROM Edicao)				

SET @dateRequestEnd = (	SELECT Contrato.dataFim

						FROM Contrato, inserted

						WHERE Contrato.fifaTeamCode = inserted.fifaTeamCode)	

IF (@EdicaoDataInicio > @ContratoDataInicio)

	PRINT @msg

	ELSE

		IF (@EdicaoDataFim < @dateRequestEnd)

			PRINT @msg

				ELSE

					INSERT INTO Inscrito SELECT * FROM INSERTED

GO

Paul White SSC Guru Points: 150457 More actions · Answer 3

A trigger fires once per statement, not once per row.

The trigger code provided does not account for multi-row changes (more than one row in the inserted/deleted pseudo-tables).

Either prevent multi-row operations (a poor plan) or rewrite the trigger logic to handle multi-row changes.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi