How to use stored procedures and triggers to extend DBMS capabilities

Stored procedures and triggers are database objects that can be used to extend the capabilities of a Database Management System (DBMS). They provide a way to encapsulate business logic, automate tasks, and enforce data integrity.

Stored Procedures:

1. Create a Stored Procedure:
   • Use the CREATE PROCEDURE statement to define a stored procedure. It can include input parameters, output parameters, and a set of SQL statements.

   DELIMITER //
   CREATE PROCEDURE sp_example(IN parameter1 INT, OUT result1 INT)
   BEGIN
       -- SQL statements
   END //
   DELIMITER ;

1. Call a Stored Procedure:
   • Use the CALL statement to execute a stored procedure.

   CALL sp_example(1, @output);

1. Input and Output Parameters:
   • Define input parameters using IN and output parameters using OUT. Parameters allow you to pass values into and out of the stored procedure.

Triggers:

1. Create a Trigger:
   • Use the CREATE TRIGGER statement to define a trigger. A trigger is associated with a specific table and is executed automatically when a specific event (e.g., INSERT, UPDATE, DELETE) occurs on that table.

   DELIMITER //
   CREATE TRIGGER tr_example
   AFTER INSERT ON table_name
   FOR EACH ROW
   BEGIN
       -- SQL statements
   END //
   DELIMITER ;

1. Trigger Events:

   • Specify the trigger event (INSERT, UPDATE, DELETE) and the timing (BEFORE or AFTER) using the BEFORE or AFTER keywords.

2. Accessing Old and New Values:

   • Use OLD and NEW to reference the old and new values in the trigger body. This is especially useful in UPDATE triggers.

   CREATE TRIGGER tr_example
   BEFORE UPDATE ON table_name
   FOR EACH ROW
   BEGIN
       -- Access old and new values
       SET @old_value = OLD.column_name;
       SET @new_value = NEW.column_name;
   END;

1. Enforce Data Integrity:
   • Triggers can be used to enforce data integrity by performing additional checks or actions when certain conditions are met.

   CREATE TRIGGER tr_check_balance
   BEFORE INSERT ON transactions
   FOR EACH ROW
   BEGIN
       IF NEW.amount > @account_balance THEN
           SIGNAL SQLSTATE '45000'
           SET MESSAGE_TEXT = 'Insufficient funds';
       END IF;
   END;

1. Drop a Trigger:
   • Use the DROP TRIGGER statement to remove a trigger from the database.

   DROP TRIGGER IF EXISTS tr_example;