SQL Triggers.pdf

DOWNLOAD ===> https://shurll.com/2tu1Uu

What are SQL Triggers and How to Use Them

A SQL trigger is a special type of stored procedure that automatically runs when an event occurs in the database server. A trigger is always associated with a particular table. If the table is deleted, all the associated triggers are also deleted automatically.

A trigger can be invoked either before or after an event such as INSERT, UPDATE, or DELETE on a table or view. These events are known as data manipulation language (DML) events. A trigger can also be invoked in response to a data definition language (DDL) event such as CREATE, ALTER, or DROP on a table, view, or other database object. A third type of trigger is a logon trigger that fires when a user's session is being established.

The main purpose of triggers is to maintain the integrity of the data and enforce business rules. For example, you can use a trigger to prevent invalid data from being inserted into a table, or to audit the changes made to a table by logging them in another table.

To create a trigger, you use the CREATE TRIGGER statement with the following syntax:

CREATE TRIGGER trigger_name [ BEFORE AFTER] event ON table_name trigger_type

BEGIN

-- trigger_logic

END;

You can also execute a stored procedure instead of writing the trigger logic in the BEGIN END block:

CREATE TRIGGER trigger_name [ BEFORE AFTER] event ON table_name trigger_type

EXECUTE stored_procedure_name;

There are two types of triggers: row level and statement level. A row level trigger executes each time a row is affected by a DML event. A statement level trigger executes once per DML event regardless of how many rows are affected.

To specify whether a trigger is row or statement level, you use the FOR EACH ROW or FOR EACH STATEMENT clause respectively.

In this article, we will show you some examples of how to create and use triggers in SQL.

Example 1: Creating a BEFORE INSERT trigger

Suppose you have a table called customers that stores the information of customers:

CREATE TABLE customers (

customer_id INT PRIMARY KEY,

first_name VARCHAR(50) NOT NULL,

last_name VARCHAR(50) NOT NULL,

email VARCHAR(100) UNIQUE NOT NULL,

phone VARCHAR(20) NOT NULL

);

You want to ensure that every customer has a valid email address before inserting into the table. You can create a BEFORE INSERT trigger that checks the email format using a regular expression and raises an error if it is invalid:

CREATE TRIGGER check_email_format BEFORE INSERT ON customers

FOR EACH ROW

BEGIN

IF NEW.email NOT REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+\\.[A-Z]{2,4}$' THEN

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid email format';

END IF;

END;

The NEW keyword refers to the new row that is being inserted. The REGEXP operator performs a pattern matching using a regular expression. The SIGNAL statement raises an error with a custom message.

Now, if you try to insert a customer with an invalid email address, you will get an error:

INSERT INTO customers VALUES (1, 'John', 'Doe', 'johndoe', '1234567890');

The output is:

Error Code: 1644. Invalid email format

Example 2: Creating an AFTER UPDATE trigger

Suppose you have another table called orders that stores the orders placed by customers:

CREATE TABLE orders (

order_id INT PRIMARY KEY,

customer_id INT NOT NULL,

order_date DATE NOT NULL,

total_amount DECIMAL(10,2) NOT NULL,

FOREIGN KEY (customer_id) REFERENCES customers(customer_id)

);

You want to keep track of the changes made to the total_amount column of this table by logging them in another table called order_audit:

CREATE TABLE order_audit ( a474f39169