如何在触发器体中显示结果和/或任何消息？

Hi I want to create trigger if it's condition satisfy then it's body should be executed and I want to display some message or any data that should be displayed if trigger body executed.

嗨我想创建触发器，如果条件满足，那么它的主体应该执行，我想显示一些消息或任何触发器主体执行时应显示的数据。

I want that if quantity of product went less then 50 then it should display message or some data. Is it possible to display message ?

我希望如果产品数量少于50，那么它应该显示消息或一些数据。是否可以显示消息？

Here testdata is table name.

这里testdata是表名。

Code :

代码：

delimiter //

create trigger trigger2 before update on test.testdata

for each row 
begin

if new.qty < 50 then

    **display here some message that quantity is less**

end if;

end;

//
delimiter ;

2 个解决方案

#1

You cannot do it, there is no place to output them in MySQL. As a work around you can add your message to the table, and then read this table.

你不能这样做，没有地方可以在MySQL中输出它们。作为解决方法，您可以将消息添加到表中，然后阅读此表。

Short example -

简短的例子 -

CREATE TABLE table1 (
  column1 VARCHAR(255) DEFAULT NULL
);

CREATE TABLE messages (
  id INT(11) NOT NULL AUTO_INCREMENT,
  message VARCHAR(255) DEFAULT NULL,
  time TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

DELIMITER $$

CREATE TRIGGER trigger1
    AFTER INSERT
    ON table1
    FOR EACH ROW
BEGIN
  INSERT INTO messages(message) VALUES ('new action');
END
$$

DELIMITER ;

Also, you could use UDF function write your logic. More information - CREATE FUNCTION Syntax for User-Defined Functions.

此外，您可以使用UDF函数编写您的逻辑。更多信息 - 用户定义函数的CREATE FUNCTION语法。

#2

For Quick and plain answering: You cannot Display Messages From Triggers. You may Only Throw errors.

对于快速和简单应答：您无法显示触发器中的消息。你可能只会抛出错误。

You are propably not knowing the reason u use triggers in databases assuming from your question. We all have passed that level so dont worry. U have understood the syntax when we use triggers but not how and what they can do and not.

根据您的问题，您可能不知道您在数据库中使用触发器的原因。我们都已经通过了那个级别，所以不要担心。你已经理解了我们使用触发器的语法，但没有理解他们可以做什么和不做什么。

A trigger will do (for your case BEFORE an UPDATE) something concerning the database and only that. That means the trigger cannot display any message on your screen. You can only handle database staff and not all of the actions are allowed for that too or some actions arent even recommended!. That is for the theory part.

一个触发器（对于你的情况，在更新之前）会做一些与数据库有关的事情。这意味着触发器无法在屏幕上显示任何消息。您只能处理数据库人员，并且不允许执行所有操作，甚至不推荐某些操作！这是理论部分。

To give you a solution to your problem now. The only thing you can do to know when the trigger has worked (that means when the new.qua<50) or basically check anything with any other trigger is the following. (Just a small fast solution):

现在为您解决问题。你可以做的唯一事情就是知道触发器何时起作用（这意味着当new.qua <50时）或基本上用任何其他触发器检查任何东西是如下。（只是一个小的快速解决方案）：

You need to create a Table that will handle all logging of the triggers.
您需要创建一个表来处理触发器的所有日志记录。
Add in it an ID field, a descr field that will hold the action of the triggerex. BefUpdate, BefInsert etc. another field for the propably the condition that triggered the logging and antyhing else u want displayed later in the application.
添加一个ID字段，一个descr字段，用于保存triggerex的动作。 BefUpdate，BefInsert等另一个字段，可能是触发日志记录和antyhing的条件，否则您希望稍后在应用程序中显示。
Then inside the if condition u are using write and insert statemement to fill the info in the new (logging) table.
然后在if条件中使用write和insert statemement来填充新（日志）表中的信息。
in your app later select that logging table to see the messages.
在您的应用程序中，稍后选择该日志记录表以查看消息。

That is a useful and fast way to log, not only triggers but also functions (stored procs). Judt for reference i give you s sample code with the CREATE, and the INSERT statement for your trigger.

这是一种有用且快速的记录方法，不仅包括触发器，还包括函数（存储过程）。请参考我给出的带有CREATE的示例代码，以及触发器的INSERT语句。

CREATE TABLE LOGGER (
    ID  BIGINT PRIMARY KEY AUTO_INCREMENT,
    DESCR_ACTIVITY VARCHAR(10),
    ACTIVITY VARCHAR(50),
    DT TIMESTAMP,
    CONDITIONVALUE VARCHAR(50)
)

In the IF of your code now make it as :

在您的代码的IF中，现在将其设为：

if new.qty < 50 then
    INSERT INTO LOGGER VALUES ('BEFINS','CHECKING QUA',NULL,'QUANTITY IS LOWER THAN 50')
end if;

And even from the workbench or from your application u can just :

甚至从工作台或您的应用程序，你可以只：

SELECT * FROM LOGGER

to see the loggings.

看日志。

But if i am confused from the reading and you want just to throw an error u can read the Mysql Documentation concerning throwing errors: enter link description here

但是，如果我对阅读感到困惑，你只想抛出错误，你可以阅读关于抛出错误的Mysql文档：在这里输入链接描述

What u can do is in your if condition write something like:

你可以做的是在你的if条件中写下这样的东西：

if new.qty < 50 then
    SIGNAL SQLSTATE '01000' SET MESSAGE_TEXT = 'Lower than 50', MYSQL_ERRNO = 1000;
endif;

What u should always NOT DO is alter the same table that a trigger is assigned and use only small portion of not so complex code in the trigger.

你永远不应该做的是改变分配触发器的同一个表，并在触发器中仅使用一小部分不那么复杂的代码。

Hope i helped a bit.

希望我帮了一下。

#1