Improve company productivity with a Business Account.Sign Up

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

Update Trigger help

Hi Experts,

I need help with an UPDATE TRIGGER for MySql.

I have two mysql databases.
I want to update 2 columns in database 2 by a user_id when a date column in database 1 is updated.
I only want to do the updates if they are a specific product id.

Is what I have done below the best way to write an update trigger.
I get an "unknow table 'Database1.xxx_users_products_jn.' in fieldlist" error
Does this logic work?

Here is what I have done.
Please advise on how to make this work

Thanks

Matt


BEGIN
 If Database1.xxx_users_products_jn.product_id in (1,2,3,4,11,12,13,14,15,26,27,42,53) then
       UPDATE Database2.usrprofiles
        SET
              Database2.usrprofiles.Active = 1,
            Database2.usrprofiles.DisableDate = NEW.Database1.access_end_date
        WHERE
              Database2.usrprofiles.user_id = Database1.xxx_users_products_jn.user_id
            AND Database1.xxx_users_products_jn.access_end_date >= NOW()
            ;
 END if;
END
0
matthewdacruz
Asked:
matthewdacruz
  • 2
  • 2
1 Solution
 
Ray PaseurCommented:
It looks like you've obscured the database and table names for this question.  Can you use phpMyAdmin or a similar tool to verify that Database1.xxx_users_products_jn actually exists?  The name is probably case-sensitive.
0
 
matthewdacruzAuthor Commented:
Figured out what i had to do. I removed all the database1.table_names and it worked.

Is this the best way of writing the trigger though. The logic
0
 
Ray PaseurCommented:
Then I'm guessing you have selected database1, so that is the assumed data base when looking for a table.

While I think a TRIGGER is certainly appropriate for this, you could probably also do the same thing with a couple of if() statements in program code.  You might also want to wrap this in a TRANSACTION.  These SitePoint articles appear to be pretty spot-on.
http://www.sitepoint.com/database-triggers-events/
http://www.sitepoint.com/how-to-create-mysql-triggers/
0
 
matthewdacruzAuthor Commented:
Thanks Ray, That helped
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.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

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