MySQL TRIGGER

A Trigger is a database object just like procedure stored in a database. In other words, the MySQL trigger is just like a program which executes implicitly.

Why Trigger Is Necessary?

1. To maintain business data in the uniform case. A user may enter data in lowercase, uppercase or mixed case but the trigger will convert all data into a uniform case that is uppercase for analyzing the data.

2. Triggers are necessary to maintain audit information on any table data in a database.

Triggers are implicitly executed automatically executed for any DML (data manipulation) operation on the table. For example- INSERT, UPDATE, DELETE.

Parts of MySQL Trigger

There are mainly three parts of triggers as follows-

1. Trigger Event- Trigger event is associated with INSERT, DELETE, UPDATE operations.

2. Trigger Restrictions- It is simply controlling the trigger, whether you want to fire the trigger before any DML operation or after any DML operations.

3. Trigger Action- In this part the logic or the functionality of the trigger is associated. You can say what trigger will do or what you want a trigger to be done, the procedure is written in this part.

Terms of trigger

There are two main important terms for triggers-

: NEW- It will get new value from the column. It is associated with update operation and after the update operation.

: OLD- It will get old value from the column. It is associated with DELETE operation and before update operation.

Types of Trigger

Primarily triggers are of two types-

1. Record level/ Row-level triggers (total of 6 types)

2. Statement level triggers (Total 6 types)

Row-level triggers are executed for each record but statement level triggers are executed only one time for a DML operation. Statement level triggers do not worry about how many records are affected.

MySQL Trigger Example-

Step 1. Create Table

1 2 3 4 5

CREATE TABLE sales ( sid int(4), itemid int(4), qtysold int(4), price int(5), total int(10) );

Step 2. INSERT a record in the table and see the table after insertion.

1 2 3 4

INSERT INTO sales VALUES (101, 8, 9, 89, 0); SELECT * from sales;

Step 3. Write the trigger

1 2 3 4 5

CREATE TRIGGER t1 BEFORE INSERT ON sales FOR EACH ROW BEGIN SET NEW.total = NEW.qtysold * NEW.price; END;

Step 4. INSERT a record again and run the select query.

1 2 3 4

INSERT INTO sales VALUES (102, 7, 15, 98, 0); SELECT * from sales;