?
Solved

mySQL Delete Trigger & php

Posted on 2011-09-29
2
Medium Priority
?
506 Views
Last Modified: 2012-06-27
I want to create a delete trigger that will run before a record gets deleted, however I would like to get some external data passed to this trigger and have no idea how to do this or even if it can be done.

The delete trigger will take the record about to be deleted and paste it into another table, but I also want it to take the username from php - value of $_SESSION['login_user'] which holds the username value of whoever is logged into the app and is trying to delete the record.

Since the Trigger is in the database and not in the php code, how do i get this info to it??
0
Comment
Question by:saunaG
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 24

Expert Comment

by:johanntagle
ID: 36818295
I don't think MySQL triggers have that capability.  It can only use data from the table involved and (I think) from the MySQL session.  Suggest you do the copying to another table in your php code.  OR, still make the trigger, then have the php code update the row in the other after it calls the delete.  This was you will always copy the row to the other table, whether the delete is called by php or not.
0
 
LVL 79

Accepted Solution

by:
arnold earned 2000 total points
ID: 36864864
Which version of mysql are you using?
http://dev.mysql.com/doc/refman/5.5/en/triggers.html
This thread might be what you are looking for
http://forums.mysql.com/read.php?99,140057,140057
The last post seems to be the example you are looking for
http://forums.mysql.com/read.php?99,140057,141249#msg-141249

The last part dealing with the take value, is not possible.

You should create a log table which will record transactions performed on the site at which point you could update/record which records were affected.
i.e. instead of actually deleting records, you could tag the record as delete using a deleted column and then reference who issued the delete. You would then have a cleanup process that will go through the table looking for rows marked for deletion and perform the transaction you want i.e. get the row into another table with the reference to the user that deleted it.

I think this approach gets to the point you want to achieve.
0

Featured Post

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question