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
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
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This statement is not correct:
select @bitCompany as F_SELCompanyAddressCount(s
This was the other problem.
THANKS!!!
ASKER
Perfect with both of those!
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
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
ASKER
IF @bitCompany > 1
Begin
update
update
end