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: 323518 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: 150467 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