Error in DELETE trigger

Hi,
I get an error when I try to delete multiple rows from a table.
Server: Msg 512, Level 16, State 1, Procedure tr_Documents_Delete, Line 16
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
I guess it is because of my trigger. The trigger looks like this:
ALTER TRIGGER tr_Documents_Delete
ON DocumentTrans
AFTER DELETE
AS
if @.@.rowcount = 0
return
DECLARE @.count INT
SET @.count = (SELECT COUNT(*) FROM DocumentTrans INNER JOIN Deleted
ON DocumentTrans.TableReference = Deleted.TableReference
AND DocumentTrans.RowNumberReference = Deleted.RowNumberReference)
IF @.count > 0
return
DECLARE @.TableName VARCHAR(100)
SET @.TableName = (SELECT TableReference FROM Deleted)
DECLARE @.SQLString VARCHAR(1000)
SET @.SQLString = 'UPDATE ' + @.TableName +
' SET DocumentsExists = NULL WHERE ' +
@.TableName + '.RowNumber = ' + CAST((SELECT RowNumberReference FROM
Deleted) AS VARCHAR(100))
EXEC(@.SQLString)
I have tried with a loop. But it also gives me an error
WHILE (SELECT TableReference FROM Deleted) IS NOT NULL
BEGIN
DECLARE @.TableName VARCHAR(100)
SET @.TableName = (Select TableReference FROM Deleted)
DECLARE @.SQLString VARCHAR(1000)
SET @.SQLString = 'UPDATE ' + @.TableName +
' SET DocumentsExists = NULL WHERE ' +
@.TableName + '.RowNumber = ' + CAST((SELECT RowNumberReference FROM
Deleted) AS VARCHAR(100))
EXEC(@.SQLString)
END
Any ideas?
Best regards
HenrikHi
The offending line could be:
SET @.TableName = (SELECT TableReference FROM Deleted)
A trigger fires once per batch, and not once per row deleted. The assumption
that only 1 row is being deleted fails on that line. The select is returning
more than one row, but the variable can not hold more than one row's value.
Either look at the way you process the row, or disallow more than one row to
be deleted at the same time with another check earlier on the in trigger.
Regards
Mike
"Henrik Skak Pedersen" wrote:

> Hi,
> I get an error when I try to delete multiple rows from a table.
> Server: Msg 512, Level 16, State 1, Procedure tr_Documents_Delete, Line 16
> Subquery returned more than 1 value. This is not permitted when the subque
ry
> follows =, !=, <, <= , >, >= or when the subquery is used as an expression
.
> The statement has been terminated.
> I guess it is because of my trigger. The trigger looks like this:
> ALTER TRIGGER tr_Documents_Delete
> ON DocumentTrans
> AFTER DELETE
> AS
> if @.@.rowcount = 0
> return
> DECLARE @.count INT
> SET @.count = (SELECT COUNT(*) FROM DocumentTrans INNER JOIN Deleted
> ON DocumentTrans.TableReference = Deleted.TableReference
> AND DocumentTrans.RowNumberReference = Deleted.RowNumberReference)
> IF @.count > 0
> return
> DECLARE @.TableName VARCHAR(100)
> SET @.TableName = (SELECT TableReference FROM Deleted)
> DECLARE @.SQLString VARCHAR(1000)
> SET @.SQLString = 'UPDATE ' + @.TableName +
> ' SET DocumentsExists = NULL WHERE ' +
> @.TableName + '.RowNumber = ' + CAST((SELECT RowNumberReference FROM
> Deleted) AS VARCHAR(100))
> EXEC(@.SQLString)
> I have tried with a loop. But it also gives me an error
> WHILE (SELECT TableReference FROM Deleted) IS NOT NULL
> BEGIN
> DECLARE @.TableName VARCHAR(100)
> SET @.TableName = (Select TableReference FROM Deleted)
> DECLARE @.SQLString VARCHAR(1000)
> SET @.SQLString = 'UPDATE ' + @.TableName +
> ' SET DocumentsExists = NULL WHERE ' +
> @.TableName + '.RowNumber = ' + CAST((SELECT RowNumberReference FROM
> Deleted) AS VARCHAR(100))
> EXEC(@.SQLString)
> END
> Any ideas?
> Best regards
> Henrik
>
>