MySQL trigger start error
I need to create a MySQL trigger that will write the user id to the delete table row statement, which should fit in one query as I am using PHP PDO. This is what I have come up with so far:
I need a way to pass the user id in a delete request, even if it doesn't matter to delete the action to be performed:
Typically, the request will look like this:
DELETE FROM mytable WHERE mytable.RowID = :rowID
If I could use multiple queries in my application, I would do it like this:
SET @userID := :userID;
DELETE FROM mytable WHERE mytable.RowID = :rowID;
Thus, the @userID variable will be set before the trigger fires and can use it. However, since I need to compress my delete statement into a single request, I came up with the following:
DELETE FROM mytable
WHERE CASE
WHEN @userID := :userID
THEN mytable.RowID = :rowID
ELSE mytable.RowID IS NULL
END
Just a side note: RowID will never be empty as this is the primary key. Now I need to create a delete trigger to register the user ID in the audit table, however I assume that in this case the trigger will be fired before the delete request itself, which means the @userID variable will not be created? It was my idea to pass it as the trigger value.
I think I am close to a solution, but this problem is blocking. How can I pass a user id value to a trigger without having multiple requests in the statement? Any thoughts, suggestions?
source to share
You can use NEW / OLD mysql trigger extensions. Link: http://dev.mysql.com/doc/refman/5.0/en/trigger-syntax.html
Here's some sample code:
drop table `project`;
drop table `projectDEL`;
CREATE TABLE `project` (
`proj_id` int(11) NOT NULL AUTO_INCREMENT,
`proj_name` varchar(30) NOT NULL,
`Proj_Type` varchar(30) NOT NULL,
PRIMARY KEY (`proj_id`)
);
CREATE TABLE `projectDEL` (
`proj_id` int(11) NOT NULL AUTO_INCREMENT,
`proj_name` varchar(30) NOT NULL,
`Proj_Type` varchar(30) NOT NULL,
PRIMARY KEY (`proj_id`)
);
INSERT INTO `project` (`proj_id`, `proj_name`, `Proj_Type`) VALUES
(1, 'admin1', 'admin1'),
(2, 'admin2', 'admin2');
delimiter $
CREATE TRIGGER `uProjectDelete` BEFORE DELETE ON project
FOR EACH ROW BEGIN
INSERT INTO projectDEL SELECT * FROM project WHERE proj_id = OLD.proj_id;
END;$
delimiter ;
DELETE FROM project WHERE proj_id = 1;
SELECT * FROM project;
SELECT * FROM projectDEL;
source to share