Delete Trigger problem - SQL Server Forums

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)

Delete Trigger problem

Author

Topic

GaryW
Starting Member

1 Post

Posted - 2007-02-04 : 10:21:32

I am getting an error 'Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.' when I try to execute a stored procedure in my asp.net 2.0 application.

My stored procedure is simple, see below

CREATE PROCEDURE sp_DELETE_ORG_COUNCIL_ALL
(
@OrgID as integer
)
As
BEGIN
DELETE FROM dbo.ORG_DISTRICT_COUNCILS
WHERE (OrgID = @OrgID)

END
GO

I have a insert, update and delete triggers setup on the ORG_DISTRICT_COUNCILS table to write to an audit table, my delete trigger is below

CREATE TRIGGER trig_DELETE_ORG_DISTRICT_COUNCILS

ON dbo.ORG_DISTRICT_COUNCILS

FOR DELETE

AS

DECLARE @TableName VARCHAR(50)
DECLARE @PrimaryKey INT
DECLARE @FieldName VARCHAR(100)
DECLARE @ActionType VARCHAR(100)

DECLARE @oldOrgID INT
DECLARE @newOrgID INT

DECLARE @oldDistrictCouncilId INT
DECLARE @newDistrictCouncilId INT

-- OrgID

SELECT @ActionType = 'DELETE'
SELECT @TableName = 'ORG_DISTRICT_COUNCILS'
SELECT @PrimaryKey = (SELECT ID FROM Deleted)
SELECT @FieldName = 'OrgID'
SELECT @oldOrgID = (SELECT OrgID FROM Deleted)
SELECT @newOrgID = (SELECT OrgID FROM Inserted)

INSERT INTO AUDIT_TRAIL(TableName, PrimaryKey, FieldName, ActionType, OriginalValue, NewValue) VALUES(@TableName, @PrimaryKey, @FieldName, @ActionType, @oldOrgID, @newOrgID)

-- DistrictCouncilId

SELECT @ActionType = 'DELETE'
SELECT @TableName = 'ORG_DISTRICT_COUNCILS'
SELECT @PrimaryKey = (SELECT ID FROM Deleted)
SELECT @FieldName = 'DistrictCouncilId'
SELECT @oldDistrictCouncilId = (SELECT DistrictCouncilId FROM Deleted)
SELECT @newDistrictCouncilId = (SELECT DistrictCouncilId FROM Inserted)

INSERT INTO AUDIT_TRAIL(TableName, PrimaryKey, FieldName, ActionType, OriginalValue, NewValue) VALUES(@TableName, @PrimaryKey, @FieldName, @ActionType, @oldDistrictCouncilId, @newDistrictCouncilId)

I have looked on the net and I think that my problem lies with the delete trigger, it works fine with a single delete from my application but when I do a multiple delete I get the error message.

How can I change my delete trigger to stop the error from occuring? Im new to using triggers so all help is much appreciated.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-04 : 10:40:24

[code]
CREATE TRIGGER trig_DELETE_ORG_DISTRICT_COUNCILS
ON dbo.ORG_DISTRICT_COUNCILS
FOR DELETE
AS

DECLARE @TableName VARCHAR(50)
DECLARE @PrimaryKey INT
DECLARE @FieldName VARCHAR(100)
DECLARE @ActionType VARCHAR(100)

DECLARE @oldOrgID INT
DECLARE @newOrgID INT

DECLARE @oldDistrictCouncilId INT
DECLARE @newDistrictCouncilId INT

-- OrgID

SELECT @ActionType = 'DELETE'
SELECT @TableName = 'ORG_DISTRICT_COUNCILS'
SELECT @PrimaryKey = (SELECT ID FROM Deleted)
SELECT @FieldName = 'OrgID'
SELECT @oldOrgID = (SELECT OrgID FROM Deleted)
SELECT @newOrgID = (SELECT OrgID FROM Inserted)

INSERT INTO AUDIT_TRAIL(TableName, PrimaryKey, FieldName, ActionType, OriginalValue, NewValue)
-- ~~VALUES(@TableName, @PrimaryKey, @FieldName, @ActionType, @oldOrgID, @newOrgID)~~
select @TableName, d.ID, @FieldName, @ActionType, d.OrgID, i.OrgID
from deleted d inner join inserted i
on d.ID = i.ID

-- DistrictCouncilId

SELECT @ActionType = 'DELETE'
SELECT @TableName = 'ORG_DISTRICT_COUNCILS'
SELECT @PrimaryKey = (SELECT ID FROM Deleted)
SELECT @FieldName = 'DistrictCouncilId'
SELECT @oldDistrictCouncilId = (SELECT DistrictCouncilId FROM Deleted)
SELECT @newDistrictCouncilId = (SELECT DistrictCouncilId FROM Inserted)

INSERT INTO AUDIT_TRAIL(TableName, PrimaryKey, FieldName, ActionType, OriginalValue, NewValue)
-- ~~VALUES(@TableName, @PrimaryKey, @FieldName, @ActionType, @oldDistrictCouncilId, @newDistrictCouncilId)~~
select @TableName, d.ID, @FieldName, @ActionType, d.DistrictCouncilId, i.DistrictCouncilId
from deleted d inner join inserted i
on d.ID = i.ID
[/code]

Kristen
Test

22859 Posts

Posted - 2007-02-04 : 10:50:49

You need to build your trigger to handle multiple records (in a single batch). It is [currently] built assuming that the trigger will only handle a single record.

So you can't have:

SELECT @PrimaryKey = (SELECT ID FROM Deleted)

because if there are multiple rows in [deleted] it will give you an error (well, technically not in all circumstances, but it will select one at random, which I'm sure is not what you want!)

So instead of trying to put the Values from the columns in the [deleted] pseudo-table into @Variables, just insert them directly into your AUDIT_TRAIL table:


INSERT INTO AUDIT_TRAIL(TableName, PrimaryKey, FieldName, ActionType, OriginalValue, NewValue) 
SELECT 'ORG_DISTRICT_COUNCILS', 
	D.ID, 
	'OrgID', 
	'DELETE', 
	D.OrgID, 
	I.OrgID
FROM	inserted AS I	-- This may return multiple rows
	LEFT OUTER JOIN deleted AS D
		ON D.OrgID = I.OrgID

By the by, in my experience having a single table for auditing pans out much worse that having an audit_table matching what physical table that you want to audit. My views on the two approaches are here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Trigger,Triggers,audit+to+changes

Kristen

Kristen
Test

22859 Posts

Posted - 2007-02-04 : 10:54:29

"from deleted d inner join inserted i"

As this is a DELETE trigger I don't reckon [inserted] will have anything in it, will it?

I did an OUTER JOIN in my example in case it was also used for UPDATE as well as DELETE.

You need to get rid of those

SELECT @foo = (SELECT foo FROM Deleted)

too - I reckon that's causing the 'Subquery returned more than 1 value' error - ordinarily it wouldn't (unless you use "SET @foo=" rather than "SELECT @foo="), but maybe the Trigger is being treated as a sub-query of the actual outer DELETE ?

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources