Chapter 6 trigger and stored procedure of Mysql
1, Trigger
1. Passive. It is not executed by the user, but automatically executes a series of sql when the conditions are met
2. Trigger conditions: insert, update, delete
3. Trigger time can be divided into: after, before
Create a trigger (insert a piece of data into the student table to trigger the backup of a piece of data in the student table):
delimiter //
create trigger tr_stu1
after insert on student
for each row
begin
insert student_bak(s_no,s_name,c_no,c_name,score)
values(new.s_no,new.s_name,new.c_no,new.c_name,new.score);
end //
Create trigger (delete data in student table triggers backup of data in student table):
delimiter EOF
create trigger tr_stu_del_bak
after delete on student
for each row
begin
insert student_bak(s_no,s_name,c_no,c_name,score)
values(old.s_no,old.s_name,old.c_no,old.c_name,old.score);
end EOF
delimiter / /: defines the end identifier as / /, when / / is encountered, it means the end. It can also be defined as other
new: represents the newly inserted data
Old: old data for deletion
Delete trigger:
drop trigger tr_stu_del_bak;
2, Interview questions
Data:
create table a (aid int primary key,create_date datetime,num int,money int,status varchar(1) default 'N');
insert into a values(1,sysdate(),3,900,'N');
insert into a values(2,sysdate(),2,900,'N');
insert into a values(3,sysdate(),5,1000,'N');
create table b (bid int,aid int,create_date datetime,money int);
answer:
delimiter //
create trigger upa_addb
after update on a
for each row
begin
declare status_new varchar(1);
declare num int;
set status_new = (select status from a where aid = old.aid);
set num = 0;
if status_new = 'Y' then
while num < old.num do
insert b values(num+1,old.aid,date_add(old.create_date,interval num month),old.money/old.num);
set num = num +1;
end while;
end if;
end
//
3, Stored procedure
1. In order to process business logic, it is equivalent to the method in JAVA
2. Loop judgment plus a bunch of sql statements
3. Parametric stored procedure (sum of two parameters):
delimiter //
create procedure pro_sum(i int ,j int)
begin
select i+j 'Summation';
end //
Calling stored procedure: call Pro sum (7,8);
4. No parameter stored procedure:
delimiter //
create procedure pro_sum()
begin
declare i int;
declare j int;
set i = 5;
set j = 6;
select i+j 'Summation';
end //
Call the stored procedure: call Pro sum();
5. Insert 5000 pieces of data
delimiter //
create procedure auto_addStu(num int)
begin
declare i int;
set i = 0;
while i < num do
insert student(s_name,s_age,s_sex) values(concat('Zara',i+1),round(rand()*20+10),rand_sex());
set i = i + 1 ;
end while;
end//
Call the stored procedure: call auto? Addstu (5000);
4, Transactions
1. Either all or none.
2. Either commit or roll back the rollback.
Open transaction:
start transaction;
insert t_user(name,sex,age,buildtime) values('Zhang San','male',18,20171202125339);
insert t_user(name,sex,age,buildtime) values('Zhang San','male',18,20171202125339);
insert t_user(name,sex,age,buildtime) values('Zhang San','male',18,20171202125339);
insert t_user(name,sex,age,buildtime) values('Zhang San','male',18,20171202125331);
rollback;