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.