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

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
简介: 数据库sqlserver-----触发器的插入,更新和删除

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


DDL(Data Definition Language)语句: 数据定义语言,主要是进行定义/改变表的结构、数据类型、表之间的链接等操作。常用的语句关键字有 CREATE、DROP、ALTER 等。


DML(Data Manipulation Language)语句: 数据操纵语言,主要是对数据进行增加、删除、修改操作。常用的语句关键字有 INSERT、UPDATE、DELETE 等。


DQL(Data Query Language)语句:数据查询语言,主要是对数据进行查询操作。常用关键字有 SELECT、FROM、WHERE 等。


DCL(Data Control Language)语句: 数据控制语言,主要是用来设置/更改数据库用户权限。常用关键字有 GRANT、REVOKE 等。

一般人员很少用到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,
)

关于自增函数和数据类型这里不细讲


注意: 两张虚拟表inserted,deleted



在对表进行操作时,总会产生 INSERTED 和(或)DELETED表,不管这个操作是否已经进行。这里的和/或,要看进行的什么操作,插入,产生 INSERTED 表,删除,产生DELETED表,而UPDATE则两个都会产生。


1.触发器的插入操作:

create trigger tri_buy_insert on buy
after insert
as
  insert into detail(buyid,name,kind,oldcost,newcost)
  select inserted.buyid,inserted.name,'新增',0.0,inserted.cost
  from inserted,buy
  where inserted.buyid=buy.buyid

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



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


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

不能写为


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

否则会报:


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


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


插入后,则会看到:



2.触发器的更新操作:

1.


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.cost
  from inserted,deleted,buy
  where inserted.buyid=buy.buyid
  and deleted.buyid=buy.buyid

此时再更新一下buy表:


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

得到效果如下:



若一定要更新第一条数据,可以用instead of

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


所以用了instead of的表做任何dml操作,都只会执行触发器内部的语句,INSTEAD OF 触发器的主要优点是使不可被修改的视图能够支持修改。


但其缺点也很明显:


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

  原始dml语句还是不会执行


after触发器和instead of触发器的区别:

after触发器是在操作成功后,所采取的一些动作


而对于instead of触发器,对数据库的操作只是一个“导火线”而已,真正起作用的是触发器里面的动作;往往触发器中会有很多分支判断语句在里面,根据不用的条件做不同的动作。


例如


创建cart表:


create table cart(
  cartid int IDENTITY(1,1),
  name varchar(20) not null,
  price decimal(8,2) not null,
  num int not null,
  total decimal(8,2) default 0.0
)


1.如果消费多于100,就-20


create trigger tri_buy on cart
instead of insert
as      
  if((select (num*price) from inserted)<100)
  begin
  insert into cart(name,price,num,total)
  select name,price,num,(price*num) from inserted
  end
  else
  begin
  insert into cart(name,price,num,total)
  select name,price,num,(price*num-20) from inserted
  end

插入数据之前,表是空的


insert into cart(name,price,num)values('两个鸡蛋煎饼',17,3)

结果:

再插入一条数据:


insert into cart(name,price,num)values('韩式时尚情侣手链',35,4)

结果:原本是140,最后结果为120




在进行第二题的编写之前要先'drop trigger tri_buy',否则会影响第二题的trigger的使用


drop trigger tri_buy

2.若买的件数小于3不优惠,大于3件,打88折,大于5件,打75折


create trigger tri_buytwo on cart
instead of insert
as
  if((select num from inserted)<5 and (select num from inserted)>=3)
  begin
  insert into cart(name,price,num,total)
  select name,price,num,(price*num*0.88) from inserted
  end
  else if((select num from inserted)>=5)
  begin
  insert into cart(name,price,num,total)
  select name,price,num,(price*num*0.75) from inserted
  end
  else
  insert into cart(name,price,num,total)
  select name,price,num,(price*num) from inserted


得到结果:




触发器的删除操作:


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

执行删除操作


delete from buy where name='薯片'

得到结果:





更新进阶:

2.


加入一张新表: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
as
  if not exists(select * from calc)
  begin
  insert into calc(maxcost,mincost,totalcost,avgcost)
  select max(cost),min(cost),sum(cost),avg(cost)
  from cys_351_buy
  end
  else
  begin
  update calc set
  maxcost=(select max(cost) from buy),
  mincost=(select min(cost) from buy),
  totalcost=(select sum(cost) from buy),
  avgcost=(select avg(cost) from buy)
  end

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



再次插入一个数据:


insert into buy values('冰淇淋',5.5)

得到结果:



对于trigger before的替代方案:


http://t.csdn.cn/UB3gC


还在学习阶段,学习到新知识会不断补充


相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
4天前
|
SQL IDE Java
Java连接SQL Server数据库的详细操作流程
Java连接SQL Server数据库的详细操作流程
|
5天前
|
SQL 存储 关系型数据库
关系型数据库中的SQL Server
【6月更文挑战第11天】
42 3
|
3天前
|
存储 SQL 关系型数据库
MySQL数据库进阶第四篇(视图/存储过程/触发器)
MySQL数据库进阶第四篇(视图/存储过程/触发器)
|
4天前
|
SQL 弹性计算 API
云服务器 ECS产品使用问题之如何通过API调用阿里云服务器上SQL Server数据库中的数据
云服务器ECS(Elastic Compute Service)是各大云服务商阿里云提供的一种基础云计算服务,它允许用户租用云端计算资源来部署和运行各种应用程序。以下是一个关于如何使用ECS产品的综合指南。
|
12天前
|
SQL 存储 数据库
如何在SQLServer中创建数据库
在SQL Server中创建数据库,可通过SSMS的图形界面或T-SQL语句。在SSMS中,连接到服务器,右键“数据库”选择“新建数据库”,配置属性后点击确定。使用T-SQL,连接到服务器,编写CREATE DATABASE语句指定数据库名称、文件路径及大小信息,执行查询完成创建。确保有足够磁盘空间,并注意权限设置。
|
17天前
|
SQL XML Java
java与sql server数据库连接
在Java中连接SQL Server数据库,你通常需要使用JDBC (Java Database Connectivity)。以下是一个简单的步骤指南,帮助你建立连接: ### 1. **下载并
|
15天前
|
关系型数据库 MySQL 数据库
MySQL数据库——触发器-案例(Insert类型、Update类型和Delete类型)
MySQL数据库——触发器-案例(Insert类型、Update类型和Delete类型)
21 0
|
15天前
|
SQL 关系型数据库 MySQL
MySQL数据库——触发器-介绍、语法(创建,查看,删除)
MySQL数据库——触发器-介绍、语法(创建,查看,删除)
10 0
|
17天前
|
SQL 存储 安全
sql server 数据库实例
SQL Server 数据库实例是指在 SQL Server 中创建的一个独立的数据库环境。每个数据库实例都拥有自己的一套完整的数据库文件、配置设置、用户和权限等,可以独立地进行管理和操作。以下是关于

热门文章

最新文章