MySQL storage functions and triggers

Storage function ：

Introduce ：

A stored function is a stored procedure with a return value , The parameters of the storage function can only be in type .

create function  Store function name ([ parameter list ])
returns type [characteristic]
begin
	--SQL
	return...;
end;

characateristic explain ：
deterministic： The same input parameters always produce the same results 
NO SQL： It doesn't contain SQL sentence 
READs SQL DATA： Contains statements that read data , But it doesn't include statements that write data 

trigger ：

Introduce ：

Triggers are database objects related to tables , Referring to insert/update/delete Before or after , Trigger and execute the... Defined in the trigger SQL Statement set . This feature of trigger can help to ensure the integrity of data in database , logging , Data verification and other operations .
Use the alias OLD and NEW To refer to the changed record content in the trigger , This is similar to other databases . Now triggers only support row level triggering , Statement level triggering is not supported .

grammar ：

#  establish 
create trigger trigger_name
before/after/ insert/update/delete
on tbl_name for each row --  Line level triggers 
begin
	trigger_stmt;
end;

#  see 
show triggers;


#  Delete 
drop trigger [schema_name.]trigger_name;	--  If not specified schema_name, Default to current data 

practice ： Defining triggers , Complete the following requirements

Record by trigger student Data change log for table , Insert the change log into the log table student_logs in , Including the addition of , modify
Delete ;

Trigger when inserting data ：

mysql> delimiter //
mysql> create trigger student_insert_trigger
    -> after insert
    -> on student for each row
    -> begin
    -> insert into student_logs values(null,'insert',now(),new.sno,concat(' The inserted data content is ：sno=',new.sno,',name=',new.sname));
    -> end //
Query OK, 0 rows affected (0.01 sec)

mysql> insert into student values('20050601',' Zhao Liu ',' male ',now(),' Guiyang, Guizhou ',' Department of economics and management ',' Financial management ');
    -> //
Query OK, 1 row affected (0.01 sec)

mysql> select * from student_logs;
+----+-----------+---------------------+------------+------------------------------------------+
| id | operation | operate_time        | operate_id | operate_params                           |
+----+-----------+---------------------+------------+------------------------------------------+
|  1 | insert    | 2022-05-25 14:08:09 | 20050601   |  The inserted data content is ：sno=20050601,name= Zhao Liu  |
+----+-----------+---------------------+------------+------------------------------------------+
1 row in set (0.00 sec)

Trigger for modifying data ：

mysql> delimiter //
mysql> create trigger student_update_trigger
    -> after update
    -> on student for each row
    -> begin
    -> insert into student_logs values(null,'update',now(),new.sno,concat(' The updated data content is ：sno=',new.sno,',name=',new.sname));
    -> end //
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> update student set sbirthday = now() where sname = ' Zhao Liu ';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student_logs;
+----+-----------+---------------------+------------+------------------------------------------+
| id | operation | operate_time        | operate_id | operate_params                           |
+----+-----------+---------------------+------------+------------------------------------------+
|  1 | insert    | 2022-05-25 14:08:09 | 20050601   |  The inserted data content is ：sno=20050601,name= Zhao Liu  |
|  2 | update    | 2022-05-25 14:13:42 | 20050601   |  The updated data content is ：sno=20050601,name= Zhao Liu  |
+----+-----------+---------------------+------------+------------------------------------------+
2 rows in set (0.00 sec)

Trigger for deleting data ：

mysql> delimiter //
mysql> create trigger student_delete_trigger
    -> after delete
    -> on student for each row
    -> begin
    -> insert into student_logs values(null,'delete',now(),old.sno,concat(' The deleted data content is ：sno=',old.sno,',name=',old.sname));
    -> end //
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> delete from student where sname = ' Zhao Liu ';
Query OK, 1 row affected (0.01 sec)

mysql> select * from student_logs;
+----+-----------+---------------------+------------+------------------------------------------+
| id | operation | operate_time        | operate_id | operate_params                           |
+----+-----------+---------------------+------------+------------------------------------------+
|  1 | insert    | 2022-05-25 14:08:09 | 20050601   |  The inserted data content is ：sno=20050601,name= Zhao Liu  |
|  2 | update    | 2022-05-25 14:13:42 | 20050601   |  The updated data content is ：sno=20050601,name= Zhao Liu  |
|  3 | delete    | 2022-05-25 14:16:51 | 20050601   |  The deleted data content is ：sno=20050601,name= Zhao Liu  |
+----+-----------+---------------------+------------+------------------------------------------+
3 rows in set (0.00 sec)