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
>
>

Error Importing CVS into SQL Server 2005 using SQLBulkCopy and c#

Hi,
I'm using SQLBulkCopy via c#, I'm importing a large text file into a
destination SQL Table, the file contains multiple columns and is about 100k
records, I am getting the following error:
'Could not allocate space for object 'dbo.list0001'.PK_List001Table' in
database 'E3232323\43343434_\34343\MyDatabase.mdf because the 'PRIMARY'
filegroup is full. Create disk space be deleting unneeded files, dropping
objects in the filegroup, adding additional files to the filegroup, or
setting autogrowth on the existing files in the filegroup.
Can someone please tell me how to resolve this?
Thanks
Hi Rob
"Rob Dob" wrote:

> Hi,
>
> I'm using SQLBulkCopy via c#, I'm importing a large text file into a
> destination SQL Table, the file contains multiple columns and is about 100k
> records, I am getting the following error:
>
> 'Could not allocate space for object 'dbo.list0001'.PK_List001Table' in
> database 'E3232323\43343434_\34343\MyDatabase.mdf because the 'PRIMARY'
> filegroup is full. Create disk space be deleting unneeded files, dropping
> objects in the filegroup, adding additional files to the filegroup, or
> setting autogrowth on the existing files in the filegroup.
>
> Can someone please tell me how to resolve this?
> Thanks
The error message is probably correct! You have either a fixed file size for
the database file, or there is not enough disc space for the file to grow by
the expected value. This may be because you have a percentage growth set and
it is therefore trying to expand by an enormous amount or you just don't have
enough space available.
John

Error Importing CVS into SQL Server 2005 using SQLBulkCopy and c#

Hi,
I'm using SQLBulkCopy via c#, I'm importing a large text file into a
destination SQL Table, the file contains multiple columns and is about 100k
records, I am getting the following error:
'Could not allocate space for object 'dbo.list0001'.PK_List001Table' in
database 'E3232323\43343434_\34343\MyDatabase.mdf because the 'PRIMARY'
filegroup is full. Create disk space be deleting unneeded files, dropping
objects in the filegroup, adding additional files to the filegroup, or
setting autogrowth on the existing files in the filegroup.
Can someone please tell me how to resolve this?
ThanksHi Rob
"Rob Dob" wrote:
> Hi,
>
> I'm using SQLBulkCopy via c#, I'm importing a large text file into a
> destination SQL Table, the file contains multiple columns and is about 100k
> records, I am getting the following error:
>
> 'Could not allocate space for object 'dbo.list0001'.PK_List001Table' in
> database 'E3232323\43343434_\34343\MyDatabase.mdf because the 'PRIMARY'
> filegroup is full. Create disk space be deleting unneeded files, dropping
> objects in the filegroup, adding additional files to the filegroup, or
> setting autogrowth on the existing files in the filegroup.
>
> Can someone please tell me how to resolve this?
> Thanks
The error message is probably correct! You have either a fixed file size for
the database file, or there is not enough disc space for the file to grow by
the expected value. This may be because you have a percentage growth set and
it is therefore trying to expand by an enormous amount or you just don't have
enough space available.
John

Error Importing CVS into SQL Server 2005 using SQLBulkCopy and c#

Hi,
I'm using SQLBulkCopy via c#, I'm importing a large text file into a
destination SQL Table, the file contains multiple columns and is about 100k
records, I am getting the following error:
'Could not allocate space for object 'dbo.list0001'.PK_List001Table' in
database 'E3232323\43343434_\34343\MyDatabase.mdf because the 'PRIMARY'
filegroup is full. Create disk space be deleting unneeded files, dropping
objects in the filegroup, adding additional files to the filegroup, or
setting autogrowth on the existing files in the filegroup.
Can someone please tell me how to resolve this?
ThanksHi Rob
"Rob Dob" wrote:

> Hi,
>
> I'm using SQLBulkCopy via c#, I'm importing a large text file into a
> destination SQL Table, the file contains multiple columns and is about 100
k
> records, I am getting the following error:
>
> 'Could not allocate space for object 'dbo.list0001'.PK_List001Table' in
> database 'E3232323\43343434_\34343\MyDatabase.mdf because the 'PRIMARY'
> filegroup is full. Create disk space be deleting unneeded files, dropping
> objects in the filegroup, adding additional files to the filegroup, or
> setting autogrowth on the existing files in the filegroup.
>
> Can someone please tell me how to resolve this?
> Thanks
The error message is probably correct! You have either a fixed file size for
the database file, or there is not enough disc space for the file to grow by
the expected value. This may be because you have a percentage growth set and
it is therefore trying to expand by an enormous amount or you just don't hav
e
enough space available.
John