
Triggers
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE| DELETE }
ON table_name FOR EACH ROW
trigger_body;
for example
CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employees
FOR EACH ROW
INSERT INTO employees_audit
SET action = 'update',
employeeNumber = OLD.employeeNumber,
lastname = OLD.lastname,
changedat = NOW();
Functions
CREATE FUNCTION function_name [ (parameter datatype [, parameter datatype]) ]
RETURNS return_datatype
BEGIN
declaration_section
executable_section
END;
for example
DELIMITER //
CREATE FUNCTION CalcIncome ( starting_value INT )
RETURNS INT
BEGIN
DECLARE income INT;
SET income = 0;
label1: WHILE income <= 3000 DO
SET income = income + starting_value;
END WHILE label1;
RETURN income;
END; //
DELIMITER ;
SELECT CalcIncome (1000);
Procedures
CREATE PROCEDURE procedure_name(parameter_list)
BEGIN
statements;
END //
DELIMITER //
CREATE PROCEDURE GetAllProducts()
BEGIN
SELECT * FROM products;
END //
DELIMITER ;
CALL GetAllProducts();