We are working on setting a trigger that would add an extra month to a membership if they renew early. We have got the trigger to work and are working on a second trigger to updated the acknowledgement letter. We can get the second trigger to work if the membership comes through the ticketing module but it will not work when going through contributions. What gives? Anyone have an advice? See the code below. We thought maybe it was a timing issue hence the delay but that is not working either.
On the Acknowledgment letter, get the new membership expr_dt and update the ack letter for the contribution
adapted from the report LRPS_MEMBERSHIP_UPDATE******************************************************************************************************************/--declare @newexp_dt datetime;--WAITFOR DELAY '00:00:02'if exists (select * from inserted a join T_TRANSACTION t on a.trn_no=t.transaction_no and t.order_no is null join TX_CONT_MEMB xm on a.cont_ref_no=xm.cont_ref_no join TX_CUST_MEMBERSHIP m on xm.cust_memb_no=m.cust_memb_no and m.current_status=3 join T_MEMB_LEVEL ml on m.memb_level=ml.memb_level and ml.category=3 -- basic where a.letter_id=12 and a.camp_desc like '%Basic Membership' ) --coming through contributions --select * into #tmp from inserted; --WAITFOR DELAY '00:00:02' update a set a.expr_dt = m.expr_dt from inserted i join T_ACK_EXT a on i.cont_ref_no=a.cont_ref_no --and i.letter_id=a.letter_id join TX_CONT_MEMB xm on i.cont_ref_no=xm.cont_ref_no join TX_CUST_MEMBERSHIP m on xm.cust_memb_no=m.cust_memb_no and m.current_status=3