Skip to main content

MySQL Trigger - Testdome Code challenge

 
A MySQL Trigger
It is a set of SQL statements that are automatically executed  on a particular table (such as INSERT, UPDATE, or DELETE) when few event happens. Triggers are useful for handling the business logic, log the audit and modify the data on database level.
 
Following is the Syntax of a MySQL Trigger

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
   -- Add your SQL statements inside this block
END;

Lets know more with few examples. 


Example 1: Audit Log on Insert

Imagine if we have a users table and audit_log table.We want to log info whenever a new user is added to users table.

Let's Create Tables first:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

CREATE TABLE audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    action_time DATETIME,
    action VARCHAR(100)
);

Next we need to create the Trigger,  

CREATE TRIGGER after_user_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO audit_log(action_time, action)
    VALUES (NOW(), CONCAT('New user added: ', NEW.name));
END;
Below is the  Trigger Keywords used here.

NEW.column_name: Refers to the new value (used in INSERT and UPDATE)
OLD.column_name: Refers to the old value (used in UPDATE and DELETE)

Example 2: Prevent Negative Balance for the user.
Suppose you have an accounts table and your have to prevent users from setting a negative balance.

CREATE TRIGGER before_balance_update
BEFORE UPDATE ON accounts
FOR EACH ROW
BEGIN
    IF NEW.balance < 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Balance cannot be negative';
    END IF;
END;

Trigger Limitations
Even though trigger is a good option, there are some limitations. 
1. It Cannot call stored procedures that modify data.

2. No trigger nesting or recursion.

3. Triggers can't be used on VIEWs.


Example 3:
Let's create a MySQL trigger that will insert the name of the deleted item into the item_archive table:This example is from testdome.com

DELIMITER $$

CREATE TRIGGER item_delete

AFTER DELETE ON item

FOR EACH ROW

BEGIN

    INSERT INTO item_archive(name) VALUES (OLD.name);

END$$


DELIMITER ;

Comments

Post a Comment

Popular posts from this blog

MySQL's ACID compliance

Mysql acid compliance ACID is an acronym that stands for four key properties of database transactions: Atomicity Ensures that a transaction is treated as a single, indivisible unit of work Either all operations within a transaction are completed successfully, or none are If any part of the transaction fails, the entire transaction is rolled back to its previous state Prevents partial updates that could leave the database in an inconsistent state Consistency Guarantees that a transaction brings the database from one valid state to another valid state All data written to the database must adhere to defined rules, constraints, cascades, triggers, and other database integrity mechanisms Ensures that any transaction will not break the database's predefined rules Isolation Determines how and when changes made by one transaction become visible to other transactions Prevents interference between concurrent transactions MySQL provides different isolation levels: Read Uncommitted Read Commit...
1 comment
Read more

PHP OOPs exercise - Basic Oops

  Here are key PHP OOP (Object-Oriented Programming) exercise questions with solutions: Basic Class and Object Exercise: // Create a simple bank account class class BankAccount {     private $accountNumber;     private $balance;     public function __construct($accountNumber, $initialBalance = 0) {         $this->accountNumber = $accountNumber;         $this->balance = $initialBalance;     }     public function deposit($amount) {         if ($amount > 0) {             $this->balance += $amount;             return true;         }         return false;  ...
Post a Comment
Read more

Interview questions for Senior PHP Developer particle41.com

1.Self Introduction 2.Basic questions on session and cookie. 3.Where is session stored? 4.Difference between Cookie and session. 5.Will there be any session before session start? 6.Post Max execution time.How can we modify it? 7.We have a string, "BJFSJK".Without any php function reverse it with half the string length.   To reverse the string with half the string length without using any PHP functions, you can implement a simple algorithm to achieve the desired result. Here's how you can do it: Initialize two pointers, one at the beginning of the string and the other at the midpoint of the string. Swap characters between these two pointers iteratively, moving the pointers towards each other until they meet or cross each other. Here's the PHP code to implement this algorithm:  <?php $string = "ABC100"; $length = strlen($string); // Calculate the midpoint of the string $midpoint = (int)($length / 2); // Initialize pointers $start = 0; $end = $length - 1; //...
Post a Comment
Read more