Update trigger question

Hi Experts

I am wanting some advice on an update trigger

Basically what happens is paypal will update a column called dwslive_pro.imi_user_detail.expiry_date.

When this record is updated we need it to update another database table called ne_dap.dap_users_products_jn.access_end_date.

In the where clause we only want the trigger to update the specific rows linked row.

Do we use old.dwslive_pro.imi_user_detail.username as the uniqe key in the where clause?
What is the proper way to call the rows email for a trigger? we only want that specif rows email used as the unique reference

The email address is unique in the database.

Thanks




UPDATE
ne_dap.dap_users_products_jn
JOIN ne_dap.dap_users ON ne_dap.dap_users.id = ne_dap.dap_users_products_jn.user_id
JOIN dwslive_pro.imi_user_detail ON dwslive_pro.imi_user_detail.username = ne_dap.dap_users.email
JOIN DWS3.usrprofiles ON DWS3.usrprofiles.user_id = ne_dap.dap_users.id
SET
ne_dap.dap_users_products_jn.access_end_date = dwslive_pro.imi_user_detail.expiry_date
WHERE
ne_dap.dap_users.email = old.dwslive_pro.imi_user_detail.username AND
DWS3.usrprofiles.user_id = ne_dap.dap_users.id

Open in new window

matthewdacruzAsked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
Since the trigger is on dwslive_pro.imi_user_detail table, I believe you want something more like this:

UPDATE ne_dap.dap_users_products_jn upjn
JOIN ne_dap.dap_users u ON u.id = upjn.user_id
JOIN DWS3.usrprofiles p ON p.user_id = u.id
SET upjn.access_end_date = NEW.expiry_date
WHERE u.email = OLD.username
;

Open in new window

0
 
matthewdacruzAuthor Commented:
Updated Trigger statement
UPDATE
ne_dap.dap_users_products_jn
JOIN ne_dap.dap_users ON ne_dap.dap_users.id = ne_dap.dap_users_products_jn.user_id
JOIN dwslive_pro.imi_user_detail ON dwslive_pro.imi_user_detail.username = ne_dap.dap_users.email
JOIN DWS3.usrprofiles ON DWS3.usrprofiles.user_id = ne_dap.dap_users.id
SET
ne_dap.dap_users_products_jn.access_end_date = new.dwslive_pro.imi_user_detail.expiry_date
WHERE
ne_dap.dap_users.email = old.dwslive_pro.imi_user_detail.username AND
DWS3.usrprofiles.user_id = ne_dap.dap_users.id

Open in new window

0
 
matthewdacruzAuthor Commented:
What I want to find out is how I only update the related row by using the username column.
Do I use OLD or NEW?
0
 
Kevin CrossChief Technology OfficerCommented:
Since DW3 table does not seem to be involved at all, unless it is to validate that you only update users with a user profile, you should be able to remove "JOIN DWS3.usrprofiles p ON p.user_id = u.id" entirely.
0
 
matthewdacruzAuthor Commented:
That did the trick, sorry for the delay as I needed to wait for transactions to take place to make sure it worked properly.

Thanks

Matt
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.