Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 301
  • Last Modified:

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

0
matthewdacruz
Asked:
matthewdacruz
  • 3
  • 2
1 Solution
 
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 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
 
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now