随笔- 24 文章- 0 评论- 3 阅读- 29109

Sql Server 触发器

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

/*
SQL Server触发器
  1.什么是触发器
    触发器是一种特殊类型的存储过程，因为它和存储过程有一样的特征，都是预用写好的Sql命令存储在SqlServer服务器中。
    触发器在指定的表中的数据发生变化（insert/update/delete）的时候会自动执行。它与存储过程的区别也在此，因为存储过程是需要我们用命令去调用的。
    触发器一般用在比check约束更加复杂的约束上面。
 
    注意:触发器本身就是一个事务，所以在触发器里面可以对修改数据进行一些特殊的检查。如果不满足可以利用事务回滚，撤销操作。
 
  2.触发器分类
    DML触发器和DDL触发器。
 
    DML触发器：
    当数据库中表中的数据发生变化时，包括insert,update,delete任意操作，如果我们对该表写了对应的DML触发器，那么该触发器将自动执行。
    DML触发器的主要作用在于强制执行业 务规则，以及扩展Sql Server约束，默认值等。因为我们知道约束只能约束同一个表中的数据，而触发器中则可以执行任意Sql命令。
    例子:我们想要在送货后减少库存的数量，那我们就可以在送货单的表上写update触发器，用来减少库存的数量。
 
    DDL触发器:
    它是Sql Server2005新增的触发器，主要用于审核与规范对数据库中表，触发器，视图等结构上的操作。比如在修改表，修改列，新增表，新增列等。
    它在数据库结构发生变化时执行，我们主要用它来记录数据库的修改过程，以及限制程序员对数据库的修改，比如不允许删除某些指定表等。
 
    DML触发器：
       1.after触发器（之后触发）
           a.insert触发器
           b.update触发器
           c.delete触发器
 
       2.insert of 触发器 之前触发
 
       after触发器只有在执行insert、update、delete之后才能触发，且只能定义在表上。
       而insert of 触发器表示并不执行其定义的操作（insert、update、delete）而仅是执行触发器本身。既可以在表上定义，也可在视图上定义。
 
  3.触发器有两个非常特殊，非常重要的两个表。
    insetred:插入表
    deleted:删除表
    这两个是逻辑表，也是虚表。由系统在内存中自动创建这两张表，这两张表不会存储在数据库中，而且这两张表都是只读的。这两张表的结果总是与被
    触发器应用的表的结构相同。当触发器完成后，这两表将会被删除。
 
    Inserted表的数据是插入或是修改后的数据，而deleted表的数据是更新前的或是删除的数据。
 
 
  4.编码实现
    业务逻辑:有水果销售表和水果库存表，
       */
--创建表
 
create table FruitsSaleTable
(
FruitsName varchar(20) primary key not null,  --水果名称
Supplier varchar(50),  --供货商
SaleNumber int,  --销售数量
SaleUnitPrice money,  --单价
SaleTotalSum money   --销售总金额
)
 
insert into FruitsSaleTable(FruitsName,Supplier,SaleNumber,SaleUnitPrice,SaleTotalSum)
values('冰糖雪梨','B',20,10,200)
 
create table FruitsStock
(
FruitsName varchar(20) primary key not null,  --水果名称
StockNumber int,       --库存数量
StockUnitPrice money,  --库存单价
StockTotalSum money    --库存总金额
)
 
 
/*
Insert:
首先我们在库存表（FruitsStock）中创建一个Insert触发器，
目的：1.库存总金额=库存数量*库存单价 这个等式成立
      2.确保收货时 “库存数量”>0  不然收货动作将毫无意义
*/
 
create trigger I_insert_FruitsStock  --创建触发器
on FruitsStock   --作用那张表
for insert    --触发条件
as
--提交事务
begin transaction
 
--判断收货库存是否>0
if not exists
(
select StockNumber from FruitsStock where FruitsName in (select FruitsName from inserted) and StockNumber >0
)
begin
--不满足条件，提示
raiserror('错误，收货数量不能小于0',16,1)
 
--事务回滚
rollback
return
end
 
--条件满足，强制执行SQL，保证业务逻辑
update FruitsStock set StockTotalSum = StockNumber * StockUnitPrice 
where FruitsName in (select FruitsName from inserted)
 
--提交事务
commit transaction
go
 
--测试触发器
--1.数据都正确
insert into FruitsStock
(FruitsName,StockNumber,StockUnitPrice,StockTotalSum)
values('苹果',50,10,500)
 
/*
结果:OK
(1 行受影响)
*/
 
--2.库存总金额<>库存数量*库存单件
insert into FruitsStock
(FruitsName,StockNumber,StockUnitPrice,StockTotalSum)
values('冰糖雪梨',30,10,100)
 
/*
结果:OK
(1 行受影响)
查询结果可的：触发器自动进行计算，结果中显示正确的库存总金额
*/
 
--3.当收货数量为<=0时
insert into FruitsStock
(FruitsName,StockNumber,StockUnitPrice,StockTotalSum)
values('蓝莓',0,10,100)
 
/*
结果:OK
消息 50000，级别 16，状态 1，过程 I_insert_FruitsStock，第 138 行
错误，收货数量不能小于0
消息 3609，级别 16，状态 1，第 124 行
事务在触发器中结束。批处理已中止。
*/
 
 
/*
update:
首先我们在销售表（FruitsSaleTable）中创建一个update触发器，
业务逻辑：更改销售价格，销售总金额一起改变
*/
 
create trigger U_update_FruitsSaleTable
on FruitsSaleTable
for update
as
 
if update(SaleUnitPrice)
declare @price money  --更改后的销售价格
--提交事务
begin transaction
select @price=SaleUnitPrice from  FruitsSaleTable where FruitsName in (select FruitsName from inserted)
--begin
--if(@price<=0)
-- print'价格修改错误'
-- rollback
-- return 
-- end
update FruitsSaleTable set SaleTotalSum =SaleNumber * @price where FruitsName in (select FruitsName from inserted)
 
commit transaction
go
 
 
/*
update触发器测试
*/
update FruitsSaleTable set SaleUnitPrice=7 where FruitsName='苹果'
 
/*
结果:
(1 行受影响)
*/
 
 
/*
delete触发器：
业务逻辑：删除库存表中数据时同时删除销售表
*/
 
create trigger D_delete_FruitsStock
on FruitsStock
for delete
as
begin
delete FruitsStock from FruitsStock fs,deleted d where fs.FruitsName=d.FruitsName
delete FruitsSaleTable from FruitsSaleTable st,deleted d where st.FruitsName=d.FruitsName
end
go
 
 
 
/*
delete触发器
(0 行受影响)
 
(1 行受影响)
*/
 
delete from FruitsStock where FruitsName='冰糖雪梨'
 
select * from FruitsStock
 
select * from FruitsSaleTable
 
 
/*
写的比较简单，本人也处于学习阶段，有错误的地方希望可以指点一下。
*/