Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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', 'admin@local.com', 'admin@local.com', 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?

share|improve this question
    
You want to run the truncate before or after your statement ? –  Up_One Feb 27 '14 at 13:46
    
@Up_One I want to run first TRUNCATE and after the SQL sentence –  ReynierPM Feb 27 '14 at 13:47

2 Answers 2

up vote 1 down vote accepted

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', 'admin@local.com', 'admin@local.com', 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).

share|improve this answer

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.

share|improve this answer
    
I would like to see this done ! Remember that is mysql! How this would be implemented? –  Up_One Feb 27 '14 at 15:14
    
mysqltutorial.org/mysql-stored-procedure-tutorial.aspx should cover this nicely. –  Sascha Rambeaud Feb 28 '14 at 9:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.