How to display result and or any message in trigger body?

Question

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 ?

Here testdata is table name.

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 ;

Solution 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.

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.

Solution 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):

1. You need to create a Table that will handle all logging of the triggers.
2. 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.
3. Then inside the if condition u are using write and insert statemement to fill the info in the new (logging) table.
4. 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 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 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

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

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.

Comments

[Java Curious ღ]

right now I have also try about to set user defined variable in trigger body ad then read it from outside.

[Java Curious ღ]

Sir suppose I ll set all message to any new table and then how to read and display that message ? suppose admin see that message and add new quantity then although the message will be display, then how to mange all that thing, I am confused about it.. any idea sir about it ??

[Devart]

Yes, using UDF is more difficult. If it is possible - try to use simple table, I have added a short example.

[Devart]

@user2659972 ღ - Welcome)

[Java Curious ღ]

Yes It helps me a lot. Thank you so much.. what SIGNAL SQLSTATE '01000' SET MESSAGE_TEXT = 'Lower than 50', MYSQL_ERRNO = 1000; will do ? Means what's it's behaviour when condition satisfy ?

[ckinfos]

it will throw an error in the console, or if u catch it in your application! That means if u check for error somewhere...u will see it there! u may break your program like that. In java for example u may create an exception and try catch it. Then u can do anything in there. The same in a stored procedure etc...

[Java Curious ღ]

Ok I got it and thanx a lot