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'm creating a trigger that deletes rows from a table when they are inserted. However, when the trigger executes I get the following message:

MYSQL ERROR: Can't update table 'player_viplist' in stored function/trigger because it is already used by statement which invoked this stored function/trigge

What do I need to do for this trigger to work?

DROP TRIGGER dele;

DELIMITER |

CREATE DEFINER = 'root'@'localhost' TRIGGER dele
  AFTER INSERT
  ON player_viplist
  FOR EACH ROW
BEGIN DELETE FROM player_viplist ; 

END|

DELIMITER ;
share|improve this question
    
What end result are you trying to accomplish? –  Michael - sqlbot Sep 19 '13 at 4:37

2 Answers 2

up vote 0 down vote accepted

Other than the ability to access to the row data of a single row that is available inside the body of a BEFORE-action trigger (which can only be used to examine or modify the row data, not cause the row to no longer exist unless your intention is to throw an exception from inside the trigger), you can't manipulate rows in a table from within a trigger defined against that same table in MySQL. However, it seems like this isn't your actual objective.

I have a system, which after using the INSERT / UPDATE this table because a bug on my system. I need to make this table is empty all the time, or that all INSERT / UPDATE to be used it is canceled.

The problem you described here sounds like a job for the BLACKHOLE Storage Engine, which should be built in to just about any MySQL distribution. If SHOW ENGINES; lists BLACKHOLE, then you have this available.

If you provision this table to be a blackhole table, it will behave as you describe -- inserts "succeed" (don't return an error) but anything you insert into a table like this immediately disappears. The table otherwise interacts with queries as if it were just always empty.

The BLACKHOLE storage engine acts as a “black hole” that accepts data but throws it away and does not store it. Retrievals always return an empty result.

INSERT INTO a blackhole table appears to succeed, but whatever you inserted is immediately and automatically discarded.

SELECT from a blackhole table always returns 0 rows.

DELETE from a blackhole table always deletes 0 rows.

UPDATE on a blackhole table always has 0 rows affected.

Your fix will be to eliminate the trigger you've been working on, and do this:

ALTER TABLE player_viplist ENGINE=BLACKHOLE;

Warning: this advice is based on my interpretation of your needs, and issuing that query will discard any current data in the `player_viplist` table. Do this only if that's what you want.

share|improve this answer
    
Perfect, Thanks –  gustavo Sep 24 '13 at 18:04

If you are having a trigger on a table, You can not operate the same table which is used in the trigger.

As you mentioned you want to delete records from the table after inserting the records. Instead, why can't you skip inserting records to the table.

Can you mention your exact requirement in brief.

share|improve this answer
    
Thanks for your anwsers. I have a system, which after using the INSERT / UPDATE this table because a bug on my system. I need to make this table is empty all the time, or that all INSERT / UPDATE to be used it is canceled. –  gustavo Sep 19 '13 at 12:11
    
If you can't do all cleanup of insert/update statements, there is a way which is a little bit costlier. If you are using MySQL 5.1 or above, create an event with Delete/Truncate table and schedule the event in your required Time interval. –  Phanindra Sep 20 '13 at 14:34

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.