SQL数据库触发器

SQL数据库触发器

基础知识

DML触发器分为两种

• after触发器
• instead of触发器

after触发器是在执行之后触发； instead of触发器是在执行之前触发

在after之后可以跟三个关键词

1. insert
2. update
3. delete
   接下来第二部分我会具体分析

instead of直接执行触发器本身

after insert

注释

运用

原样：

代码：
定义

create trigger jr on person
after insert
as
INSERT INTO Person (LastName, Address) VALUES ('Wilson', 'Champs-Elysees')

触发

INSERT INTO person
VALUES ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing')

结果

想说一下

after update

注释

运用

原样：

代码：
定义

create trigger dizhi on person
after update 
as
update person set address = 'zhongshan 23' ,city='nanjing'
where lastname ='wilson'

触发

update person set firstname='fred' where lastname = 'wilson'

结果

想说一下

注意一下定义触发器是不可以定义成具体的东西的，我最开始就是错了，下面吧错误的代码给大家警戒一下

create trigger dizhi on person
after update person set firstname='fred' where lastname = 'wilson'
as
update person set address = 'zhongshan 23' ,city='nanjing'
where lastname ='wilson'

after delete

注释

运用

原样：

代码：
定义

create trigger shanchu
on person
after delete
as
delete from person where lastname = 'wilson'

触发

delete from person where firstname='bill'

结果

想说一下

哎，数据库要多刷新啊，尤其是这个触发器。

instead of

注释

运用

原样：

代码：
定义

create trigger tidai on person
instead of delete
as
delete from person where lastname='aaa'

触发

delete from person where lastname='bbb'

结果

想说一下