Getting trigger to fire conditionally (MySQL / PHP)

Question

I want a trigger to fire only when UPDATEs are user-initiated (not me running updates from the MySQL command line).

What's the 'industry standard' for achieving this? I have been trying unsuccessfully to detect a variable passed in with the query (i.e. @user_update=true), but without success. A colleague of mine suggested a way to do it would be to add a new column into the table containing the trigger: 'fire_trigger', for example. And fill this with 'true' when running the SQL from the code, and ensure that this is present in order for the trigger to fire.

Any help much appreciated!

Answer 1

Disable the trigger before doing command-line updates and re-enable it after.

But you're doing this wrong, conceptually.

Why shouldn't the trigger run when you use the command line? I Assume this is some sort of auditing trigger, that captures who updated a row. You at the command line are a "who" too.

Comment from OP:

Yes - good point! :) It is a "dd/mm/yyyy hh:mm - User A changed First name from "Jane" to "Joan"" type thing. I just don't want it to fill the history table during the test phase when I'm potentially updating rows 'manually'.

Then either disable the trigger, or just delete the added history rows after. Even better, do your testing on a separate database, and just don't worry about this at all.

Answer 2

Create 2 mysql users: one that is used by the application(so it will identify your users) and one that you will use when connecting through the mysql client.

In the update trigger, check for the user with CURRENT_USER() and take action only when it's the application user.

Hope it helps!