Introduction and use of triggers and cursors in MySQL

Time:2022-1-6

Trigger introduction

Trigger is a special stored procedure associated with a table. It can trigger execution when inserting, deleting or modifying data in the table. It has finer and more complex data control ability than the standard functions of the database itself.

Advantages of trigger:

  • Security: users can have certain rights to operate the database based on the value of the database. For example, it is not allowed to modify database data after work and holidays;
  • Audit: it can track the user’s operation on the database;
  • Implement complex data integrity rules. For example, a trigger can roll back any attempt to eat more futures than its own margin;
  • Provides another way to run scheduled tasks. For example, if the fund on the company’s account is less than 50000 yuan, send warning data to the financial personnel immediately.

Using triggers in MySQL

Create trigger

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

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

The basic syntax for creating triggers is as follows:

CREATE TRIGGER
-- trigger_ Name: the name of the trigger; 
-- tirgger_ Time: trigger time, before or after;
-- trigger_ Event: trigger event, which is insert, delete or update; 
 trigger_name trigger_time trigger_event 
 ON
 -- tb_ Name: indicates the name of the table where the trigger is created and the table on which the trigger is created;
 tb_name
 --For each row means that the trigger event will be triggered if the operation on any record meets the trigger event.
 FOR EACH ROW
 -- trigger_ Stmt: the program body of the trigger, which can be an SQL statement or multiple statements contained in begin and end; 
 trigger_stmt
  • trigger_ Name: the name of the trigger;
  • tirgger_ Time: trigger time, before or after;
  • trigger_ Event: trigger event, which is insert, delete or update;
  • tb_ Name: indicates the name of the table where the trigger is created and the table on which the trigger is created;
  • trigger_ Stmt: the program body of the trigger, which can be an SQL statement or multiple statements contained in begin and end;
  • For each row means that the trigger event will be triggered if the operation on any record meets the trigger event.

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

Trigger old and new records

New and old are defined in MySQL to represent the row of data in the table where the trigger is triggered:

  • In the insert trigger, new is used to represent the new data to be (before) or (after) inserted;
  • In the update trigger, old is used to represent the original data to be or have been modified, and new is used to represent the new data to be or have been modified;
  • In the delete trigger, old is used to represent the original data that will or has been deleted.

Create a trigger. When the user purchases goods, the corresponding commodity inventory record is updated at the same time. The code is as follows:

--Delete trigger, drop trigger trigger name
--If exists, it will be deleted only if it is judged to exist
drop trigger if exists myty1;
--Create trigger
Create trigger mytg1 -- name of myty1 trigger
After insert on orders -- on which table does orders create triggers;
for each row
begin
	update product set num = num-new.num where pid=new.pid;
end;
--Insert record into order table
insert into orders values(null,2,1);
--Query commodity inventory update in commodity table
select * from product;

Create a trigger. When the user deletes the order, the corresponding commodity inventory record is updated at the same time. The code is as follows:

--Create trigger
create trigger mytg2
after delete on orders
for each ROW
begin 
--Return the inventory and add it again
	update product set num = num+old.num where pid=old.pid;
end;
--Delete order record
delete from orders where oid = 2;
--Query commodity inventory update in commodity table
select * from product;

The difference between before and after

Before before after after after after after statement execution

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

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

cursor

Introduction to cursor

The cursor is used to traverse the records returned by the query database for corresponding operations. Cursors have the following characteristics

  • The cursor is read-only, that is, it cannot be updated;
  • Cursors cannot scroll, that is, they can only traverse in one direction, can’t advance or retreat between records, and can’t skip some records;
  • Avoid updating data on tables that already have cursors open.

Create cursor

The syntax for creating cursors consists of four parts:

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

Create a procedure P1 and use the cursor to return the first student information of the student table in the test database. The code is as follows:

--Define process
create procedure p1()
begin 
	declare id int;
	declare name varchar(20);
	declare age int;
	--Define cursor declare label name cursor for select statement;
	declare mc cursor for select * from student;
	--Open cursor tag 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 cursor
	close mc;
end;
--Call procedure
call p1();

Create a student2 table in the test database, create a process P2, use the cursor to extract all student information in the student table and insert it into the student2 table. The code is as follows:

--Define process
create procedure p3()
begin 
	declare id int;
	declare name varchar(20);
	declare age int;
	declare flag int default 0;
	--Define cursor declare label name cursor for select statement;
	declare mc cursor for select * from student;
	declare continue handler for not found set flag=1;
	--Open cursor tag 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 the fetch fails
	leave a;--  Terminate cycle
	end if;
	--Traverse and insert each row of extracted data into the student2 table
	insert into student2 values(id,name,age);
	end loop;
	--Close cursor
	close mc;
end;
--Call procedure
call p3();
--Query student2 table
select * from student2;

summary

This is the end of this article on triggers and cursors in MySQL. For more information about MySQL triggers and cursors, please search previous articles of developeppaer or continue to browse the relevant articles below. I hope you will support developeppaer in the future!