MySQL server 5.1 trigger problem

MySQLmysql-5.1trigger

I have implemented a trigger. But once I try to update the table it is giving me the error:

can't update table 'booking' in stored function/trigger because it is already used by the statement which invoked this stored function/trigger

This is the trigger

DELIMITER //
CREATE TRIGGER CANCEL_BOOK AFTER UPDATE on BOOKING
FOR EACH ROW
BEGIN
DELETE FROM BOOKING
    WHERE NEW.F_ID=NULL AND NEW.H_ID=NULL;
END //
DELIMITER ;

please help

Best Answer

Prevent record creation with F_ID and H_ID having NULL values (needs 5.5 or newer server version):

DELIMITER @@;

CREATE TRIGGER cancel_insert_book BEFORE INSERT ON booking
FOR EACH ROW
BEGIN
    IF ((NEW.F_ID IS NULL) AND (NEW.H_ID IS NULL)) THEN
        SIGNAL SQLSTATE '23000'
        SET MESSAGE_TEXT = 'Both F_ID and H_ID cannot be NULL at the same time', 
        MYSQL_ERRNO = 1048;
    END IF;
END; 
@@;

CREATE TRIGGER cancel_update_book BEFORE UPDATE ON booking
FOR EACH ROW
BEGIN
    IF ((NEW.F_ID IS NULL) AND (NEW.H_ID IS NULL)) THEN
        SIGNAL SQLSTATE '23000'
        SET MESSAGE_TEXT = 'Both F_ID and H_ID cannot be NULL at the same time', 
        MYSQL_ERRNO = 1048;
    END IF;
END; 
@@;

DELIMITER ;

Remove record which have F_ID and H_ID having NULL values:

DELIMITER @@;

CREATE TRIGGER cancel_book AFTER UPDATE ON booking
FOR EACH ROW
BEGIN
    IF ((NEW.F_ID IS NULL) AND (NEW.H_ID IS NULL)) THEN
        CREATE EVENT remove_record
        ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND
        DO
          DELETE FROM booking
          WHERE ((f_id IS NULL) AND (h_id IS NULL));
    END IF;
END; 
@@;

DELIMITER ;

Related Solutions

Mysql – Trigger to increment a column value after update in a column of same table

You can also change values using new. You just have to use not after but before trigger.

delimiter $$
create trigger increment
before update on T1
begin
    if new.C1=0 then
        set new.c2 = new.c2 +1;
    end if;
end $$
delimiter ;

Mysql – What am I doing wrong in thesql database

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.

Best Answer

Related Solutions

Mysql – Trigger to increment a column value after update in a column of same table

Mysql – What am I doing wrong in thesql database

Related Question