[mysql] MySQL 트리거에서 오류 발생

trigger before the update테이블에있는 경우 해당 테이블의 업데이트를 방해하는 오류를 발생시키는 방법은 무엇입니까?

답변

Here is one hack that may work. It isn’t clean, but it looks like it might work:

Essentially, you just try to update a column that doesn’t exist.

답변

MySQL 5.5부터는 SIGNAL구문을 사용하여 예외를 던질 수 있습니다 .

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

상태 45000은 “처리되지 않은 사용자 정의 예외”를 나타내는 일반 상태입니다.

다음은 접근 방식에 대한보다 완전한 예입니다.

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;

답변

불행히도 @RuiDC가 제공하는 답변 은 저장 프로 시저에 대한 SIGNAL 구현이 없기 때문에 5.5 이전의 MySQL 버전에서는 작동하지 않습니다 .

내가 찾은 해결책 은 table_name doesn't exist오류를 발생 시키는 신호를 시뮬레이션 하여 사용자 정의 된 오류 메시지를 (으)로 푸시하는 것 table_name입니다.

해킹은 트리거를 사용하거나 저장 프로 시저를 사용하여 구현할 수 있습니다. @RuiDC에서 사용한 예제에 따라 아래 두 옵션을 모두 설명합니다.

트리거 사용

DELIMITER $$
-- before inserting new id
DROP TRIGGER IF EXISTS before_insert_id$$
CREATE TRIGGER before_insert_id
    BEFORE INSERT 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 ;

저장 프로 시저 사용

저장 프로 시저를 사용하면 동적 SQL을 사용할 수있어 한 번의 절차로 오류 생성 기능을 캡슐화 할 수 있습니다. 반론은 애플리케이션 삽입 / 업데이트 메소드를 제어해야하므로 스토어드 프로 시저 만 사용한다는 점이다 (INSERT / UPDATE에 직접 권한을 부여하지 않음).

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 INTO test (id) VALUES (p_id);
    END IF;
END$$
DELIMITER ;

답변

다음 절차는 (mysql5에서) 사용자 정의 오류를 발생시키고 동시에 기록하는 방법입니다.

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 INTO mysql_error_generator(error_field) VALUES (errorWithDate);
    INSERT INTO mysql_error_generator(error_field) VALUES (errorWithDate);
END;
$$
DELIMITER ;


call throwCustomError("Custom error message with log support.");

답변

CREATE TRIGGER sample_trigger_msg
    BEFORE INSERT
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;

답변

사용할 수있는 또 다른 (해킹) 방법 (어떤 이유로 5.5 이상이 아닌 경우) :

필수 필드가있는 경우 트리거 내에서 필수 필드를 NULL과 같은 유효하지 않은 값으로 설정하십시오. 이것은 INSERT와 UPDATE 모두에서 작동합니다. NULL이 필수 필드에 유효한 값이면 (어떤 미친 이유로)이 방법은 작동하지 않습니다.

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

5.5 이상인 경우 다른 답변에 설명 된대로 신호 상태를 사용할 수 있습니다.

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

답변

DELIMITER @@
DROP TRIGGER IF EXISTS trigger_name @@
CREATE TRIGGER trigger_name
BEFORE UPDATE ON table_name
FOR EACH ROW
BEGIN

  --the condition of error is: 
  --if NEW update value of the attribute age = 1 and OLD value was 0
  --key word OLD and NEW let you distinguish between the old and new value of an attribute

   IF (NEW.state = 1 AND OLD.state = 0) THEN
       signal sqlstate '-20000' set message_text = 'hey it's an error!';
   END IF;

END @@
DELIMITER ;