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;