6.3  使用触发器

触发器是一种特殊类型的存储过程，它不同于们前面介绍过的存储过程。触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用。当对某一个表进行诸如UPDATE、INSERT、DELETE这些操作时，SQL Server就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合由这些SQL语句所定义的规则。

触发器的主要作用就是：使用触发器能够实现由主键和外键所不能保证的、复杂的参照完整性和数据的一致性。

例0611  创建触发器

本例首先显示如图6-13所示的界面，用户可以在其中输入创建触发器的命令语句（默认输入了一个禁止向“操作员”表中插入数据的触发器代码），单击“创建触发器”按钮，即可在MS SQL Server数据库中创建相应的触发器。

图6-13  创建触发器

创建触发器的SQL语句如下：

CREATE TRIGGER trigger_name

ON { table | view }

[ WITH ENCRYPTION ]

{

    { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ ,] [ DELETE] }

        [ WITH APPEND ]

        [ NOT FOR REPLICATION ]

        AS

        [ { IF UPDATE ( column )

            [ { AND | OR } UPDATE ( column ) ]

                [ ...n ]

        | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )

                { comparison_operator } column_bitmask [ ...n ]

        } ]

        sql_statement [ ...n ]

    }

}

其中部分参数的含义如下：

—    trigger_name：是触发器的名称。在数据库中必须唯一。

—    table | view：是在其上执行触发器的表或视图，有时称为触发器表或触发器视图。可以选择是否指定表或视图的所有者名称。

—    { [INSERT] [,] [UPDATE] [,] [DELETE] }：是指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。在触发器定义中允许使用以任意顺序组合的这些关键字。必须至少指定一个选项，如果指定的选项多于一个，则需要用逗号分隔这些选项。

—    AS：是触发器要执行的操作。

 新建index.asp文件，在文件首部包含以下代码引用到数据库的连接。

<!--#include file="conn.asp"-->

 创建如图6-13所示的表单，设置文本区域控件的名称为“txtSql”，并输入初始的创建触发器语句。

CREATE TRIGGER dis_insert

ON 操作员

FOR INSERT

AS

BEGIN

 RAISERROR('不允许向“操作员”表中增加数据!',10,1)

 ROLLBACK TRAN

END

 当用户单击“创建触发器”按钮时提交表单，使用以下代码检查输入的SQL语句是否正确。

<%

   On Error Resume Next

   strsql=Request.Form("txtSql")

   If InStr(UCase(strsql),"CREATE TRIGGER")=0 Then

      Response.Write "<script language=javascript>"

      Response.Write "alert('请输入创建触发器的SQL语句！');"

      Response.Write "window.history.go(-1);"

      Response.Write "</script>"

      Response.End

   End If

 执行创建触发器的SQL语句，并检查错误对象Err的值，根据错误值判断创建触发器操作是否成功。

   conn.Execute(strsql)

   If Err<>0 Then

      Response.Write "<script language=javascript>"

      Response.Write "alert('创建触发器失败，可能该触发器已经存在！');"

      Response.Write "</script>"

   Else

      Response.Write "<script language=javascript>"

      Response.Write "alert('触发器创建成功');"

      Response.Write "</script>"

   End If

End If

%>

本例使用SQL语句创建触发器，本例代码可应用到以下场景：

—    在人事管理系统中，为“操作员”表创建触发器，禁止插入新的内容。

—    在人事管理系统中，为员工工资表创建触发器，使员工工资在规定的范围之内。

—    在库房管理系统中，为商品类型表创建触发器，使数据保持完整性。

例0612  显示表的触发器

本例显示为指定表设置的触发器，如图6-14所示，在表单中输入表名，单击“提交”按钮，将在下方的表格中显示为该表设置的触发器，可显示触发器的名称、创建者，以及是哪类触发器（图6-14显示为Insert触发器）。

图6-14  显示表的触发器

本例使用系统存储过程sp_helptrigger返回指定表中定义的当前数据库的触发器类型。该存储过程的语法格式如下：

sp_helptrigger [ @tabname = ] 'table' [ , [ @triggertype = ] 'type' ]

其中各参数的含义如下：

—    [@tabname =] 'table'，是当前数据库中表的名称，将返回该表的触发器信息。table的数据类型为nvarchar(776)，没有默认值。

—    [@triggertype =] 'type'，是触发器的类型，将返回此类型触发器的信息。type 的数据类型为char(6)，默认值为NULL，并且可以是DELETE、INSERT、UPDATE三者之一。

 新建index.asp文件，在文件首部包含以下代码引用到数据库的连接。

<!--#include file="conn.asp"-->

 使用以下代码创建表单，接收用户输入的表名。

<form action="" method="post">

表名：<input name="tbname" type="text">

<input name="" type="submit" value="提交">

<input name="tbname1" type="hidden" value="<%=tbname%>">

</form>

 当用户提交表单时，获取表名。调用系统存储过程sp_helptrigger获取指定表名的触发器信息。具体代码如下：

<%

If Request.ServerVariables("REQUEST_METHOD")="POST" Then

   Dim strSQL,tbname,rst

   tbname=Trim(Request.Form("tbname"))

   strSQL = "exec sp_helptrigger '" & tbname & "'"

   Set rst=Server.CreateObject("ADODB.Recordset")

   rst.Open strSQL,conn,1,3

End If

%>

 创建表格显示指定表触发器的相关信息。

<%

If Request.ServerVariables("REQUEST_METHOD")="POST" Then

   Response.Write "<tr>"

   For i=0 To rst.Fields.Count-1

      Response.Write "<td>&nbsp;" & rst.Fields(i).Name & "</td>"

   Next

   Response.Write "</tr>"

   Do Until rst.EOF

      Response.Write "<tr>"

      For i=0 To rst.Fields.Count-1

         Response.Write "<td>&nbsp;" & rst.Fields(i).Value & "</td>"

      Next

      Response.Write "</tr>"

      rst.MoveNext

   Loop

   rst.Close

   Set rst = Nothing

   conn.Close

   Set conn = Nothing

End if

%>

本例调用系统存储过程sp_helptrigger显示指定表的触发器信息，本例代码可应用到以下场景：

—    显示指定表的触发器类型。

—    显示指定表的触发器名称。

例0613  查看触发器的定义

本例首先使用与上例相似的代码，显示指定表的触发器，如图6-15所示。单击右侧的“查看”链接，将打开另一个窗口，在该窗口中将显示该触发器的SQL定义语句，如图6-16所示。

图6-15  触发器列表

图6-16  显示触发器的定义语句

使用系统存储过程sp_helptext可以显示规则、默认值、未加密的存储过程、用户定义函数、触发器或视图的文本。

调用该存储过程的语法格式如下：

sp_helptext [ @objname = ] 'name'

其中，参数[@objname =] 'name'，为对象的名称，将显示该对象的定义信息。对象必须在当前数据库中。name 的数据类型为nvarchar(776)，没有默认值。

 新建index.asp文件，使用与上例类似的方法显示指定表的触发器，在表格右侧增加一列显示超链接。增加的单元格代码如下：

Response.Write "<td><a href='showtrig.asp?trigname=" & rst.Fields(0).Value & "'>查看</a></td>"

 新建showtrig.asp文件，用来显示触发器的SQL定义语句。在文件首部包括连接数据库的文件：

<!--#include file="conn.asp"-->

 使用以下代码接收传入的触发器名称，并调用系统存储过程sp_helptext获取触发器的定义内容。

<%

trigname=Request("trigname")

strSQL = "exec sp_helptext '" & trigname & "'"

Set rst=Server.CreateObject("ADODB.Recordset")

rst.Open strSQL,conn,1,3

%>

 使用以下代码循环显示触发器的SQL定义语句。

<%

Do Until rst.EOF

   Response.Write rst(0) & "<br>"

   rst.MoveNext

Loop

%>

注意：数据库在保存触发器定义时，将用户输入的每一行保存为一条记录，上面的代码使用循环将记录集中的内容显示完，才能得到完整的触发器定义代码。

本例通过系统存储过程sp_helptext显示触发器的定义文本，本例代码可应用到以下场景：

—    使用系统存储过程sp_helptext显示触发器的定义文本。

—    使用系统存储过程sp_helptext显示存储过程的定义文本。

例0614  应用触发器防错误删除

在库房管理系统中，如果库存商品数量大于0，则该商品的记录不允许操作。对这种规则，如果在ASP程序中编写代码进行判断，可防止用户通过程序删除记录。但是，如果操作员直接对数据表进行操作，则无法保存该规则；如果使用触发器，则可以很好地解决该问题，并可以简化ASP程序。

本例对“商品”表设置了删除保存的触发器，执行删除操作时，如果不满足条件（一次删除一条记录，且只能删除数量为0的记录），则将返回错误信息，如图6-17所示。

图6-17  用触发器防错误删除

使用触发器时，系统将在内存中维护两张临时表：inserted表和deleted表。它们的具体作用如下：

—    inserted表：在INSERT或UPDATE语句执行过程中，存放插入到触发表中的新数据行的副本。因此，在inserted表的行与触发表中新数据行相同。

—    deleted表：在DELETE或UPDATE语句执行过程中，存放从触发表中删除的旧数据行的副本。deleted表和触发表不会有相同的行。

当执行INSERT操作向触发表中插入数据行时，系统将根据触发表的结构为触发器建立inserted表，并在inserted表中保存所插入的新数据行的副本。同样，在执行DELETE操作时，系统将在deleted表中保存所删除的数据行。执行UPDATE操作时，将在inserted表中存放更新后的新行的值，在deleted表中存放更新前的旧值，相当于执行DELETE操作删除旧行，再使用INSERT操作插入一个新的行。

注意：inserted表和deleted表只能由触发器创建，将存放在内存中。触发器工作完成后，这两个表也将自动删除。用户不能修改这两个表中字段的值，但可引用字段中的值。

 在数据库中创建以下触发器：

CREATE TRIGGER dele_protect

ON 商品

FOR DELETE

AS

  IF @@rowcount>1

   BEGIN

    RAISERROR('每次只允许删除一条记录！',16,10)

    ROLLBACK TRAN

   END

  DECLARE @amount INT

  SELECT @amount=数量 FROM deleted

   IF @amount>0

   BEGIN

    RAISERROR('不能删除本记录，因为其数量不为0！',16,10)

    ROLLBACK TRAN

   END

 新建index.asp文件，编写以下代码用来测试删除保护触发器。程序首先删除“商品”表中所有记录，使用Err对象捕获错误，并显示错误信息。同样，再删除另一条数量不为0的记录，并捕获错误信息。

<%

On Error Resume Next

strSQL="delete from 商品"

Response.Write "执行SQL语句：" & strSQl & "<br>"

Response.Write "删除“商品”表中的所有记录<br>"

conn.Execute(strSQL)

If Err.Number<>0 Then  

   Response.Write Err.Description & "<br>"

Else

   Response.Write "删除完成！<br>" 

End If

strSQL="delete from 商品 where id=1"

Response.Write "<br>执行SQL语句：" & strSQl & "<br>"

Response.Write "删除“商品”表中的一条记录<br>"

conn.Execute(strSQL)

If Err.Number<>0 Then  

   Response.Write Err.Description & "<br>"

Else

   Response.Write "删除完成！<br>" 

End If  

conn.Close

Set conn = Nothing

%>

注意：必须使用On Error Resume Next语句，否则执行到错误处程序将停止。

本例创建了删除触发器，用来保护数据，防止错误删除数据。本例代码可应用到以下场景：

—    在文章系统中，创建删除触发器，防止被误删数据。

—    在库房管理系统中，创建删除触发器，防止被误删数据。

—    在网上商城系统中，创建删除触发器，防止被误删数据。