Trigger ON Delete - 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

Transact-SQL (2000)

Trigger ON Delete

Author

Topic

dejjan
Yak Posting Veteran

99 Posts

Posted - 2004-07-06 : 03:41:36

Hello, I have a problem with trigger ON delete.
I have a table ORDERS and I want to execute this statement:

Delete ORDERS where name is null (11 rows)

I have a trigger tr_Orders:

CREATE TRIGGER tr_Orders ON dbo.Orders FOR DELETE
AS
BEGIN
if @@rowcount = 0 return
DECLARE @IDENT varchar(30),
@SECTION varchar(20)

SET @IDENT = (select Ident from deleted)
SET @SECTION = (select Section from deleted)

insert into tracking (tabela,Ident_oldvalue,Section_oldvalue,date,compname)
values ('ORDERS',@ident,@Section,host_name())
END

But, when I want to delete those 11 rows, I get a message:

" 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 don't want do delete one by one row.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-07-06 : 04:55:36

you have 5 columns specified in insert and only 4 in values.

insert into tracking (tabela,Ident_oldvalue,Section_oldvalue,date,compname)
values ('ORDERS',@ident,@Section,host_name(), column5)

and doesn't

insert into tracking (tabela,Ident_oldvalue,Section_oldvalue,date,compname)
select 'ORDERS', deleted.Ident, deleted.Section, host_name(), column5
from deleted
do what you need?

Go with the flow & have fun! Else fight the flow :)

nfsoft
Starting Member

36 Posts

Posted - 2004-07-06 : 05:50:25

this seams simple...
The trigger acts as a block transaction where, in fact the aux tables deleted and inserted are multiple row values.
simply use ...

CREATE TRIGGER tr_Orders ON dbo.Orders FOR DELETE
AS
BEGIN
insert into tracking (tabela,Ident_oldvalue,Section_oldvalue,date,compname)
select 'ORDERS',ident ,Section,host_name(), null
from deleted

hope helps

Nuno Ferreira

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources