MySQL数据库存储过程和触发器的创建

一、基础内容

(1) 触发器是一种实施复杂数据网整形的特殊存储过程，在对表或者视图执行INSERT,UPDATE或者DELETE语句时自动执行，以防止对数据进行不正确，未授权或不一致的修改。它不可以像调用存储过程一样由用户直接调用执行。 创建触发器可以在查询分析器中用SQL语句完成，也可以用企业管理器完成。在企业管理器中书写触发器时，通过右键单机创建触发器的表，在弹出的快捷菜单中依次选择“所有任务”->“管理触发器”命令，就打开“触发器属性”对话框，然后在该对话框的“文本”框中输入常见触发器的SQL语句。
在数据库eduTest中，创建触发器的操作，然后在相关的表上执行INSERT,UPDATE,或DELETE语句，观察他们的执行结果。通过经过触发器的操作应该了解触发器的执行过程。如何建立，删除触发器。

(2)存储过程用来对一些基本的表的操作进行封装，在需要进行操作时进行调用，大大减轻编程的冗余性，增加了程序编写的条理性和易读性，以及可重用性；

(3)存储过程与触发器结合编程实验；

二、操作语句

触发器和存储过程结合使用：

(1)/测试用/

CREATE TABLE student_test(
    id INT PRIMARY KEY AUTO_INCREMENT,
    sno INT(8) ZEROFILL NOT NULL UNIQUE,
    sname VARCHAR(20) NOT NULL,
    sage INT CHECK(sage>=16 AND sage<=60),
    ssex VARCHAR(5) CHECK(ssex IN('男','女')),
    sdept VARCHAR(10) DEFAULT 'cs'
);

DROP TABLE student_test;
SELECT * FROM student_test;

INSERT INTO student_test(sno,sname,sage,ssex) VALUE(04151078,'黎涛',20,'男');
INSERT INTO student_test(sno,sname,sage,ssex,sdept) VALUE(04151079,'郗宇',21,'ccc','cs');
INSERT INTO student_test(sno,sname,sage,ssex,sdept) VALUE(04151080,'张晰',19,'女','is');
INSERT INTO student_test(sno,sname,sage,ssex,sdept) VALUE(04151081,'王伟',70,'男','is');

/创建日志表信息对表操作完成后触发写进日志表/

CREATE TABLE text_log(
    id INT PRIMARY KEY AUTO_INCREMENT, 
    opra_table VARCHAR(20) NOT NULL,
    opra_type VARCHAR(20) NOT NULL,
    opra_remark VARCHAR(20)
);

/创建student表插入的触发器/

CREATE TRIGGER trigger_insert_student AFTER INSERT ON student FOR EACH ROW 
INSERT INTO text_log(opra_table,opra_type,opra_remark) VALUE('student','insert','插入');

/删除触发器pro_insert_student/

DROP TRIGGER trigger_insert_student;

/创建student表删除的触发器/

CREATE TRIGGER trigger_delete_student AFTER DELETE ON student FOR EACH ROW 
INSERT INTO text_log(opra_table,opra_type,opra_remark) VALUE('student','delete','删除');

/删除触发器pro_insert_student/

DROP TRIGGER trigger_delete_student;

/定义会话变量/

SET @sno_start=04151087;
SET @sno_end=04151095;

/查看会话变量/

SELECT @sno_start;
SELECT @sno_end;

/创建批插入的存储过程，触发插入操作对应的触发器/

DELIMITER $
CREATE PROCEDURE insert_student(IN sno_start INT, IN sno_end INT)
 BEGIN
    DECLARE i INT DEFAULT 0;
    SET i=sno_start;
    WHILE i<=sno_end DO
         INSERT INTO student(sno,sname,sage,ssex,sdept) 
        VALUE(i,'安雨轩',18+i-sno_start,'女','net');
         SET i=i+1;
    END WHILE;
 END 
$

/调用存储过程，触发插入操作对应的触发器/

CALL insert_student(@sno_start,@sno_end);

/创建批删除的存储过程，触发删除操作对应的触发器/

DELIMITER $
CREATE PROCEDURE delete_student(IN sno_start INT, IN sno_end INT)
 BEGIN
    DECLARE i INT DEFAULT 0;
    SET i=sno_start;
    WHILE i<=sno_end DO
         DELETE FROM student WHERE student.sno=i;
         SET i=i+1;
    END WHILE;
 END 
$

/调用存储过程，触发删除操作对应的触发器/

CALL delete_student(@sno_start,@sno_end);

/创建一个触发器，在对student_test表进行insert delete update 时触发日志信息更新，以及查询student_test表/

CREATE TRIGGER trigger_update_stuTest_to_select AFTER UPDATE ON student_test FOR EACH ROW 
INSERT INTO text_log(opra_table,opra_type,opra_remark) VALUE('student_test','update','修改');

/更新student_test表的信息，触发student_test的更新触发器/

UPDATE student_test SET student_test.ssex='男' WHERE student_test.sno=04151079;

/查询student_test表/

SELECT * FROM student_test;

修改前：

image.png
image.png
修改后：
image.png
image.png
/查询student表的数据添加情况/

SELECT * FROM student;

/查询text_log表的数据更新情况/

SELECT * FROM text_log;

image.png
/查询student表的数据添加情况/

SELECT * FROM student;

image.png
/查询text_log表的数据更新情况/

SELECT * FROM text_log;

image.png

DROP PROCEDURE insert_student;
DROP TABLE text_log;

存储过程：

(1)/查询网络工程系年龄不大于20的学生的信息/
/定义会话变量进行值得传递/

SET @dept='net';
SET @age=21;
DELIMITER $
CREATE PROCEDURE select_sdept_sage(IN dept VARCHAR(10),IN age INT)
BEGIN 
    SELECT * FROM student WHERE student.sage<=age AND 
student.sdept=dept; 
END     
$ 

CALL select_sdept_sage(@dept,@age);

image.png
触发器：
（1）/创建student表插入的触发器/

CREATE TRIGGER trigger_insert_student_test  AFTER INSERT ON student_test FOR EACH ROW
 INSERT INTO text_log(opra_table,opra_type,opra_remark) VALUE('student','insert','插入');

/*删除触发器pro_insert_student_test */

DROP TRIGGER trigger_insert_student_test ;

插入数据：

INSERT INTO student_test(sno,sname,sage,ssex,sdept) VALUE(04151084,'张发',18,'女','is');
INSERT INTO student_test(sno,sname,sage,ssex,sdept) VALUE(04151085,'程里',19,'男','is');

插入前：
student_test：

image.png

text_log：

image.png
插入后：
student_test
image.png
text_log
image.png
（2）/创建student表删除的触发器/

CREATE TRIGGER trigger_delete_student_test  AFTER DELETE ON student_test  FOR EACH ROW
 INSERT INTO text_log(opra_table,opra_type,opra_remark) VALUE('student','delete','删除');

/*删除触发器pro_insert_student_test */

DROP TRIGGER trigger_delete_student_test ;

/删除语句/

DELETE FROM student_test WHERE student_test.sno=04151079;

删除前：
student_test：

image.png
text_log：
image.png
删除后：
student_test：
image.png
text_log：
image.png
（3）/创建一个触发器，在对student_test表进行insert delete update 时触 发日志信息更新，以及查询student_test表/

CREATE TRIGGER trigger_update_stuTest_to_select AFTER UPDATE ON student_test FOR EACH ROW 
INSERT INTO text_log(opra_table,opra_type,opra_remark) VALUE('student_test','update','修改');

/更新student_test表的信息，触发student_test的更新触发器/

UPDATE student_test SET student_test.ssex='女' WHERE student_test.sno=04151079;

更新前：

student_test：

image.png

text_log：

image.png
更新后：
student_test：
image.png

text_log：

image.png