Trigger talk- Need some assistance

Question

Trigger talk- Need some assistance

davidsalazar01

SSCarpal Tunnel

Points: 4072
More actions
March 17, 2005 at 2:45 pm

#163979

All ,
I'd like to add logic to an existing trigger. This is what I'd like to do:
on update

when CancelDate is less than or equal to getdate() - set ExtendedPaymentsInfo.PaymentStatusID = 3

also I need to check if PaymentEndDate = getdate() - set ExtendedPaymentsInfo.PaymentStatusID = 3

How would I code this? Thanks a lot!!!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

it's pretty easy to do but it'd be better if you could post the rest of the trigger so we could put the code into its rightfull place instead of just sending you an exemple.

davidsalazar01 SSCarpal Tunnel Points: 4072 More actions · Answer 2

create trigger tr_upd_payment_bal

on ExtendedPayment

for update as

if update(completed)

begin

update ExtendedPaymentsInfo

set ExtendedPaymentsInfo.PaymentBalance = ExtendedPaymentsInfo.PaymentBalance - deleted.paymentamount

from ExtendedPaymentsInfo,deleted,ExtendedPayment

where deleted.extpaymentsinfoid = ExtendedPaymentsInfo.ExtPaymentsInfoID

and ExtendedPayment.extpaymentsinfoid = ExtendedPaymentsInfo.ExtPaymentsInfoID

end

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

create trigger tr_upd_payment_bal

on ExtendedPayment

for update as

if update(completed)

begin

update ExtendedPaymentsInfo

set ExtendedPaymentsInfo.PaymentBalance = ExtendedPaymentsInfo.PaymentBalance - deleted.paymentamount

, ExtendedPaymentsInfo.PaymentStatusID =

CASE WHEN CancelDate <= GETDATE() OR CONVERT(varchar(10), PaymentEndDate, 111) = CONVERT(varchar(10), getdate(), 111) THEN 3 ELSE ExtendedPaymentsInfo.PaymentStatusID END

from ExtendedPaymentsInfo,deleted,ExtendedPayment

where deleted.extpaymentsinfoid = ExtendedPaymentsInfo.ExtPaymentsInfoID

and ExtendedPayment.extpaymentsinfoid = ExtendedPaymentsInfo.ExtPaymentsInfoID

end

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

oops.. I didn't see the if Update(complete). Do the rows that need to be modified change if that column is not updated?..

Anyways you get the idea. Msg me if you need more help.

davidsalazar01 SSCarpal Tunnel Points: 4072 More actions · Answer 5

davidsalazar01

SSCarpal Tunnel

Points: 4072

March 18, 2005 at 8:59 am

#547800

Thanks Remi! I'll give it try.

davidsalazar01 SSCarpal Tunnel Points: 4072 More actions · Answer 6

Remi,

I'm still having trouble with this logic. The developer came back and gave me the following logic to use in an sproc:

If CancelDate <= getDate() then status = 3 ' cancelled

elseif CancelDate is null AND PaymentEndDate <= getDate() then status = 1 ' completed

else status = 0 ' pending

Thanks again,

David

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

CASE WHEN CancelDate <= GETDATE() THEN 3

WHEN CancelDate IS NULL AND CONVERT(varchar(10), PaymentEndDate, 111) <= CONVERT(varchar(10), getdate(), 111) THEN 1

ELSE 0

END

davidsalazar01 SSCarpal Tunnel Points: 4072 More actions · Answer 8

Thanks Remi! I'll try this. A developer created a Recurring Payment Service with .NET that processes the Recurring Payment transactions that are scheduled to process for the current date. But in some cases, the doctor's office might have a patient who comes in and wants to just pay off the remaining balance. That's what I'm trying to do with this logic somehow is set the CancelDate to certain statuses based on the criteria. Since I'm kinda new to DBA development I've been struggling with this.

I really appreciate your help thus far!

- David