• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 515
  • Last Modified:

mySQL Delete Trigger & php

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??
1 Solution
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.
Which version of mysql are you using?
This thread might be what you are looking for
The last post seems to be the example you are looking for

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.
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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