Introduction and use of triggers and cursors in MySQL

Trigger Introduction

A trigger is a special stored procedure associated with a table that can be triggered and executed when data in a table is inserted, deleted, or modified. It has more sophisticated and complex data control capabilities than the standard functions of the database itself.

Advantages of triggers:

• Security: Users can be given certain rights to operate the database based on the values ​​of the database. For example, it is not allowed to modify database data after get off work and on holidays;
• Auditing: can track user operations on the database;
• Implement complex data integrity rules. For example, a trigger can roll back any attempt to take futures exceeding one's margin;
• Provides an alternative way to run scheduled tasks. For example, if the funds in the company's account are less than 50,000 yuan, a warning data will be sent to the financial personnel immediately.
  

Using triggers in MySQL

Create a trigger

The trick to creating triggers is to remember the four elements of a trigger:

• Monitoring location: table;
• Monitoring events: insert/update/delete;
• Trigger time: after/before;
• Triggering events: insert/update/delete.
  

The basic syntax for creating a trigger is as follows:

CREATE TRIGGER
-- trigger_name: the name of the trigger; 
-- tirgger_time: trigger time, BEFORE or AFTER;
-- trigger_event: trigger event, which can be INSERT, DELETE or UPDATE; 
 trigger_name trigger_time trigger_event 
 ON
 -- tb_name: indicates the table name where the trigger is created, and on which table the trigger is created;
 tb_name
 -- FOR EACH ROW means that any operation on a record that satisfies the trigger event will trigger the trigger.
 FOR EACH ROW
 -- trigger_stmt: The body of the trigger, which can be a single SQL statement or multiple statements enclosed by BEGIN and END; 
 trigger_stmt

• trigger_name: the name of the trigger;
• tirgger_time: trigger time, either BEFORE or AFTER;
• trigger_event: trigger event, which can be INSERT, DELETE or UPDATE;
• tb_name: indicates the table name where the trigger is created, and on which table the trigger is created;
• trigger_stmt: The body of the trigger, which can be a single SQL statement or multiple statements enclosed by BEGIN and END;
• FOR EACH ROW means that any operation on a record that satisfies the trigger event will trigger the trigger.
  

Note: For the same trigger event with the same trigger time in the same table, only one trigger can be defined.

Trigger new and old records

NEW and OLD are defined in MySQL to indicate the row of data that triggered the trigger in the table where the trigger is located:

• In an INSERT trigger, NEW is used to indicate new data that is about to be inserted (BEFORE) or has already been inserted (AFTER);
• In an UPDATE trigger, OLD is used to indicate the original data that is about to be or has been modified, and NEW is used to indicate the new data that is about to be or has been modified.
• In a DELETE trigger, OLD is used to indicate that the original data is about to be or has been deleted.
  

Create a trigger to update the corresponding product inventory record when a user purchases a product. The code is as follows:

-- Delete trigger, drop trigger trigger name-- if exists, it will be deleted only if it exists drop trigger if exists myty1;
-- Create a trigger create trigger mytg1-- the name of the myty1 trigger after insert on orders-- orders on which table to create the trigger;
for each row
begin
	update product set num = num-new.num where pid=new.pid;
end;
--Insert records into the order table insert into orders values(null,2,1);
-- Query the inventory update status of the product in the product table select * from product;

Create a trigger to update the corresponding product inventory record when the user deletes an order. The code is as follows:

-- Create trigger create trigger mytg2
after delete on orders
for each ROW
begin 
-- Roll back the inventory and re-add update product set num = num+old.num where pid=old.pid;
end;
-- Delete order records delete from orders where oid = 2;
-- Query the inventory update status of the product in the product table select * from product;

The difference between before and after

before Before executing a statement after After executing a statement

When the order quantity exceeds the inventory, modify the order quantity to the maximum inventory:

-- -- Create a before trigger create trigger mytg3
before insert on orders
for each row 
begin 
	-- Define a variable to receive the inventory declare n int default 0;
	-- Query inventory and assign num to n
	select num into n from product where pid = new.pid;
	-- Determine whether the order quantity is greater than the inventory if new.num>n then
		-- Greater than modify order inventory (inventory changed to maximum quantity)
	set new.num = n;
	end if;
	update product set num = num-new.num where pid=new.pid;
end;
--Insert records into the order table insert into orders values(null,3,50);
-- Query the inventory update status of the product in the product table select * from product;
-- Query the order table select * from orders;

cursor

Introduction to cursors

The function of the cursor is to traverse the records returned by querying the database in order to perform corresponding operations. The cursor has the following characteristics:

• The cursor is read-only, which means it cannot be updated;
• The cursor cannot be scrolled, that is, it can only be traversed in one direction, and cannot move forward and backward between records at will, and cannot skip certain records;
• Avoid updating data on a table that has an open cursor.
  

Creating a Cursor

The syntax for creating a cursor consists of four parts:

• Define a cursor: declare cursor name cursor for select statement;
• Open the cursor: open cursor name;
• Get results: fetch cursor name into variable name [, variable name];
• Close cursor: close cursor name;
  

Create a procedure p1 that uses a cursor to return the first student information in the student table in the test database. The code looks like this:

-- Define procedure create procedure p1()
begin 
	declare id int;
	declare name varchar(20);
	declare age int;
	-- Define cursor declare cursor name cursor for select statement;
	declare mc cursor for select * from student;
	-- Open the cursor open cursor name;
	open mc;
	-- Get data fetch cursor name into variable name [, variable name];
	fetch mc into id,name,age;
	-- Print select id,name,age;
	-- Close the cursor close mc;
end;
-- Call procedure call p1();

Create a student2 table in the test database, create a procedure p2, use a cursor to extract all student information from the student table and insert it into the student2 table. The code looks like this:

-- Define procedure create procedure p3()
begin 
	declare id int;
	declare name varchar(20);
	declare age int;
	declare flag int default 0;
	-- Define cursor declare cursor name cursor for select statement;
	declare mc cursor for select * from student;
	declare continue handler for not found set flag=1;
	-- Open the cursor open cursor name;
	open mc;
	-- Get data fetch cursor name into variable name [, variable name];
	a:loop -- loop to get data fetch mc into id,name,age;
	if flag=1 then -- trigger the continue handler when fetch fails
	leave a;-- terminate the loop end if;
	-- Traverse and insert each row of data extracted into the student2 table insert into student2 values(id,name,age);
	end loop;
	-- Close the cursor close mc;
end;
-- Call procedure call p3();
-- Query the student2 table select * from student2;

Summarize

This is the end of this article about triggers and cursors in MySQL. For more relevant MySQL triggers and cursors, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!