存储过程 VS 触发器

一：存储过程

框架     

Create procedure 存储过程名 @参数名 数据类型, @参数名 数据类型 As declare @变量名 数据类型, @变量名 数据类型 Begin sql语句 End 参数：传进来的，指传到存储过程的指，所以D层中参数要和存储过程的参数名相同 变量：在存储过程执行的过程中，给改变量赋值

 

1.查询 Select

查询新闻表News前10条记录，字段有新闻id，题目，创建时间，所属类别，评论数量，类别ID,且按评论数量进行降序排序:

       

ALTER PROCEDURE [dbo].[news_SelectHotNews] AS BEGIN select top 10 n.id,n.title,n.createTime,c.[name],count(com.id) as comCount,c.id as caid from news n inner join category c on c.id=n.caid left join comment com on com.newId=n.id group by n.id,n.title,n.createtime,c.[name],c.id order by comCount desc END

查询结果为：

注意：inner join与left join的区别

inner join：先查询满足查询表要求的记录，然后在此查询结果上去除不满足内连接表条件的记录（即查询同时满足两者要求的记录）

left inner：区别于内链接，把不符合查询条件的记录置为null，而不是去除

举例：把left join改为inner join，查询结果为：评论数为0的记录没有了

（没有评论的新闻，即不满足 com.newId=n.id，所以把记录为0的记录从满足查询表的记录中删除）

2.增 Insert     

CREATE PROCEDURE [dbo].[news_Insert] @title varchar(20), @content text, @caId int AS BEGIN INSERT INTO news(title, [content], caId) VALUES(@title,@content,@caId) END

3.删 Delete

删除新闻

ALTER PROCEDURE [dbo].[news_Delete] @id int AS BEGIN delete from news where id=@id END

这时会报错：

这是因为：

数据库关系图——右击news表——选择关系——可看到如下关系：comment是由news的外键得来的

所以在删除时，要先删除comment，再删除news

4.改Update

Create PROCEDURE [dbo].[news_Update] @title varchar(20), @content text, @caId int, @id int AS BEGIN UPDATE news SET title = @title, [content] = @content, caId = @caId where id=@id END 【注】order by排序永远放在最后

【在查询器中设计查询】编写sql语句

查询——在编辑器中设计查询——出现添加表框（里面有改数据库用到的表）——选中要添加的表——点击添加——在查询设计器中就出现了添加的表

在表外的空白处右击——更改类型——选择对该表要进行的操作类型

选择要添加的字段——下面的表中在列中就会出现添加的字段名——在筛选器中写入要查询的条件——最下面就会自动出现相应的sql语句——然后在复制到存储过程即可（非常方便，增删改操作类似）

二：触发器

上面删除存储过程，可改为如下触发器：

create TRIGGER trigNewsDelete --触发器名称 ON news --对哪个表建触发器 instead of DELETE --进行的操作，有3中，insert，delete，update AS BEGIN declare @id int --声明一个变量 select @id=id from deleted --先把id从临时表deleted表取出来赋值给变量@id delete comment where newId=@id --先从评论表中删除对应newid评论 delete news where id=@id --再删除新闻表中对应id的新闻 END GO

【注】

1.执行过程：在D层中写删除Delete语句，命令类型还是CommandType.Text，只是在执行delete语句时，会自动触发该表的触发器

2.触发器是一种特殊的存储过程，是通过事件进行触发被动调用执行，而存储过程通过存储过程的名称调用

3.触发器针对的是增、删、改操作时会自动执行的特殊的存储过程；一般用于Check约束较复杂的情况。

4.此处用的是instead of，即在D层执行的delete语句，实际没有真正删除表中的数据，把执行结果存储在了一张临时表中deleted中了，而实际执行的是触发器里面的sql语句

5.触发器分为2中：

Instead of：（触发器之后）

1>若触发的语句中有select语句，后面不能查询所有的，即不能写为：select *，否则会报如下错误：

After：（触发器之前）

【总结】

1.在新建一个触发器或存储过程时，是以Create开头的，如果再次执行建立存储过程或触发器的语句时，会报“存在同名的错误”，这是因为以执行的create新建，但你已了一个改同名的存储过程或触发器，当然回报错了，这是可把“Create”改为“alter”修改

2.触发器是一种特殊的存储过程

3.执行存储过程用exec 存储过程名，而触发器不能这样用，因为触发器是由事件引发的