导致 INSERT 失败的触发器？可能的？

发布于 2024-07-07 14:18:17 字数 446 浏览 6 评论 0原文

在清理这个答案我了解了一些关于 MySQL 中的 TRIGGER 和存储过程的知识，但令我惊讶的是，BEFORE INSERT 和 BEFORE UPDATE 触发器可以修改数据，它们似乎无法导致插入/更新失败（即验证）。在这种特殊情况下，我能够通过以导致主键重复的方式操作数据来使其工作，这在这种特殊情况下是有意义的，但在一般意义上不一定有意义。

MySQL 中可以实现这种功能吗？在任何其他 RDBMS 中（遗憾的是我的经验仅限于 MySQL）？也许是 THROW EXCEPTION 风格的语法？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

戏蝶舞 2024-07-14 14:18:17

从这个博客文章

MySQL 触发器：如何中止 INSERT、UPDATE 或 DELETE
扳机？在 EfNet 的#mysql 上有人
问：
如果我的业务规则失败，如何让触发器中止操作？
在 MySQL 5.0 和 5.1 中，您需要
采取一些诡计来制造
触发失败并提供有意义的
错误信息。 MySQL 存储
程序常见问题解答说明了有关错误的信息
处理：
SP 11. SP 是否有“提出”声明来“提出应用程序错误”？抱歉，目前没有。 SQL 标准 SIGNAL 和 RESIGNAL 语句位于 TODO 上。
也许 MySQL 5.2 将包含 SIGNAL
声明这将使这个黑客
直接从 MySQL 存储中窃取
程序编程已过时。什么
是黑客吗？你要强行
MySQL 尝试使用一个列
不存在。丑陋的？是的。可以
工作？当然。
创建触发器 mytabletriggerexample 
  插入前 
  对于每一行开始 
  IF(NEW.important_value) <   （幻想 * 舞蹈 * 计算）然后 
      声明虚拟 INT； 

      选择您有意义的错误消息进入此处 INTO dummy  
          来自我的表 
        WHERE mytable.id=new.id 
  万一;   结尾; 
  

From this blog post

MySQL Triggers: How do you abort an INSERT, UPDATE or DELETE with a
trigger? On EfNet’s #mysql someone
asked:
How do I make a trigger abort the operation if my business rule fails?
In MySQL 5.0 and 5.1 you need to
resort to some trickery to make a
trigger fail and deliver a meaningful
error message. The MySQL Stored
Procedure FAQ says this about error
handling:
SP 11. Do SPs have a “raise” statement to “raise application errors”? Sorry, not at present. The SQL standard SIGNAL and RESIGNAL statements are on the TODO.
Perhaps MySQL 5.2 will include SIGNAL
statement which will make this hack
stolen straight from MySQL Stored
Procedure Programming obsolete. What
is the hack? You’re going to force
MySQL to attempt to use a column that
does not exist. Ugly? Yes. Does it
work? Sure.
CREATE TRIGGER mytabletriggerexample
BEFORE INSERT
FOR EACH ROW BEGIN
IF(NEW.important_value) < (fancy * dancy * calculation) THEN
    DECLARE dummy INT;

    SELECT Your meaningful error message goes here INTO dummy 
        FROM mytable
      WHERE mytable.id=new.id
END IF; END;

回复收藏 0 原文

分分钟 2024-07-14 14:18:17

这是我的做法。请注意SET NEW='some error';。 MySQL 会告诉你“变量‘new’不能设置为‘错误：无法删除此项目。销售表中有包含此项目的记录。’”

你可以在代码中捕获此内容，然后显示结果错误:)

DELIMITER $
DROP TRIGGER IF EXISTS before_tblinventoryexceptionreasons_delete $
CREATE TRIGGER before_tblinventoryexceptionreasons_delete
BEFORE DELETE ON tblinventoryexceptionreasons
FOR EACH ROW BEGIN
  IF (SELECT COUNT(*) FROM tblinventoryexceptions WHERE tblinventoryexceptions.idtblinventoryexceptionreasons = old.idtblinventoryexceptionreasons) > 0
  THEN
    SET NEW='Error: Cannot delete this item. There are records in the inventory exception reasons table with this item.';
  END IF;
END$
DELIMITER ;

DELIMITER $
DROP TRIGGER IF EXISTS before_storesalesconfig_delete $
CREATE TRIGGER before_storesalesconfig_delete
BEFORE DELETE ON tblstoresalesconfig
FOR EACH ROW BEGIN
  IF (SELECT COUNT(*) FROM tblstoresales WHERE tblstoresales.idtblstoresalesconfig=old.idtblstoresalesconfig) > 0
  THEN
    SET NEW='Error: Cannot delete this item. There are records in the sales table with this item.';
  END IF;
  IF (SELECT COUNT(*) FROM tblinventory WHERE tblinventory.idtblstoresalesconfig=old.idtblstoresalesconfig) > 0
  THEN
    SET NEW='Error: Cannot delete this item. There are records in the inventory table with this item.';
  END IF;
  IF (SELECT COUNT(*) FROM tblinventoryexceptions WHERE tblinventoryexceptions.idtblstoresalesconfig=old.idtblstoresalesconfig) > 0
  THEN
    SET NEW='Error: Cannot delete this item. There are records in the inventory exceptions table with this item.';
  END IF;
  IF (SELECT COUNT(*) FROM tblinvoicedetails WHERE tblinvoicedetails.idtblstoresalesconfig=old.idtblstoresalesconfig) > 0
  THEN
    SET NEW='Error: Cannot delete this item. There are records in the inventory details table with this item.';
  END IF;
END$
DELIMITER ;

DELIMITER $
DROP TRIGGER IF EXISTS before_tblinvoice_delete $
CREATE TRIGGER before_tblinvoice_delete
BEFORE DELETE ON tblinvoice
FOR EACH ROW BEGIN
  IF (SELECT COUNT(*) FROM tblinvoicedetails WHERE tblinvoicedetails.idtblinvoice = old.idtblinvoice) > 0
  THEN
    SET NEW='Error: Cannot delete this item. There are records in the inventory details table with this item.';
  END IF;
END$
DELIMITER ;

Here is the way I did it. Note the SET NEW='some error';. MySQL will tell you "Variable 'new' can't be set to the value of 'Error: Cannot delete this item. There are records in the sales table with this item.'"

You can trap this in your code and then show the resulting error :)

DELIMITER $
DROP TRIGGER IF EXISTS before_tblinventoryexceptionreasons_delete $
CREATE TRIGGER before_tblinventoryexceptionreasons_delete
BEFORE DELETE ON tblinventoryexceptionreasons
FOR EACH ROW BEGIN
  IF (SELECT COUNT(*) FROM tblinventoryexceptions WHERE tblinventoryexceptions.idtblinventoryexceptionreasons = old.idtblinventoryexceptionreasons) > 0
  THEN
    SET NEW='Error: Cannot delete this item. There are records in the inventory exception reasons table with this item.';
  END IF;
END$
DELIMITER ;

DELIMITER $
DROP TRIGGER IF EXISTS before_storesalesconfig_delete $
CREATE TRIGGER before_storesalesconfig_delete
BEFORE DELETE ON tblstoresalesconfig
FOR EACH ROW BEGIN
  IF (SELECT COUNT(*) FROM tblstoresales WHERE tblstoresales.idtblstoresalesconfig=old.idtblstoresalesconfig) > 0
  THEN
    SET NEW='Error: Cannot delete this item. There are records in the sales table with this item.';
  END IF;
  IF (SELECT COUNT(*) FROM tblinventory WHERE tblinventory.idtblstoresalesconfig=old.idtblstoresalesconfig) > 0
  THEN
    SET NEW='Error: Cannot delete this item. There are records in the inventory table with this item.';
  END IF;
  IF (SELECT COUNT(*) FROM tblinventoryexceptions WHERE tblinventoryexceptions.idtblstoresalesconfig=old.idtblstoresalesconfig) > 0
  THEN
    SET NEW='Error: Cannot delete this item. There are records in the inventory exceptions table with this item.';
  END IF;
  IF (SELECT COUNT(*) FROM tblinvoicedetails WHERE tblinvoicedetails.idtblstoresalesconfig=old.idtblstoresalesconfig) > 0
  THEN
    SET NEW='Error: Cannot delete this item. There are records in the inventory details table with this item.';
  END IF;
END$
DELIMITER ;

DELIMITER $
DROP TRIGGER IF EXISTS before_tblinvoice_delete $
CREATE TRIGGER before_tblinvoice_delete
BEFORE DELETE ON tblinvoice
FOR EACH ROW BEGIN
  IF (SELECT COUNT(*) FROM tblinvoicedetails WHERE tblinvoicedetails.idtblinvoice = old.idtblinvoice) > 0
  THEN
    SET NEW='Error: Cannot delete this item. There are records in the inventory details table with this item.';
  END IF;
END$
DELIMITER ;

回复收藏 0 原文

燕归巢 2024-07-14 14:18:17

因为当我在 MySQL 触发器中搜索错误处理时，这篇文章出现在顶部，所以我想我应该分享一些知识。

如果出现错误，您可以强制 MySQL 使用 SIGNAL，但如果您不将其指定为 SQLEXCEPTION 类，那么什么都不会发生，因为并非所有 SQLSTATE 都被认为是坏的，即使如此，您也必须确保 RESIGNAL 如果您有任何嵌套的 BEGIN/END 块。

或者，可能更简单，在触发器中声明一个退出处理程序并重新发出异常信号。

CREATE TRIGGER `my_table_AINS` AFTER INSERT ON `my_table` FOR EACH ROW
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
        RESIGNAL;
    DECLARE EXIT HANDLER FOR SQLWARNING
        RESIGNAL;
    DECLARE EXIT HANDLER FOR NOT FOUND
        RESIGNAL; 
    -- Do the work of the trigger.
END

如果你的身体发生错误，它会被抛回顶部并错误退出。这也可以用于存储过程等。

这适用于任何 5.5+ 版本。

Because this article comes up towards the top when I search for error handling in MySQL triggers, I thought I'd share some knowledge.

If there is an error, you can force MySQL to use a SIGNAL, but if you don't specify it as a class as SQLEXCEPTION, then nothing will happen, as not all SQLSTATEs are considered bad, and even then you'd have to make sure to RESIGNAL if you have any nested BEGIN/END blocks.

Alternatively, and probably simpler still, within your trigger, declare an exit handler and resignal the exception.

CREATE TRIGGER `my_table_AINS` AFTER INSERT ON `my_table` FOR EACH ROW
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
        RESIGNAL;
    DECLARE EXIT HANDLER FOR SQLWARNING
        RESIGNAL;
    DECLARE EXIT HANDLER FOR NOT FOUND
        RESIGNAL; 
    -- Do the work of the trigger.
END

And if in your body there occurs an error, it will be thrown back up to the top and exit with an error. This can also be used in stored procedures and whatnot.

This works with anything version 5.5+.

回复收藏 0 原文

奢欲 2024-07-14 14:18:17

这将通过引发异常来中止您的 INSERT（来自 http://www.experts -exchange.com/Database/MySQL/Q_23788965.html）

DROP PROCEDURE IF EXISTS `MyRaiseError`$

CREATE PROCEDURE `MyRaiseError`(msg VARCHAR(62))
BEGIN
DECLARE Tmsg VARCHAR(80);
SET Tmsg = msg;
IF (CHAR_LENGTH(TRIM(Tmsg)) = 0 OR Tmsg IS NULL) THEN
SET Tmsg = 'ERROR GENERADO';
END IF;
SET Tmsg = CONCAT('@@MyError', Tmsg, '@@MyError');
SET @MyError = CONCAT('INSERT INTO', Tmsg);
PREPARE stmt FROM @MyError;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$

用法：

call MyRaiseError('Here error message!');

This will abort your INSERT by raising an exception (from http://www.experts-exchange.com/Database/MySQL/Q_23788965.html)

DROP PROCEDURE IF EXISTS `MyRaiseError`$

CREATE PROCEDURE `MyRaiseError`(msg VARCHAR(62))
BEGIN
DECLARE Tmsg VARCHAR(80);
SET Tmsg = msg;
IF (CHAR_LENGTH(TRIM(Tmsg)) = 0 OR Tmsg IS NULL) THEN
SET Tmsg = 'ERROR GENERADO';
END IF;
SET Tmsg = CONCAT('@@MyError', Tmsg, '@@MyError');
SET @MyError = CONCAT('INSERT INTO', Tmsg);
PREPARE stmt FROM @MyError;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$

Usage:

call MyRaiseError('Here error message!');

回复收藏 0 原文

浊酒尽余欢 2024-07-14 14:18:17

不适用于触发器
（存储函数或触发器中不允许使用动态 SQL）

我使用了一个非常肮脏的解决方案：

If NEW.test=1 then 调用TEST_CANNOT_BE_SET_TO_1；万一;

当 test=1 时，Mysql 抛出以下异常：

PROCEDURE administratie.TEST_CANNOT_BE_SET_TO_1 不存在

不复杂，但快速且有用。

回复收藏 0 原文

北渚 2024-07-14 14:18:17

在 MS SQL 中，您可以使用正确的语法使其工作：

IF UPDATE(column_name)
BEGIN
  RAISEERROR
  ROLLBACK TRAN
  RETURN
END

http://msdn. microsoft.com/en-us/magazine/cc164047.aspx

in MS SQL you could make it work using proper syntax:

IF UPDATE(column_name)
BEGIN
  RAISEERROR
  ROLLBACK TRAN
  RETURN
END

http://msdn.microsoft.com/en-us/magazine/cc164047.aspx

回复收藏 0 原文

~没有更多了~

关于作者

云裳

暂无简介

0 文章

0 评论

22 人气

关注发私信

友情链接

文江博客

导致 INSERT 失败的触发器？可能的？

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（6）

关于作者

相关话题

热门标签

推荐作者

qq_FjTq5B

18273202778

WordPress小学生

〃温暖了心ぐ

迷乱花海

niuniu

友情链接

导致 INSERT 失败的触发器？ 可能的？

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（6）

关于作者

相关话题

热门标签

推荐作者

qq_FjTq5B

18273202778

WordPress小学生

〃温暖了心ぐ

迷乱花海

niuniu

友情链接

导致 INSERT 失败的触发器？可能的？

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。