SQL Triggers & Stored Procedures

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