SQL Triggers
A SQL trigger is a code block that executes automatically when a specified event occurs on a table or view, such as an insert, update or delete.
A trigger can be used to perform actions before or after the event such as checking data integrity or spread data changes between tables.
Such an example would be to create a trigger that prevents users from inserting or updating data in a table if the data violates a rule, such as a maximum length or a required field.
CREATE TRIGGER trigger_name
BEFORE/AFTER event
ON table_name
FOR EACH ROW
BEGIN
-- trigger code or call to procedure
END;
SQL Stored Procedures
A SQL Procedure is a code block that performs one or more tasks and can be called by other programs or queries. A procedure can accept parameters, return values and use variables, loops and conditional statements.
A procedure can be used to encapsulate complex logic or reuse code.
CREATE PROCEDURE procedure_name (parameters)
BEGIN
-- procedure code
END;
Best practices
- Use descriptive and consistent names.
- Document your code with comments and explain the purpose and logic
- Avoid using too many or complex procedures or functions. This may affect performance or reliability of your database. They really increase difficulty to follow an operation.
Reference(s)
https://www.linkedin.com/advice/3/how-do-you-use-sql-triggers-procedures-functions?lang=en