Triggers in MySQL

Triggers are like procedures without parameters. Only difference is that you need to call the procedures explicitly. But triggers get invoked automatically before or after insert/delete/update operation. We can call functions and procedures from within trigger code block. MySQL does not support the CHECK constraints. So you should use TRIGGER To view all defined triggers, you can use below syntax.
 
SHOW TRIGGERS;

To view triggers from specific database, you can use below syntax.
 
SHOW TRIGGERS FROM YourDatabaseName

Example on Trigger

 
delimiter //
CREATE TRIGGER CheckMobileNo BEFORE INSERT ON CUSTOMER
FOR EACH ROW
BEGIN
          DECLARE errormsg varchar(200);
            IF LENGTH(NEW.MobileNo)< 9 THEN
            SET errorMessage = CONCAT(‘MyTriggerError: Invalid MobileNo’,

cast(new.MobileNo as char));

signal sqlstate ‘45000’ set message_text = errorMessage;
         END IF;
END//
delimiter “;”

Dropping trigger

To remove tigger, you need to use below syntax.
 
DROP TRIGGER CheckMobileNo

Web development and Automation testing

solutions delivered!!