数据库sqlserver-----触发器的插入，更新和删除

在学习触发器之前，先弄清DDL,DML,DQL,DCL的区别:

http://t.csdn.cn/Le3wA

触发器就是当执行某个事件的时候触发另一个事件的执行，根据事件的触发时间可分为

before和after

Before与After区别：before：(insert、update)可以对new进行修改，after不能对new进行修改。两者都不能修改old数据。

但是在sqlserver没有类似Oracle、Postgresql数据库的before功能，这里主要讲after

提要：创建两个表：buy 和detail

create table buy(buyid int not null identity(1,1),-----------表示buyid自增name varchar(20) not null,cost decimal(8,2)
)create table detail(detailid int not null identity(1,1),buyid int not null,name varchar(20) not null,kind varchar(20) not null,oldcost decimal(8,2) null,newcost decimal(8,2) null,
)

关于自增函数和数据类型的补充可以看这篇，这里不细讲：

自增函数：

http://t.csdn.cn/u1VAX

数据类型：

http://t.csdn.cn/AVGMk

两张虚拟表inserted，deleted

1.触发器的插入操作:

create trigger tri_buy_insert on buy
after insert
asinsert into detail(buyid,name,kind,oldcost,newcost)select inserted.buyid,inserted.name,'新增',0.0,inserted.costfrom inserted,buywhere inserted.buyid=buy.buyid

在buy表中插入数据之前，两张表都为空

在buy表中插入一个数据之后：

insert into buy values('薯片',7.50)

不能写为

insert into buy values(1,'薯片',7.50)

否则会报：

仅当使用了列列表并且 IDENTITY INSERT 为 ON 时，才能为表'buy'中的标识列指定显式值。

第一个字段是自增的，插入时不需要赋值

插入后，则会看到：

2.触发器的更新操作：

create trigger tri_buy_update on buy
after update
as insert into detail(buyid,name,kind,oldcost,newcost)select inserted.buyid,inserted.name,'更新',deleted.cost,inserted.costfrom inserted,deleted,buywhere inserted.buyid=buy.buyidand deleted.buyid=buy.buyid

此时再更新一下buy表：

update buy set cost=6.7
where name='薯片'

得到效果如下：

触发器的删除操作：

instead of触发器是在insert、update、delete这些操作进行之前就被激活了，并且不再去执行原来的dml SQL操作，而是用触发器内部的SQL语句代替执行。

所以用了instead of的表做任何dml操作，都只会执行触发器内部的语句

一个table或者view只能有一个instead of

原始dml语句还是不会执行

这里用after和instead of执行效果是一样的

create trigger tri_buy_delete on buy
instead of delete
asinsert into detail(buyid,name,kind,oldcost,newcost)select deleted.buyid,deleted.name,'删除',deleted.cost,0.00 from deleted,buywhere deleted.buyid=buy.buyid

执行删除操作

delete from buy where name='薯片'

得到结果：

更新进阶：

加入一张新表：calc，如下图：

	create table calc(maxcost decimal(8,2) null,mincost decimal(8,2) null,totalcost decimal(8,2) null,avgcost decimal(8,2) null
)

刚开始表为：

实现:

若calc为空，则插入新数据，若calc表不为空，则更新数据：

create trigger tri_buy_insert1 on buy
after insert
asif not exists(select * from calc)begininsert into calc(maxcost,mincost,totalcost,avgcost)select max(cost),min(cost),sum(cost),avg(cost)from cys_351_buyendelsebeginupdate calc setmaxcost=(select max(cost) from buy),mincost=(select min(cost) from buy),totalcost=(select sum(cost) from buy),avgcost=(select avg(cost) from buy)end

第一次插入数据结果呈现：