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;