A community in which webmasters can ask for help with topics such as PHP coding , MySQL , IT jobs, web design, IT security.
Current location:homephp forumphp talk in 2009 yearGetting trigger to fire conditionally (MySQL / PHP) - page 1
User InfoPosts
Getting trigger to fire conditionally (MySQL / PHP)#1
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!

posted date: 2009-04-10 10:10:00


Re: Getting trigger to fire conditionally (MySQL / PHP)#2
I had made out the solution of this problem. click to view my topic...

hope that hepls.

posted date: 2009-04-10 10:10:01


Re: Getting trigger to fire conditionally (MySQL / PHP)#3
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.

posted date: 2009-04-10 10:22:00


Re: Getting trigger to fire conditionally (MySQL / PHP)#4
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'.

posted date: 2009-04-10 10:26:00


Re: Getting trigger to fire conditionally (MySQL / PHP)#5
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!

posted date: 2009-04-10 13:35:00


select page: « 1 »
Copyright ©2008-2017 www.momige.com, all rights reserved.