MySQL Tutorial
Introduction Installation of MySQL Getting MySQL server information RDBMS Concepts Data Definition Language Data types Managing Databases Managing Tables Managing Views Inserting records in a table Updating records in a table Deleting records from a table Retrieving Records Data Control Language Variables in MySQL Procedures in MySQL Built-In functions in MySQL User Defined functions in MySQL Triggers in MySQL Cursors in MySQL Transaction control Event scheduler Tuning MySQL Server Performance Managing users Importing and Exporting data Database Testing Workbench PHPMyAdminTriggers 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!!