SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Delete Trigger problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

GaryW
Starting Member

1 Posts

Posted - 02/04/2007 :  10:21:32  Show Profile  Reply with Quote
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)

Singapore
17586 Posts

Posted - 02/04/2007 :  10:40:24  Show Profile  Reply with Quote

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



KH

Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 02/04/2007 :  10:50:49  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 02/04/2007 :  10:54:29  Show Profile  Reply with Quote
"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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000