Execute SQL query after execute TRUNCATE in a table

Question

I have a table called fos_user_user where I have a default record "always". Since I'm testing all the time and sometimes I need to clean that table I'm asking if there is any way to run the following query any time I run the TRUNCATE command on the table:

INSERT INTO `fos_user_user` (`id`, `username`, `username_canonical`, `email`, `email_canonical`, `enabled`, `salt`, `password`, `last_login`, `locked`, `expired`, `expires_at`, `confirmation_token`, `password_requested_at`, `roles`, `credentials_expired`, `credentials_expire_at`, `created_at`, `updated_at`, `date_of_birth`, `firstname`, `lastname`, `website`, `biography`, `gender`, `locale`, `timezone`, `phone`, `facebook_uid`, `facebook_name`, `facebook_data`, `twitter_uid`, `twitter_name`, `twitter_data`, `gplus_uid`, `gplus_name`, `gplus_data`, `token`, `two_step_code`) VALUES
(1, 'admin', 'admin', '[email protected]', '[email protected]', 1, 'qd3bxv0cg34s0ow0owsso848k8g4g8s', 'w0PbwCKqCJmEzr7VewCJIq0M8t7+35wS4Tfbzj2LaRKNg9wNv8ic/1TY+y5BTJXoftMENobutsizDluVKM/R/Q==', '2014-02-25 02:36:56', 0, 0, NULL, NULL, NULL, 'a:1:{i:0;s:10:"ROLE_ADMIN";}', 0, NULL, '2014-02-13 02:36:09', '2014-02-25 02:36:56', NULL, NULL, NULL, NULL, NULL, 'u', NULL, NULL, NULL, NULL, NULL, 'null', NULL, NULL, 'null', NULL, NULL, 'null', NULL, NULL);

Any advice?

• Up_One: You want to run the truncate before or after your statement ?
• ReynierPM: @Up_One I want to run first TRUNCATE and after the SQL sentence

Answer 1

You just need to use a AFTER DELETE TRIGGER:

USE `test`;
DELIMITER $$
DROP TRIGGER IF EXISTS test.for_user_user_AD$$
USE `test`$$
CREATE DEFINER = CURRENT_USER TRIGGER `test`.`for_user_user_AD` AFTER DELETE ON `for_user_user` FOR EACH ROW
BEGIN
 INSERT INTO `fos_user_user` (`id`, `username`, `username_canonical`, `email`, `email_canonical`, `enabled`, `salt`, `password`, `last_login`, `locked`, `expired`, `expires_at`, `confirmation_token`, `password_requested_at`, `roles`, `credentials_expired`, `credentials_expire_at`, `created_at`, `updated_at`, `date_of_birth`, `firstname`, `lastname`, `website`, `biography`, `gender`, `locale`, `timezone`, `phone`, `facebook_uid`, `facebook_name`, `facebook_data`, `twitter_uid`, `twitter_name`, `twitter_data`, `gplus_uid`, `gplus_name`, `gplus_data`, `token`, `two_step_code`) VALUES
 (OLD.id, 'admin', 'admin', '[email protected]', '[email protected]', 1, 'qd3bxv0cg34s0ow0owsso848k8g4g8s', 'w0PbwCKqCJmEzr7VewCJIq0M8t7+35wS4Tfbzj2LaRKNg9wNv8ic/1TY+y5BTJXoftMENobutsizDluVKM/R/Q==', '2014-02-25 02:36:56', 0, 0, NULL, NULL, NULL, 'a:1:{i:0;s:10:"ROLE_ADMIN";}', 0, NULL, '2014-02-13 02:36:09', '2014-02-25 02:36:56', NULL, NULL, NULL, NULL, NULL, 'u', NULL, NULL, NULL, NULL, NULL, 'null', NULL, NULL, 'null', NULL, NULL, 'null', NULL, NULL);
END
$$
DELIMITER ;

This TRIGGER will INSERT the same row you deleted. (I left the same parameters for the others fields).

Answer 2

How about creating a stored procedure that includes your truncate command and the insert statement.

Since that insert statement only makes sense specifically after calling truncate on your fos_user_user table, you could just as well run 'exec .ufn_Truncate_fos_user' or something like that.

• Up_One: I would like to see this done ! Remember that is mysql! How this would be implemented?
• Sascha Rambeaud: mysqltutorial.org/mysql-stored-procedure-tutorial.aspx should cover this nicely.