Jika saya memiliki satu pemicu
sebelum update
di atas meja, bagaimana saya bisa melempar kesalahan yang mencegah pembaruan pada meja itu?
Seperti MySQL 5.5, anda dapat menggunakan SINYAL
sintaks untuk melemparkan pengecualian:
signal sqlstate '45000' set message_text = 'My Error Message';
Negara 45000 generik negara yang mewakili "tidak tertangani user-defined exception".
Di sini adalah yang lebih lengkap contoh dari pendekatan ini:
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;
Di sini adalah salah satu hack yang dapat bekerja. Ini isn't bersih, tapi sepertinya hal itu mungkin bekerja:
Pada dasarnya, anda hanya mencoba untuk meng-update kolom yang doesn't ada.
Sayangnya, jawaban yang diberikan oleh @RuiDC tidak bekerja di versi MySQL sebelum 5.5 karena tidak ada pelaksanaan SINYAL untuk disimpan prosedur.
Solusi saya've ditemukan adalah untuk mensimulasikan sinyal melemparkan table_name doesn't ada
kesalahan, mendorong disesuaikan pesan kesalahan ke table_name
.
Hack dapat diimplementasikan dengan menggunakan pemicu atau menggunakan prosedur yang tersimpan. Saya menjelaskan kedua pilihan di bawah ini berikut contoh yang digunakan oleh @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 ;
Disimpan prosedur yang memungkinkan anda untuk menggunakan sql dinamis, yang memungkinkan enkapsulasi dari kesalahan generasi fungsi dalam satu prosedur. Tandingan adalah bahwa kita harus mengendalikan aplikasi insert/update metode, sehingga mereka menggunakan hanya disimpan prosedur (bukan pemberian langsung hak untuk 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 ;
Prosedur berikut (pada mysql5) cara untuk melemparkan kesalahan kustom , dan log mereka pada waktu yang sama:
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.");
Lain (hack) metode (jika anda tidak berada di 5.5+ untuk beberapa alasan) yang dapat anda gunakan:
Jika anda memiliki bidang yang diperlukan, maka hanya memicu mengatur bidang yang diperlukan untuk nilai yang tidak valid tersebut sebagai NULL. Ini akan bekerja baik untuk INSERT dan UPDATE. Perhatikan bahwa jika NULL adalah nilai yang valid untuk bidang yang diperlukan (untuk beberapa alasan gila) maka pendekatan ini tidak akan bekerja.
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
Jika anda berada di 5.5+ maka anda dapat menggunakan sinyal negara seperti yang dijelaskan dalam jawaban yang lain:
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