SQL 触发器练习

1.关于如何创建触发器

这是可以运行的部分。

delimiter ||
create trigger TRIG_NAME
after insert on TAB_NAME for each row
begin
  insert into tab_1 (f1, f2) values ( val1, val2 );     // 向 TAB_NAME 插入之后，执行这一行的动作。
end ||

参考   

https://www.cnblogs.com/shoelesscai/p/19198343

 

 

2.关于删除 TRIGGER

 

建立了一个错误的触发器，要删除。

 

通用： drop trigger SCHEMA.TRIG_NAME

示例： drop trigger trigger1; ||

再次创建 trigger，查看结果。

 

3.第二轮测试

（1）

create table t1 (id int auto_increment primary key, name varchar(20) );
create table t2 (id int auto_increment primary key, name varchar(20) );

（2）

create trigger ttt
after insert on t1 for each row
begin
insert into t2 (id, name) values (0, 'auto_insert');
end;

（3）

insert into t1 (id, name ) values ( 0, 'Mary' );

 

（4）结果显示

 

结论，如果直接删除表，trigger 一起删除了。

 

4.存储过程

这段程序是可以运行的。

create table employees (id int primary key, salary decimal(10,2), cate int) ;||
create table salary_log (employee_id int primary key, salary decimal(10,2), change_date datetime) ;||

# 插入代码

insert into employees (id, salary, cate ) values (1, 5000.00, 1); ||
insert into employees (id, salary, cate) values (2, 3000.00, 1); ||
insert into employees (id, salary, cate) values (3, 4000.00, 2); ||
insert into employees (id, salary, cate) values (4, 1000.00, 2); ||

# 定义

DELIMITER ||
CREATE PROCEDURE out_sum ( IN cate_id INT, OUT total_salary decimal(10, 2) )
BEGIN
select sum(salary) into total_salary from employees where cate=cate_id;
END ||

调用

call out_sum( 1, @total_salary );
select @total_salary;

参考

https://developer.aliyun.com/article/1577051

 

5.关于 CHECK

示例（之后完整代码）
var char(2) check (var IN ('1', '2') )

如果违反，返回
'tablename_chk_1' is violated

（1）

create table player (pno int primary key,
pname varchar(20),
sex varchar(1),
region varchar(20),
tel varchar(15)
);

create table contest (cno int primary key,
cname varchar(20),
type varchar(10),
Date datetime
);

create table pc (pno int references player(pno),
cno int references contest(cno),
city varchar(10) not null,
rank_1 char(2) check (rank_1 IN ('1', '2', '3', 'n') ),
point smallint,
primary key (pno,cno)
);

 

（2）

select pno from player
where pno not in (
select pno from pc, contest
where pc.cno=contest.cno and type='ai' )
order by pno;

 

（3）

insert into pc ( pno, cno, city, rank_1, point ) values (1,1,'sh','4','1');

显示
ERROR 3819 (HY000): Check constraint 'pc_chk_1' is violated.

 

6.存储过程中的 DECLARE

1） 存储过程：create procedure p_name () begin ... end;
2） 定义变量。在 bigin [...] END; 中括号中，定义变量，即
DECLARE var_name var_type;

以下语句是调得通的。

create procedure update_books_sale_type( IN bno varchar(20) )
BEGIN
  DECLARE all_nums INT;
  SELECT sum( book_nums ) INTO all_nums FROM orders
  WHERE book_no = bno;
  IF all_nums < 50 then UPDATE books SET sale_type = 'onlineNew' WHERE book_no=bno;
  ELSE UPDATE books SET sale_type='storeNew' WHERE book_no=bno;
  END IF;
  COMMIT;
END; ||

# 查看

select * from books where book_no !='f002';

# 查看
call update_books_sale_type( 'f001');
select * from books where book_no !='f002';

 注意，测试了以下，DECLARE 必须写在 BEGIN ... END; 内部。

 参考   

https://www.w3ccoo.com/mysql8/mysql_declare_statement.html

https://blog.51cto.com/u_16213412/13250802

 

 

7.触发器进阶版
同样的数据表设定 books, orders, booklimit, bookminlevel, bookorders

这段程序是可以运行的。关键点，所有的【关键字】都写在 begin ... end; 中间。

create trigger TTT after update on books for each row
begin
  IF new.all_nums < (select level from bookminlevel as a where a.book_no=old.book_no)
    AND old.all_nums >= (select level from bookminlevel as b where b.book_no=old.book_no)
  THEN
    insert into bookorders (
      select book_no, limit_amount
      from booklimit as tmp
      where tmp.book_no=old.book_no and tmp.sale_type=old.sale_type
    );
  END IF;
end; ||

 

# 查看运行结果

 注意，涉及 【update】关键字涉及 【OLD】和【NEW】。

 参考 

https://www.cnblogs.com/longkui-site/p/15859527.html

https://www.cnblogs.com/ziyiang/articles/12316384.html

 https://cloud.tencent.com/developer/article/2075506

 

8.游标练习 CURSOR

第一步：定义 CURSOR

declare cur_name cursor
for select ...

 

第二步：在 Procedure 使用

open cur_name;

fetch cur_name into v1, v2, v3;  # v1-v3 是 procedure 内部新定义的

update ... where p_no=v1 and m_no=v3;   # 某种操作

close cur_name;

 

# 创建 procedure，只执行 1 次。因为 LOOP 用不来！

create procedure update_price()
begin
  declare pno varchar(20);
  declare pprice decimal(10,2);
  declare mno varchar(20);
  declare upprice cursor for select p_no, t_price, m_no from tmp_prices;

  open upprice;
    fetch upprice into pno, pprice, mno;
    update products set price = pprice where p_no=pno and m_no=mno;
  close upprice;
  commit;
end;

 

# 调用

call update_price();    # 即 call pro_name; 

# 运行结果

 CURSOR 参考

https://learn.microsoft.com/zh-cn/sql/t-sql/language-elements/declare-cursor-transact-sql?view=sql-server-ver17
https://www.cnblogs.com/xiongzaiqiren/p/sql-cursor.html

 

9.循环 SQL

这是很少用的部分，公式如下。

declare cnt int;
set cnt=0;
while cnt<5 do
  insert into ...
  set cnt=cnt+1;
end while;

 

# 创建表
create table tmp_test ( id int );
insert into tmp_test (id) values (0);

# 创建循环

create procedure test_loop()
begin
  declare cnt int;
  set cnt=0;
  while cnt < 5 do
    insert into tmp_test (id) value (cnt);
    set cnt=cnt+1;
  end while;
end;

 

# 调用
call test_loop;

参考
https://www.cnblogs.com/guorongtao/p/11939751.html

 

10.触发器：新老版本对照

# 触发器设计
# 原来写法 referencing new row as nrow ... 改成 new.vname

create trigger stunum_add after insert on student for each row
begin
  update school set schstunum = schstunum+1
  where schno=new.schno;
end;

 

# 效果

 

# 使用 old.vname
# 不使用 begin ... end 似乎也是起作用，暂时补知道差异。

create trigger stunum_sub before delete on student for each row
begin
  update school set schstunum = schstunum-1 where schno=old.schno;
end;

 

# 删除 stuno='20262109999' 以及 schno='101' 的学生。学生名 Jingyi  ——  ：P

order by schname

 

商业赋能行业。

https://shoelesscai.com/