Help with Trigger

[MISdad]

I'm creating a trigger to change a value in one table based on values in other tables. The problem I'm having is that there is not always a corresponding record in the other tables, and the trigger doesn't appear to assign a null value to the variable when that record doesn't exist. Any clues on what I can look for? Here's the code (sorry it's kind of long).

Thanks,
Jim


create trigger cd.t_activwork_update on ar.activwork
for insert,update
as

declare @activworkclientid char (18)
declare @activworkuniqueid char (18)
declare @clientuniqueid char (18)
declare @txgridclientid char (18)
declare @txplanexpdate datetime
declare @activitydate datetime
declare @program char (5)
declare @activity char (5)
declare @recipient char (5)
declare @assessmentdate datetime
declare @txoverride char (1)
declare @activplan char (1)

select @activworkclientid = inserted.clientid_c
from inserted
select @activworkuniqueid = inserted.uniqueid_c
from inserted
select @clientuniqueid = client.uniqueid_c
from ar.client client, inserted
where client.uniqueid_c = inserted.clientid_c
select @txgridclientid = txgrid.clientid_c
from cd.txplangrid txgrid, inserted
where txgrid.clientid_c = inserted.clientid_c
select @txplanexpdate = left(txgrid.exdate_dt, 11)
from cd.txplangrid txgrid, inserted
where txgrid.clientid_c = inserted.clientid_c
order by txgrid.exdate_dt
select @activitydate = inserted.activitydate_d
from inserted
select @program = inserted.program_c
from inserted
select @activity = inserted.activity_c
from inserted
select @recipient = inserted.recipient_c
from inserted
select @assessmentdate = left(presentprob.assess1_dt, 11)
from cd.presentprob presentprob, inserted
where presentprob.clientid_c = inserted.clientid_c
order by presentprob.assess1_dt
select @txoverride = txgrid.override_c
from cd.txplangrid txgrid, inserted
where txgrid.clientid_c = inserted.clientid_c
select @activplan = txgrid.activplan_c
from cd.txplangrid txgrid, inserted
where txgrid.clientid_c = inserted.clientid_c

--This will allow someone to override the NOTX
if @txoverride = 'N'
begin

-- This checks for MI programs and non crisis, assessment or tx plan
services Also checks to see if the
--service date is before the tx plan expiration date.
if @program in ('010', '030', '110', '120', '140', '150', '160', '210',
'220', '240',
'270', '330', '340', '350', '460', '550') and
@activity not in ('10R0', '13R0', '1023', 'IMR0', 'IQR0', 'IRR0',
'0MR0', '0QR0', '0CR0','0DR0', 'DROP', 'BAL0', 'ATV2',
'ATVT', 'CO1C', 'HA1C', 'HD1C', 'HAL5', 'LI25',
'PR1C', 'PF2C', 'BOOK') and
left(@recipient, 1) in ('1', '3') and
@recipient not in ('10NB', '10NP', '30NB')

-- This will update the activity being scheduled to NOTX because the Tx
Plan is expired
begin
update ar.activwork
set recipient_c = 'NOTX'
where (@activitydate > @txplanexpdate or @txplanexpdate <
left(getdate(), 11)) and
ar.activwork.uniqueid_c = @activworkuniqueid


-- This will update the activity being scheduled to NOTX because the Tx
Plan is not present within 45 days of assessment
update ar.activwork
set recipient_c = 'NOTX'
where @txplanexpdate is null and @assessmentdate < left(getdate() -
45, 11) and
ar.activwork.uniqueid_c = @activworkuniqueid
end
else

--This checks for SA programs and non crisis, assessment or tx plan
services. Also checks to see if the
--service date is before the tx plan expiration date.
if @program in ('080', '140','290', '570', '600', '610', '620', '640',
'660', '690', '730') and
@activity not in ('1000', 'INTA', '0003', '0005', 'DROP', 'BAL0',
'BOOK') and
left(@recipient, 1) in ('1', '3') and
@recipient not in ('10NB', '10NP', '30NB', '30NP', '1EIG', '1EII')

--This will update the activity being scheduled to NOTX because the TX
Plan is expired or has no end date
begin
update ar.activwork
set recipient_c = 'NOTX'
where (@activitydate > @txplanexpdate or @txplanexpdate is null or
@txplanexpdate < left(getdate(), 11))
and ar.activwork.uniqueid_c = @activworkuniqueid
end

--This is the end statement for the override statement above
end

 

[JayKusch]

I surely hope your table is not inserted or updated too frequently because that is one monster of a trigger - lol.

You can "try" setting the variables to a NULL value after you declare them and before you try to find a record per to populate them.

Thanks

J. Kusch

 

[vongrunt]

AFAIK variables are already set to NULL after being declared.

How many rows get updated at once (with a single statement)? Always only one?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[MISdad]

JayKusch,

There is actually quite a bit of inserting and updating done in this table. I know it looks like a lot of code, but it actually runs pretty well. Basically, we just want to stop services from being billed if certain criteria is not met and it's a whole lot easier for the system to do it on the front end than it is for someone to try and catch it on the back end. So, the couple of extra seconds to save the record is worth the payoff (assuming I can get this thing working correctly).

Vongrunt,

I was also under the impression that the variables were set to null once declaired. That's why I don't understand why I can't get this to work. Yes, you are correct. One and only one record is updated every time.

Maybe this will help clarify. Everything is working with this trigger unless a record does not exist in the txgrid table for that client. Services can only be billed if there is a valid record in the txgrid. If one doesn't exist, then the variable should be null and allow me to make the change - but it doesn't work. I'm sure I'm just missing something simple here that I can't put my finger on.