Calling a sproc from a trigger...Newbie need help

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

Transact-SQL (2000)

Calling a sproc from a trigger...Newbie need help

Author

Topic

evergreen
Starting Member

10 Posts

Posted - 2005-08-03 : 10:41:49

Hi,

I am trying to accomplish the following sql statement from within a trigger. The trigger is

________________________Trigger

ALTER TRIGGER [destinationremove2] ON [dbo].[tbl_brokertable]
FOR UPDATE
as
DECLARE @deleted VARCHAR(100)
declare @brokeridx int

if not update(deleted)
begin
return
end

SELECT @deleted= (SELECT deleted FROM Inserted)
select @brokeridx=(select brokeridx from inserted)
if @deleted='yes'
begin
exec sproc_trigger @brokeridx
end

exec sproc_remove_deleted_brokertable

exec sproc_remove_deleted_destination

if @@error !=0 rollback tran
_________________________________________Error

However, I am getting this error.

Server: Msg 512, Level 16, State 1, Procedure destinationremovedelete, Line 14
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.

______________________________________Sproc

ALTER procedure sproc_trigger
@brokeridx as int
as
update tbl_destination
set deleted='yes'
where brokeridx=@brokeridx

INSERT INTO tbl_brokertable_deleted_records
SELECT *
FROM tbl_brokertable
WHERE (deleted = 'yes')
GO

evergreen
Starting Member

10 Posts

Posted - 2005-08-03 : 10:42:42

Any idea what I am doing wrong. I read that this error indicates that within a trigger to do this operation, I need to use a cursor to go through the recordset. However, I don't understand why this will not work when I call it from a sproc.

Thanks. This delay is really slowing me down.

kapilarya
Yak Posting Veteran

86 Posts

Posted - 2005-08-03 : 10:48:18

select @brokeridx=(select brokeridx from inserted),
select @brokeridx=(select brokeridx from inserted)

are returning you more than one record, that's why you are getting this error.

Kapil Arya

evergreen
Starting Member

10 Posts

Posted - 2005-08-03 : 13:38:20

quote:
Originally posted by kapilarya

select @brokeridx=(select brokeridx from inserted),
select @brokeridx=(select brokeridx from inserted)

are returning you more than one record, that's why you are getting this error.

Kapil Arya

Thanks for your follow up. Actually, the sprocs only contain an update and a deletion. Mulitiple records should be me returned.

evergreen
Starting Member

10 Posts

Posted - 2005-08-03 : 14:00:56

Actually, the original update statement this is assigned to is a single update.

Does that help>

Kristen
Test

22859 Posts

Posted - 2005-08-03 : 15:33:06

Hi evergreen, welcome to SQL Team!

You ought to design your triggers to handle multiple rows - otherwise if you do a bulk-import, or something like

UPDATE MyPrices
SET PriceEach = PriceEach * 0.10

then it ain't going to work!

You could do:


IF (SELECT COUNT(*) FROM inserted) > 1
BEGIN
   RAISERROR ('Multiple row update of MyTable denied', 16, 1)
END

or

IF (SELECT COUNT(*) FROM inserted) = 1
BEGIN

... rest of single row processing logic

END

This will obviously do NOTHING on a multi-row-update, and I therefore think its a Bad Idea! but ...

Kristen

evergreen
Starting Member

10 Posts

Posted - 2005-08-03 : 16:34:19

Thanks for your reply. I still don't understand how I can do this in a trigger.

delete from poopoo where columnxx='yes'

Sorry, I understand you can only do one at at time, however how do I do this simple procedure in a trigger.

evergreen
Starting Member

10 Posts

Posted - 2005-08-03 : 17:03:02

Actually, this delete statement is in the sproc. One of the sprocs actually deletes from another table.

Kristen
Test

22859 Posts

Posted - 2005-08-04 : 00:51:11

Couple of thoughts:

(SELECT deleted FROM Inserted)

"deleted" is a reserve word, use [deleted] whereever you use it as a column name

Change:

select @brokeridx=(select TOP 1 brokeridx from inserted)

but if that makes the problem go away you are still left with the fact that the trigger was being asked to handle multiple rows, and you've only processed one of them

Creating a CURSOR, or using a loop, to walk around the rows, and call the SProc, is your only route [I think!]

Kristen

evergreen
Starting Member

10 Posts

Posted - 2005-08-04 : 18:33:40

Thanks again for follow up.

Sorry, I am not tracking. I don't understand why I cannot do a multi record deletion from within trigger or from a sproc the trigger calls.

In a sproc I can do an operation such as
delete from annoyed where sometable='xxxxx'

I always get this error:

Server: Msg 512, Level 16, State 1, Procedure destinationremovedelete, Line 14
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.

evergreen
Starting Member

10 Posts

Posted - 2005-08-04 : 18:36:07

ALTER TRIGGER [destinationremove2] ON [dbo].[tbl_brokertable]
FOR UPDATE
as

if not update(deleted)
begin
return
end

update dest set
dest.deleted ='yes'
FROM tbl_destination dest
JOIN inserted i ON dest.brokeridx = i.brokeridx
where i.deleted = 'yes'

INSERT INTO tbl_brokertable_deleted_records
SELECT *
FROM tbl_brokertable
WHERE (deleted = 'yes')

exec sproc_remove_deleted_brokertable

exec sproc_remove_deleted_destination

evergreen
Starting Member

10 Posts

Posted - 2005-08-04 : 18:38:17

This was my latest attempt:

ALTER TRIGGER [destinationremove2] ON [dbo].[tbl_brokertable]
FOR UPDATE
as

if not update(deleted)
begin
return
end

update dest set
dest.deleted ='yes'
FROM tbl_destination dest
JOIN inserted i ON dest.brokeridx = i.brokeridx
where i.deleted = 'yes'

INSERT INTO tbl_brokertable_deleted_records
SELECT *
FROM tbl_brokertable
WHERE (deleted = 'yes')

exec sproc_remove_deleted_brokertable

exec sproc_remove_deleted_destination

evergreen
Starting Member

10 Posts

Posted - 2005-08-04 : 18:53:51

this is the error i am getting.

Server: Msg 512, Level 16, State 1, Procedure destinationremovedelete, Line 14
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.

Line 14 is the statment

update dest set
dest.deleted ='yes'
FROM tbl_destination dest
JOIN inserted i ON dest.brokeridx = i.brokeridx
where i.deleted = 'yes'

Kristen
Test

22859 Posts

Posted - 2005-08-05 : 00:22:54

Server: Msg 512, Level 16, State 1, Procedure destinationremovedelete, Line 14

What is "destinationremovedelete" ? The procedure that caused the call to this trigger, or something else that is called by once of your two EXEC's within the trigger?

I should have raised that after your first post :-(

Multi-row updates in your trigger etc. are fine, so I don't think that's the problem now you've removed the ambiguous code.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources