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

Interview questions related to Laravel 8 updates- Laravel Interview questions

 Laravel 8 brought several updates and features to the framework. If you are preparing for an interview and expecting questions related to Laravel 8 updates, here are some potential questions: 1. What are the major features introduced in Laravel 8? Laravel Jetstream: A new application scaffolding for Laravel, providing teams with a starting point for building robust applications. Laravel Breeze: A lightweight and minimalistic front-end starter kit. Model Factory Classes: Introduction of factory classes for model factories, allowing for better organization of data seeding logic. Job Batching: A feature that allows you to easily run a batch of jobs and then perform some action when all the jobs have completed. Dynamic Blade Components: The ability to render Blade components dynamically. 2. Explain the improvements made to the Laravel job queue in version 8. Laravel 8 introduced Job Batching, which allows you to group multiple jobs into a batch and perform actions upon the completion ...
Post a Comment
Read more

AWS Lambda functions within a Laravel application

O ne common scenario for using AWS Lambda functions within a Laravel application is to offload specific tasks or processes that are either time-consuming, resource-intensive, or need to be executed asynchronously. Here are some common use cases: Image Processing: You can use Lambda functions to resize, crop, or manipulate images uploaded by users. For example, when a user uploads an image, trigger a Lambda function to process it and generate thumbnails or apply filters asynchronously. Email Notifications: Lambda functions can be used to send email notifications, such as welcome emails, password reset emails, or transactional emails. You can trigger Lambda functions from events within your Laravel application, such as user registration or order placement. Data Processing and Transformation: Perform data processing tasks, such as parsing CSV files, transforming data formats, or aggregating data from multiple sources. Lambda functions can be invoked by events like file uploads to S3 or by...
Post a Comment
Read more