SQL触发器

概念

某一个表的一定的操作，触发某种条件，从而执行的一段程序。触发器是一个特殊的存储过程。

常见的触发器有三种：分别应用于Insert , Update , Delete 事件。

语法

1 2 3 4 5 6 7 8 9 10 11

create trigger 触发器名 on zg -- 在zg表建立触发器 for insert,update -- 为什么事件触发 as -- 事件触发后所要做的事情 select * from inserted select * from deleted if exists (select * from ck where 仓库号 in (select 仓库号 from inserted)) commit else rollback

1
2
3
4
5

CREATE  TRIGGER trigger_name [BEFORE|AFTER] event_name
ON table_name
BEGIN
 -- Trigger logic goes here....
END

Deleted 与 Inserted

1. Deleted表存放由于执行Delete或Update语句而要从表中删除的所有行。
2. Inserted表存放由于执行Insert或Update语句而要向表中插入的所有行。

UPDATE 操作上在表的一个或多个指定列上创建触发器(Trigger)

语法

1
2
3
4
5

CREATE  TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
ON table_name
BEGIN
 -- Trigger logic goes here....
END

触发器操作

删除

1

drop trigger trigger_name

修改

1
2
3
4
5
6
7
8
9

alter  trigger tri_ck_zg
on zg for insert,update
as
select * from inserted
select * from deleted
if exists (select * from ck where 仓库号 in (select 仓库号 from inserted))
commit
else
rollback

列出触发器

• 所有

  1 2	SELECT name FROM sqlite_master WHERE type = 'trigger'

• 指定表

  1 2	SELECT name FROM sqlite_master WHERE type = 'trigger' AND tbl_name = '表名'

习题

成绩小于60的不可更改

1 2 3 4 5 6 7 8 9

create trigger tri_grade on xkgx for update as select * from inserted select * from deleted if update(成绩) if exists(select * from deleted where 成绩<60) rollback

- 验证

1	update xkgx set 成绩=80 where课程号='303' and 学号='S101101'

成绩小于60的不可更改

1
2
3
4
5
6
7
8
9
10

create trigger tri_grade
on xkgx for update
as
select * from inserted
select * from deleted

if update(成绩)
if exists(select * from inserted join deleted on  inserted.学号=deleted.学号
where inserted.成绩>=60 and deleted.成绩<60)
rollback