Wirft einen Fehler in einem MySQL Trigger

stimmen
136

Wenn ich habe trigger before the updateauf einem Tisch, wie kann ich einen Fehler aus , dass das Update auf die Tabelle verhindert?

Veröffentlicht am 01/08/2008 um 13:12
quelle vom benutzer
In anderen Sprachen...                            


6 antworten

stimmen
113

Ab MySQL 5.5 können Sie die verwenden SIGNALSyntax , eine Ausnahme zu werfen :

signal sqlstate '45000' set message_text = 'My Error Message';

Zustand 45000 ist ein allgemeiner Zustand „nicht behandelte benutzerdefinierte Ausnahme“ darstellen.


Hier ist ein vollständigeres Beispiel des Ansatzes:

delimiter //
use test//
create table trigger_test
(
    id int not null
)//
drop trigger if exists trg_trigger_test_ins //
create trigger trg_trigger_test_ins before insert on trigger_test
for each row
begin
    declare msg varchar(128);
    if new.id < 0 then
        set msg = concat('MyTriggerError: Trying to insert a negative value in trigger_test: ', cast(new.id as char));
        signal sqlstate '45000' set message_text = msg;
    end if;
end
//

delimiter ;
-- run the following as seperate statements:
insert into trigger_test values (1), (-1), (2); -- everything fails as one row is bad
select * from trigger_test;
insert into trigger_test values (1); -- succeeds as expected
insert into trigger_test values (-1); -- fails as expected
select * from trigger_test;
Beantwortet am 25/08/2011 um 12:14
quelle vom benutzer

stimmen
52

Hier ist ein Hack , die funktionieren kann. Es ist nicht sauber, aber es sieht aus wie es funktionieren könnte:

Im Wesentlichen versuchen Sie einfach eine Spalte zu aktualisieren, die nicht existiert.

Beantwortet am 01/08/2008 um 14:02
quelle vom benutzer

stimmen
28

Leider ist die Antwort , die von @RuiDC nicht vor 5.5 in MySQL - Versionen arbeiten , weil es keine Implementierung ist SIGNAL für gespeicherte Prozeduren.

Die Lösung , die ich habe gefunden ist , ein Signal zu werfen einen simulieren table_name doesn't existFehler, eine angepasste Fehlermeldung in dem Schiebt table_name.

Der Hack könnte mit Trigger implementiert oder eine gespeicherte Prozedur. Ich beschreibe beiden Optionen unten nach dem Beispiel verwendet von @RuiDC.

Mit Trigger

DELIMITER $$
-- before inserting new id
DROP TRIGGER IF EXISTS before_insert_id$$
CREATE TRIGGER before_insert_id
    BEFORE INSERT IGNORE  ON test FOR EACH ROW
    BEGIN
        -- condition to check
        IF NEW.id < 0 THEN
            -- hack to solve absence of SIGNAL/prepared statements in triggers
            UPDATE `Error: invalid_id_test` SET x=1;
        END IF;
    END$$

DELIMITER ;

eine gespeicherte Prozedur

Stored Procedures können Sie dynamische SQL verwenden, die in einem Arbeitsgang möglich, die Kapselung der Fehlererzeugung Funktionalität macht. Der Kontrapunkt ist, dass wir die Anwendungen Aktualisierungsmethoden einsetzen / steuern sollten, so dass sie verwenden nur unsere Stored Procedure (nicht direkte Privilegien Gewährung / UPDATE INSERT IGNORE).

DELIMITER $$
-- my_signal procedure
CREATE PROCEDURE `my_signal`(in_errortext VARCHAR(255))
BEGIN
    SET @sql=CONCAT('UPDATE `', in_errortext, '` SET x=1');
    PREPARE my_signal_stmt FROM @sql;
    EXECUTE my_signal_stmt;
    DEALLOCATE PREPARE my_signal_stmt;
END$$

CREATE PROCEDURE insert_test(p_id INT)
BEGIN
    IF NEW.id < 0 THEN
         CALL my_signal('Error: invalid_id_test; Id must be a positive integer');
    ELSE
        INSERT IGNORE  INTO test (id) VALUES (p_id);
    END IF;
END$$
DELIMITER ;
Beantwortet am 28/01/2012 um 16:46
quelle vom benutzer

stimmen
10

Das folgende Verfahren ist (auf mysql5) eine Art und Weise individuelle Fehler zu werfen, und melden sie zugleich:

create table mysql_error_generator(error_field varchar(64) unique) engine INNODB;
DELIMITER $$
CREATE PROCEDURE throwCustomError(IN errorText VARCHAR(44))
BEGIN
    DECLARE errorWithDate varchar(64);
    select concat("[",DATE_FORMAT(now(),"%Y%m%d %T"),"] ", errorText) into errorWithDate;
    INSERT IGNORE  IGNORE INTO mysql_error_generator(error_field) VALUES (errorWithDate);
    INSERT IGNORE  INTO mysql_error_generator(error_field) VALUES (errorWithDate);
END;
$$
DELIMITER ;


call throwCustomError("Custom error message with log support.");
Beantwortet am 08/11/2012 um 17:15
quelle vom benutzer

stimmen
6
CREATE TRIGGER sample_trigger_msg 
    BEFORE INSERT IGNORE 
FOR EACH ROW
    BEGIN
IF(NEW.important_value) < (1*2) THEN
    DECLARE dummy INT;
    SELECT 
           Enter your Message Here!!!
 INTO dummy 
        FROM mytable
      WHERE mytable.id=new.id
END IF;
END;
Beantwortet am 12/08/2016 um 18:08
quelle vom benutzer

stimmen
4

Weitere (Hack) -Methode (wenn Sie nicht auf 5.5+ aus irgendeinem Grund), dass Sie verwenden können:

Wenn Sie ein erforderliches Feld, dann innerhalb eines Triggers stellen Sie das gewünschte Feld auf einen ungültigen Wert wie NULL. Dies wird sowohl für INSERT IGNORE und UPDATE arbeiten. Sie beachten, dass, wenn NULL ein gültiger Wert für das gewünschte Feld (aus irgendeinem verrückten Grund) ist, dann wird dieser Ansatz nicht funktionieren.

BEGIN
    -- Force one of the following to be assigned otherwise set required field to null which will throw an error
    IF (NEW.`nullable_field_1` IS NULL AND NEW.`nullable_field_2` IS NULL) THEN
        SET NEW.`required_id_field`=NULL;
    END IF;
END

Wenn Sie auf 5.5+ sind, dann können Sie den Signalzustand verwenden, wie in anderen Antworten beschrieben:

BEGIN
    -- Force one of the following to be assigned otherwise use signal sqlstate to throw a unique error
    IF (NEW.`nullable_field_1` IS NULL AND NEW.`nullable_field_2` IS NULL) THEN
        SIGNAL SQLSTATE '45000' set message_text='A unique identifier for nullable_field_1 OR nullable_field_2 is required!';
    END IF;
END
Beantwortet am 09/04/2016 um 23:11
quelle vom benutzer

Cookies help us deliver our services. By using our services, you agree to our use of cookies. Learn more