Hep with Trigger That Updates Multi-Rows 1

[Sydney1]

Good Morning,

I've been attempting to create a trigger that updates multi rows based upon the date criteria of what was entered into the the inserted table. Even after reviewing other examples on this site of triggers (some used the inserted and deleted tables joined together), I haven't been able to get it right. I currently have the following code

CREATE TRIGGER UpdateRundate ON dbo.Table2
after update
AS

if (select inserted.rundate from inserted  inner join dbo.table2 on inserted.FCReferralID = dbo.table2.FCReferralID )<'1/1/2001'

Begin
update dbo.table2
set dbo.table2.rundate = '1/1/2007' 
from inserted  inner join dbo.table2 on inserted.FCReferralID = dbo.table2.FCReferralID 
where inserted.rundate <'1/1/2001'
	
    if @@error <>0 
	begin
	raiserror ('Error: Update not correct',16,1)
	rollback transaction
	return
	end
	
     end

Else

    if @@Trancount >0
     begin
Commit Transaction
     end
Else
  begin
  return
  end

When I attempt to update more than one record I get the following error message "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

Any assistance with this trigger would be greatly appreciated.

Sydney

 

[Otto]

if (select count(inserted.rundate) from inserted inner join dbo.table2 on inserted.FCReferralID = dbo.table2.FCReferralID where inserted.rundate<'1/1/2001')>0 begin
...

 

[Sydney1]

Otto,

It works great. Thanks a bunch, I really appreciate your help.

Sydney

 

[ESquared]

May I suggest using exists instead of count as best practice:

if exists (
   select *
   from
     inserted
     inner join dbo.table2 on inserted.FCReferralID = dbo.table2.FCReferralID
   where inserted.rundate < '1/1/2001'
) begin

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]