trigger doesn't work. why?

[karen4201]

I've developed a trigger that runs for an UPDATE on table A. It simply calculates some information and updates table B when table A has an update.

It works fine if the update is on only 1 item in table A. However, if table A has multiple items that are updated, then the trigger returns 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.
The statement has been terminated.

After reviewing the query and subqueries, there are none that return multiple resuts, so I don't understand the error message. Any ideas why this has trouble? Does a Trigger behave in a certain manner when the update applies to multiple items?

Thanks in advance.

 

[SQLDenis]

a trigger fires per statement not per row
Can you post the code

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[karen4201]

Here is a very modified version of the trigger. It's enough to show the logic of the trigger, but I had to modify some info so that I could post the question:

CREATE TRIGGER [person_tbl_trigger] ON [dbo].[person_tbl]
FOR INSERT, UPDATE, DELETE
AS

declare @my_id varchar(100)
declare @parent_id varchar(100)
declare @my_title varchar(150)
declare @my_grade varchar(100)
declare @cnt_pass int
declare @cnt_fail int

select @my_id = (select my_id from Inserted)
if @my_id is null
select @my_id = (select top 1 my_id from Deleted)
select @parent_id = (select top 1 dbo.getParentId( @my_id ) )
select @my_title = (select top 1 dbo.getTitle( @my_id ) )
select @my_grade = (select top 1 dbo.getGrade( @my_id ) )
select @cnt_pass =
(select top 1 count(c.passed) from person_tbl c where c.my_id = @my_id)
select @cnt_fail =
(select top 1 count(c.failed) from person_tbl c where c.my_id = @my_id)


-- if the row has not already been created, then we create a new row with an insert-operation
if 0 = (select top 1 count(*) from person_tbl where my_id = @my_id)
insert into person_tbl(my_id,parent_id, my_title, cnt_pass, cnt_fail, my_grade)
values(@my_id,@parent_id,@my_title, @cnt_fail,@cnt_pass,@my_grade)
else
update person_tbl
set
parent_id = @parent_id
, my_title = @my_title
, cnt_fail = @cnt_fail
, cnt_pass = @cnt_pass
, my_grade = @my_grade
where
my_id = @my_id

 

[SQLDenis]

you error is on this line

select @my_id = (select my_id from Inserted)

if you insert or update 2 rows or more then inserted will contain 2 rows or more and the statement will fail

you probably want to do something like this

update p
set
parent_id = dbo.getParentId( my_id ),
my_title = dbo.getTitle( my_id )
etc
etc
from person_tbl p join inserted i on p.my_id =i.my_id

Read up on triggers in BOL

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[karen4201]

wow. that's it!

Thanks