Must Know These SQL Concepts For Data Administrator: Triggers, Stored Procedure and Advanced Querying

Triggers and Stored Procedure are Advanced Concepts in SQL Database, we discuss in details.

In SQL, triggers and stored procedures are both database objects that can automate tasks and enhance code reusability. Here’s a breakdown of each concept with code examples:

Triggers: A trigger is a stored procedure that automatically executes in response to specific events (INSERT, UPDATE, DELETE) on a table. They can be used for data validation, data manipulation, or side effects (e.g., logging changes).

Photo by Djim Loic on Unsplash

• Code Example:

CREATE TRIGGER update_customer_last_updated
AFTER UPDATE ON Customers  -- Trigger fires after an update on the Customers table
FOR EACH ROW  -- Applies to each row affected by the update
BEGIN
  UPDATE Customers  -- Update the same table within the trigger (be cautious to avoid infinite loops)
  SET last_updated = CURRENT_TIMESTAMP  -- Set the 'last_updated' column to the current timestamp
  WHERE customer_id = OLD.customer_id;  -- Target the same row that was updated (using OLD pseudo-table)
END;

Explanation:

• This trigger fires after any row in the Customers table is updated.
• For each updated row, it updates the…