MySQL – Create Trigger

This article will explain how to create the first trigger in MySQL. Using the CREATE TRIGGER statement, we can create a new trigger in MySQL. In order to use the CREATE TRIGGER command, we must ensure that we have trigger privileges. In order to create a trigger, the following syntax should be used:

Syntax: 

CREATE TRIGGER trigger_name  trigger_time trigger_event  
ON table_name FOR EACH ROW  
BEGIN  
    --variable declarations  
    --trigger code  
END;    

There are the following parameters in the create trigger syntax:

trigger_name: This is the name of the trigger we want to create. After the CREATE TRIGGER statement, it must be written. Within the schema, the trigger name must be unique.

trigger_time: The trigger action time should either be BEFORE or AFTER the event. Defining a trigger requires this parameter. The trigger is invoked before or after each row modification in the table.

trigger_event:  This is the name of the type of operation that activates the trigger. It can be either an INSERT, UPDATE, or DELETE operation. It is possible for the trigger to invoke only one event at a time. In order to define a trigger that is invoked by multiple events, it is necessary to define multiple triggers, one for each event.

table_name: This is the name of the table associated with the trigger. This must be written after the ON keyword. A trigger would not exist if we did not specify the table name.

BEGIN END Block: The final step is to specify the statement that will be executed when the trigger is activated. The BEGIN END block contains a set of queries that define the logic for executing multiple statements.

It is possible for the trigger body to access the column’s values, which are affected by the DML statement. The NEW and OLD modifiers are used to distinguish column values BEFORE and AFTER the DML statement has been executed. The column name with NEW and OLD modifiers can be used as OLD.col_name and NEW.col_name, respectively. OLD.column_name indicates the column of an existing row before an update or deletion was made. An existing row after it has been updated or a new column that will be inserted will be indicated by NEW.col_name.

Example: 

Let’s begin by creating a trigger in MySQL that makes changes to the customers table. Our first step will be to create a new table named customers by executing the following statement:

create table customers
(cust_id int, age int, name varchar(30));

To fill the employee table with records, execute the following statement:

insert into customers
value(101,27,"Raj"),
(102,-40,"Divya"),
(103,32,"Kumari"),
(104,-39,"Marieya");

The next step is to create a BEFORE INSERT trigger. The trigger is automatically invoked if someone attempts to insert age < 0

delimiter //
create trigger age_verify2
BEFORE INSERT on customers for each row
BEGIN
if new.age > 0 then
set age = 30 WHERE old.age < 0;
end if;
end;