MySQL数据库--触发器

一.触发器的概述

        触发器是一种与表操作（INSERT、UPDATE、DELETE）有关的数据库对象。是一种特殊的存储过程，在插入、删除或修改表中的数据时触发执行。触发器定义了一系列操作，这一系列操作称为触发程序。当触发器所在表上出现INSERT、UPDATE以及DETETE操作时，将激活触发器。实现操作数据前、后，强制检验或转换数据等操作。

二.触发器的优点

        触发器可以通过数据库中的相关表实现级联无痕更改操作；保证数据安全，进行安全校验。

三.触发器的缺点

        增加了数据库表数据维护的复杂程度；触发器的无痕操作会造成数据在程序（如PHP、Java等）层面不可控。

四.语法结构

五.案例实战

# 1.在student表上创建一个触发器（stud_insert），实现当插入一个学生学生记录的时候，保证学生的年龄只能是16~40岁，性别只能是男或女。
-- 查看student表结构
desc
    student;

-- 删除触发器（stud_insert）
drop
    trigger if exists stud_insert;

-- 创建触发器（stud_insert）
create
    trigger stud_insert before
insert
    on student for each row
begin
declare
    now_year year;
select
    year(now()) into now_year;
if (now_year - year(new.S_bir) not between 16 and 40)
and (new.S_sex not in ('男', '女')) then SIGNAL SQLSTATE '45000'
SET
    MESSAGE_TEXT = '学生的年龄只能是16~40岁,性别只能是男或女';
end if;
if now_year - year(new.S_bir) not between 16 and 40 then SIGNAL SQLSTATE '45000'
SET
    MESSAGE_TEXT = '学生的年龄只能是16~40岁';
end if;
if new.S_sex not in ('男', '女') then SIGNAL SQLSTATE '45000'
SET
    MESSAGE_TEXT = '学生的性别只能是男或女';
end if;
end;

-- 测试触发器（stud_insert）
insert into
    student
values
    (
        18122221329,
        '黄天宇',
        '男',
        '1949-10-01',
        '18770146874',
        '878267152@qq.com'
    );

insert into
    student
values
    (
        18122221329,
        '黄天宇',
        '中',
        '2002-08-24',
        '18770146874',
        '878267152@qq.com'
    );

insert into
    student
values
    (
        18122221329,
        '黄天宇',
        '中',
        '1949-10-01',
        '18770146874',
        '878267152@qq.com'
    );

insert into
    student
values
    (
        18122221329,
        '黄天宇',
        '男',
        '2002-08-24',
        '18770146874',
        '878267152@qq.com'
    );

# 2.在student表上创建一个触发器（stud_update）,实现当更新一个学生的学号的时候，为了保证数据完整性，同时更新score表中该学生的学号。
-- 删除触发器（stud_update）
drop
    trigger if exists stud_update;

-- 创建触发器（stud_update）
create
    trigger stud_update after
update
    on student for each row
begin
declare
    old_sno bigint;
declare
    new_sno bigint;
set
    old_sno = old.S_no;
set
    new_sno = new.S_no;
if old_sno <> new_sno then
update
    score
set
    S_no = new_sno
where
    S_no = old_sno;
end if;
end;

-- 测试触发器（stud_update）
update
    student
set
    S_no = 18122221327
where
    S_no = 18122221321;

# 3.在student表上创建一个触发器（stud_delete）,实现当删除一个学生记录的时候，为了保证数据完整性，同时删除该学生的所有课程成绩。
-- 删除触发器（stud_delete）
drop
    trigger if exists stud_delete;

-- 创建触发器（stud_delete）
create
    trigger stud_delete after
delete
    on student for each row
begin
declare
    old_sno bigint;
set
    old_sno = old.S_no;
delete from
    score
where
    score.S_no = old_sno;
end;

-- 测试触发器（stud_delete）
delete from
    student
where
    S_no = 18122221329;