Mysql – What am I doing wrong in thesql database

insertMySQLtrigger

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 ;

Best Answer

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.

Related Question