MySQL 트리거에서 테이블 업데이트를 막는 오류 발생

MySQL 트리거에서 테이블 업데이트를 막는 오류 발생

테이블 업데이트 전에 트리거가 있는 경우 해당 테이블에서 업데이트를 차단하는 오류를 발생시키려면 어떻게 해야 합니까?

MySQL 5.5부터는 구문을 사용하여 예외를 슬로우할 수 있습니다.

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에서 제공하는 답변은 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을 사용할 수 있습니다.이것에 의해, 에러 생성 기능을 1개의 프로시저로 캡슐화할 수 있습니다.반대되는 점은 응용 프로그램 삽입/업데이트 방법을 제어해야 한다는 것입니다. 그러면 응용 프로그램은 저장 프로시저만 사용합니다(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 ;

언급URL : https://stackoverflow.com/questions/24/throw-an-error-preventing-a-table-update-in-a-mysql-trigger