Mysql Trigger,Mysql Stored Procedure,Mysql Function

Mysql Trigger :
 A SQL trigger is a set of  SQL statements stored in the database catalog. A SQL trigger is executed or fired whenever an event associated with a table occurs.In MySQL, a trigger is a set of SQL statements that is invoked automatically when a change is made to the data on the associated table.
MySQL stores triggers in a data directory e.g., /data/classicmodels/ with the files named table name.TRG and trigger name.TRN
The tablename.TRG file maps the trigger to the corresponding table.
the triggername.TRN file contains the trigger definition.

There are maximum 6 trigger that is associated with table

1. BEFORE INSERT
2. AFTER INSERT 
3. BEFORE UPDATE
4. AFTER UPDATE 
5. BEFORE DELETE 
6. AFTER DELETE 

Display All Trigger

SHOW TRIGGERS

Trigger Ex :

DELIMITER $$
CREATE TRIGGER before_customer_update 
    BEFORE UPDATE ON customer
    FOR EACH ROW 
BEGIN
    INSERT INTO othertblName
    SET action = 'update',
     cust_number = OLD.cust_number,
        updatedate = NOW(); 
END$$
DELIMITER ;

DROP Trigger

DROP TRIGGER table_name.trigger_name;

Before MySQL version 5.7.2, you can only create one trigger for an event in a table.But after 5.7.2 you can set multiple trigger for an event in table.

Mysql Stored Procedure: 
A stored procedure is a segment of declarative SQL statements stored inside the database catalog.Stored procedures that return multiple values. Stored Procedure Ex:

DELIMITER $$
CREATE PROCEDURE get_cust_order(
 IN cust_id INT,
 OUT countData INT)
BEGIN
 SELECT
            count(*) INTO countData
        FROM
            orders
        WHERE
            customerNumber = cust_id;
 
END

Call Stored Procedure :

CALL get_order_by_cust(141,@countData);
SELECT @countData;

Loop in Stored Procedure :

DELIMITER $$
 CREATE PROCEDURE test_loop()
 BEGIN
 DECLARE x  INT;
 DECLARE str  VARCHAR(255);
 
 SET x = 1;
 SET str =  '';
 
 WHILE x  <= 10 DO
 SET  str = CONCAT(str,x,',');
 SET  x = x + 1; 
 END WHILE;
 
 SELECT str;
 END$$
DELIMITER ;

MySQL stored function:
A stored function is a special kind stored program that returns a single value. You use stored functions for business rules that are reusable among SQL statements or stored programs.
if the stored function returns the same result, it is considered deterministic and otherwise, the stored function is not deterministic.

DELIMITER $$
CREATE FUNCTION funName(amount double) RETURNS VARCHAR(10)
    DETERMINISTIC
BEGIN
    DECLARE rval varchar(10);
 
    IF amount > 10 THEN
 SET rval = 'A';
    ELSEIF (amount <= 10 AND p_creditLimit >= 100) THEN
        SET rval = 'B';
    ELSEIF p_creditLimit < 1000 THEN
        SET rval = 'C';
    END IF;
 
 RETURN (rval);
END

Diffrence between function and stored procuder
Function
Function must return a value.
Will allow only Select statements, it will not allow us to use DML statements.
We can use only table variables, it will not allow using temporary tables.
Stored procuder
Stored Procedure may or not return values..
Can have select statements as well as DML statements such as insert, update, delete and so on
Can use both table variables as well as temporary table in it.