【2018-01-19】Sql Server-触发器
时间：2021-07-01 10:21:17
帮助过：3人阅读
--create table users--创建用户表
2 --(
3 -- ids int primary key identity(1,1),
4 -- name nvarchar(200),--姓名
5 -- class nvarchar(200)--班级编号
6 --)
7
8 --create table class--创建班级表
9 --(
10 -- classCode nvarchar(200) primary key,--班级编号
11 -- className nvarchar(200),--班级名字
12 --)
13
14 ----插入班级表数据
15 --insert into class values(‘c001‘,‘一班‘);
16 --insert into class values(‘c002‘,‘二班‘);
17 --insert into class values(‘c003‘,‘三班‘);
18 --insert into class values(‘c004‘,‘四班‘);
19 --insert into class values(‘c005‘,‘五班‘);
20 ----插入用户表数据
21 --insert into users values(‘张一‘,‘c001‘);
22 --insert into users values(‘张二‘,‘c002‘);
23 --insert into users values(‘张三‘,‘c003‘);
24 --insert into users values(‘张四‘,‘c004‘);
25 --insert into users values(‘张五‘,‘c005‘);
26
27 --select * from users;
28 --select * from class;
29
30 ----触发器
31 ----（一个特殊的存储过程，没办法直接调用它，而是通过增删改的动作来触发它
32 ----一个表的一个动作只能有一个触发器）
33 create trigger users_Delete
34 on users
35 for delete--执行delete操作触发，然后执行以下操作
36 as
37 select * from users
38
39
40 create trigger users_Insert
41 on users
42 for insert--执行insert操作触发，然后执行一下操作
43 as
44 select * from users
45
46 create trigger users_Delete
47 on users
48 instead
of delete--执行delete操作时，不执行次操作，并用以下操作代替
49 as
50 select * from users
51
52 create trigger users_Delete
53 on users
54 instead
of delete
55 as
56 select * from deleted
--查询删除的数据
57
58
59 create trigger users_Delete
60 on users
61 instead
of delete
62 as
63 declare @a nvarchar(
100)
64 select @a=ids
from deleted
65 if @a=2
66 begin
67 select ‘太丑了，不能删！‘
68 end
69 else
70 begin
71 delete from users
where ids
=@a
72 end
73
74 delete from users
where ids
=2
75
76
77
78 ----级联删除
79 create trigger class_delete
80 on class
81 instead
of delete
82 as
83 declare @a nvarchar(
200);
84 select @a=classCode
from deleted;
85 delete from users
where class
=@a;
86 delete from class
where classCode
=@a;
87
88 delete from class
where classcode
=‘c001‘;
【2018-01-19】Sql Server-触发器
标签：users val 通过 pre tab 编号 from delete 创建
