Basic understanding of triggers and stored procedures in mysql

Basic understanding of triggers and stored procedures in mysql: 01 trigger: a trigger that is triggered upon a certain operation (or before ), the attached operation is executed immediately. 02 purpose: monitor a situation and trigger an operation. 03 Note: 04 events that can be monitored: add, delete, modify; events that can be triggered: add, delete, and modify; 05 06 Monitoring location: table07 monitoring event: insert/update/delete08 trigger time: after/before09 trigger event: insert/update/delete10 11 # create a commodity table: 12 www.2cto.com create table goods (goodsId int, name varchar (10), num int) charset utf8 $13 14 # create order table: 15 create table orders (ordersId int, goodsId int, num int) charset utf8 $16 17 # insert data 18 insert into goods values (1, 'pig', 22), (2, 'sheep', 19), (3, 'Dog', 12), (4, 'cat', 8) $19 20 # Buy three sheep 21 insert into orders values (1, 2, 3); 22 23 # reduce sheep inventory 24 update goods set num = num-3 where goodsId = 2; 25 26 # start to use the trigger 27 # monitoring location: orders28 # monitoring operation: insert29 # Trigger operation: update30 # Trigger time: after31 32 # create the first trigger 33 create trigger t134after insert on orders35for each row36begin37 update goods set num = num-3 where goodsId = 2; 38 www.2cto.com end $39 40 # Delete trigger 41 drop trigger t1 $42 43 # create second trigger 44 create trigger t245after I Nsert on orders46for each row47begin48 update goods set num = num-new. num where goodsId = new. goodsId; 49end $50 51 # create the third trigger 52 create trigger tstrap after delete on orders54for each row55begin www.2cto.com 56 update goods set num = num + old. num where goodsId = old. goodsId; 57end $58 59 # create the fourth trigger 60 create trigger t461after update on orders62for each row63begin64 update goods set num = num + old. num-new. Num where goodsId = old. goodsId; 65end $ basic understanding of a stored procedure: 01 meaning of a stored procedure: 02 a stored procedure is similar to a function. It encapsulates a piece of code. When you want to execute this piece of code, you can call this stored procedure. In the encapsulated statement body, you can use the if/else, case, while and other control structures. SQL programming is called here. 03 # Test table creation and table operations: 04 create table users (num int, name varchar (20), password varchar (20); 05 www.2cto.com insert into users values (1, 'zwh ', 'zwh'); 06 insert into users values (2, 'lhy ', 'lhy'); 07 insert into users values (3, 'test ', 'test'); 08 insert into users values (4, 'test', 'test'); 09 insert into users values (5, 'test', 'test '); 10 insert into users values (6, 'test', 'test'); 11 insert into users values (7, 'test', 'test '); 12 insert into users values (8, 'test', 'test'); 13 insert into users values (9, 'test', 'test '); 14 insert into users values (10, 'test', 'test'); 15 www.2cto.com 16 # view the stored procedure: 17 show procedure status; 18 19 # delete an existing stored procedure: 20 drop procedure ***; 21 22 23 # change the delimiter to $: 24 delimiter $25 26 # create the first stored procedure: experience "SQL encapsulation" 27 create procedure p1 () 28begin29 select * from users; 30end $31 32 # call stored procedure 33 call p1 () $34 35 # create a second stored procedure: experience the "parameter" 36 create procedure p2 (n int) 37begin38 select * from users where num <n; 39end $ www.2cto.com 40 41 # create a third stored procedure: Experience "control structure" 42 create procedure p3 (n int, j char (1 )) 43begin44 if j = 'H' then45 select * from users where num> n; 46 else47 select * from users where num <n; 48 end if; 49end $50 51 # create the fourth stored process: experience the 'cyclic struct'. Note: The stored procedure does not return a value 52 # function: calculate the values of 1 to n and 53 create procedure p4 (n smallint) 54begin55 declare I int; 56 declare s int; 57 set I = 1; 58 www.2cto.com set s = 0; 59 while I <= n do60 set s = s + I; 61 set I = I + 1; 62 end while; 63 select s; 64end $ author Zhuang Weihong