储存过程与触发器

触发器:

• 它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。
  • 语法

    --创建触发器(DML)
    create trigger trigger_name
    

    on tab1_name--表的名称

    before/after insert/update/delete--是在之前触发还是之后, 对表的什么操作执行的
    
    as
    
    begin--触发器要做什么，比如约束，规则等等 (add 的时候 new 可以获取到这个表的对象) 
    IF  insert/update/delete(columns)--获取当前要修改的字段
    begin

    　　declare @state varchar(10)

       select @state = ErrNum from inserted--必须需要这个inserted 给他赋值

    
    end

    --方法体这里如果去要用到表数据(insert new:代表将要新增或者已经新增的数据/update old:没有修改之前的数据 new:将要修改或已经修改的数据/delete old：表示将要活已经删除的数据) 
    
    end
    
    --删除触发器 
    
    drop trigger [schema_name.]trigger_name --schema_name没有指定的话，默认为当前的数据库
    
    --查看触发器
    
    show triggers;

• 优点: 1.强化约束：强制复杂业务的规则和要求，能实现比check语句更为复杂的约束。 
           2.跟踪变化：触发器可以侦测数据库内的操作，从而禁止数据库中未经许可的更新和变化。 
           3.级联运行：侦测数据库内的操作时，可自动地级联影响整个数据库的各项内容。
           4.嵌套调用：触发器可以调用一个或多个存储过程。触发器最多可以嵌套32层。
• 缺点: 1.可移植性差；
  　　  2.占用服务器资源，给服务器造成压力；
  　　  3.执行速度主要取决于数据库服务器的性能与触发器代码的复杂程度；
  　　  4.嵌套调用一旦出现问题，排错困难，而且数据容易造成不一致，后期维护不方便。

存储过程：

• 存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升。
• 语法:

  if (exists (select * from sys.objects where name = 'GetUser')) drop proc GetUser   --判断存储过程是否存在，存在则删除然后重建。
  go
  create proc GetUser  --创建存储过程 GetUser
  @Id int --参数
  as 
  set nocount on;  --不返回计数，提高应用程序性能
  begin --开始
      select * from [dbo].[User] where Id=@Id  --执行sql语句
  end;--结束
  --调用存储过程
  EXEC GetUser 1;--1是参数

   

• 优点: 1. 允许模块化程序设计。
           2.可维护性高，只需创建存储过程一次并将其存储在数据库中，以后即可在程序中调用该过程任意次。存储过程可独立于程序源代码而单独修改，而不需要更改、测试以及重新部署程序集。
  　　  3.运行速度快。存储过程将比T-SQL批代码的执行要快。存储过程是被编译后存放在数据库服务器的过程高速缓存中，当使用时，服务器不必再重新分析和编译它们。 
           4. 减少网络流量，在网络中要发送的数百行代码，可由一条执行其存储过程代码的一条单独语句就可实现。 
           5.代码精简一致，一个存储过程可以用于应用程序代码的不同位置。
           6.更好的版本控制，通过使用 Microsoft Visual SourceSafe 或某个其他源代码控制工具，您可以轻松地恢复到或引用旧版本的存储过程。
           7.  有安全机制，可授予没有直接执行存储过程中语句的权限的用户，也可执行该存储过程的权限。另外可以防止用户直接访问表，强制用户使用存储过程执行特定的任务。
• 缺点: 1.如果更改范围大到需要对输入存储过程的参数进行更改，或者要更改由其返回的数据，则您仍需要更新程序集中的代码以添加参数、更新获取返回值的调用，等等，这时候估计比较繁琐了。
           2.可移植性差 ,由于存储过程将应用程序绑定到 SQL Server，因此使用存储过程封装业务逻辑将限制应用程序的可移植性。如果应用程序的可移植性在您的环境中非常重要，则将业务逻辑封装在不特定于 RDBMS 的中间层中可能是一个更佳的选择。Sql语句灵活，可移植性强，查询速度比存储过程慢些。