【2018-01-19】Sql Server-触发器

 1 --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‘;