SQL Server trigger wont run all updates inside if...

Can anyone tell me why I cannot get the first statement to execute under the IF statement?  If I move the                               update t_address
                              set address2 = @i_CompanyIdentifier
to the bottom it fires.

But then the next update doesn't fire.

There doesn't seem to be an END IF; like in Oracle so I am struggling to figure this out.  Everything else executes fine just the first statement after the IF never fires.  How can that be possible?

Please give me your thoughts.

Thanks,

B
drop trigger TR_XREFCompanyAddress
go
 
CREATE TRIGGER TR_XREFCompanyAddress
ON T_XREFCompanyAddress
 
AFTER INSERT
 
AS
 
begin
 
declare @sysdate as smalldatetime
set @sysdate = getdate()
 
declare @i_CompanyIdentifier		as		uniqueidentifier
set @i_CompanyIdentifier = (
							select IDCompany 
							from inserted
							)
 
declare @i_AddressIdentifier		as		uniqueidentifier
set @i_AddressIdentifier = (
							select IDAddress 
							from inserted
							)
 
declare @bitCompany					as		smallint
select @bitCompany as F_SELCompanyAddressCount(select @i_CompanyIdentifier)
 
	Begin
 
			IF @bitCompany > 1
 
					update t_address
					set address2 = @i_CompanyIdentifier
 
					UPDATE T_XREFCompanyAddress
					SET Omega = @sysdate
					WHERE @i_CompanyIdentifier = IDCompany
					and Omega > @sysdate
					and IDAddress <> @i_AddressIdentifier
 
					UPDATE T_Address
					SET Omega = @sysdate
					FROM 
					  T_Address					TA
					, T_XREFCompanyAddress		TXRCA
					WHERE @i_CompanyIdentifier = TXRCA.IDCompany
					AND TXRCA.IDAddress = TA.ID
					and TA.Omega > @sysdate
					and TA.ID <> @i_AddressIdentifier
					
					update t_address
					set address3 = @i_AddressIdentifier	
					
	end	
 
end
 
GO

Open in new window

LVL 1
cyimxtckAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you have to do this:

if
BEGIN
 
 
END


if you do this:
if
 
 

it works like this:

if
BEGIN
 
END

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cyimxtckAuthor Commented:
If I do it that way none of them fire:

IF @bitCompany > 1
                              
Begin

update

update

end
0
dqmqCommented:
Angel's comment is spot on.  In addition, I don't think @bitCompanyis getting set as you intend, thus are NEVER executing the first line.  In that case, if you follow Angel's instructions, the entire block will be skipped.  

This statement is not correct:

select @bitCompany as F_SELCompanyAddressCount(select @i_CompanyIdentifier)
 
I don't know what you intend, but that statement does NOT assign a value to @bitCompany!!!!
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

cyimxtckAuthor Commented:

This statement is not correct:

select @bitCompany as F_SELCompanyAddressCount(select @i_CompanyIdentifier)

This was the other problem.

THANKS!!!
0
cyimxtckAuthor Commented:
Perfect with both of those!
0
ErezMorCommented:
every time you need to execute a block of sentences as one unit, the block has to start with "begin" and end with "end"
in if statements, no exeption, create a block, otherwise only the first follwing sentence will be considered as related to the if
so if the fix proved that none of them fire as you said, your condition result as false
what really happens according to you is that the first sentence right after the if doesnt fire, no? that's because the if sentence proved false and the reason why consecutive sentences did execute is that they are not considered to be conditioned by anything
ok i said it twice . enough
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.