SQL Server 触发器创建、删除、修改、查看示例

一﹕
触发器是千篇一律种植特有之储存过程﹐它不克于显式地调用﹐而是以往表中插入记录﹑更新记录或去除记录时让活动地激活。所以触发器可以就此来促成对表实施复杂的完整性约`束。

其次﹕ SQL
Server为每个触发器都创了少单专用表﹕Inserted表和Deleted表。这点儿只说明。
 

  一﹕
触发器是千篇一律栽奇特之仓储过程﹐它不能够于显式地调用﹐而是于往表中插入记录﹑更新记录或去除记录时叫电动地激活。所以触发器可以为此来落实对表实施复杂的完整性约`束。
        

  二﹕
SQL
Server为每个触发器都创了有限独专用表﹕Inserted表和Deleted表。这有限个说明由网来维护﹐它们存在让内存中而休是于数据库中。这半独说明的组织总是跟受拖欠触发器作用的发明的构造同样。触发器执行
完成后﹐与拖欠触发器相关的立即片单说明也让去除。
Deleted表存放由于实行Delete或Update语句而设自表中删除的持有执行。
Inserted表存放由于实行Insert或Update语句而使于表中插的装有执行。

  三﹕Instead
of 和 After触发器

SQL
Server2000提供了少种植触发器﹕Instead of 和After 触发器。

立半栽触发器的歧异在他们受激活的同﹕

Instead
of触发器用于代替引起触发器执行的T-SQL语句。除表之外﹐Instead of
触发器也得以用来视图﹐用来扩张视图可以支持之翻新操作。        

After触发器在一个Insert,Update或Deleted语句之后执行﹐进行封锁检查等动作都以After触发器被激活之前发生。After触发器只能用于表。一个表要视图的各级一个改动作(insert,update和delete)都得以发一个instead
of 触发器﹐一个说明的每个修改动作还足以生出差不多只After触发器。

  四﹕触发器的尽过程
如果一个Insert﹑update或者delete语句子违反了束缚﹐那幺After触发器不见面执行﹐因为对约束之自我批评是以After触发器被感动之前发生的。所以After触发器不克跳约束。
      

Instead
of
触发器可以代替激发她的操作来推行。它于Inserted表和Deleted表刚刚成立﹐其它任何操作还尚无出时给实践。因为Instead
of 触发器在封锁之前实施﹐所以它可以针对约束进行部分先处理。        

  
五﹕使用T-SQL语句来创造触发器   基本语句如下﹕                  

create
trigger trigger_name           

on
{table_name | view_name}           

{for |
After | Instead of }            [ insert, update,delete ]
          

as
           sql_statement  

  
六﹕删除触发器:   基本语句子如下﹕

     drop
trigger trigger_name  

  七:查看数据库中都发触发器:
 


查看数据库已经发生触发器

use
jxcSoftware go select * from sysobjects where xtype=’TR’  


查看单个触发器 exec sp_helptext ‘触发器名’

  八﹕修改触发器:
 

基本语句如下﹕
alter trigger trigger_name           

on
{table_name | view_name}           

{for |
After | Instead of }            [ insert, update,delete ]
          

as
           sql_statement

1、触发器实例

Create
Table Student(              –学生表        

      
StudentID int primary key,       –学号        

      
….        )

     
Create Table BorrowRecord(               –学生借书记录表        

      
BorrowRecord   int identity(1,1),       –流水号          

       StudentID     
int ,                    –学号        

      
BorrowDate     datetime,                –借来时间        

      
ReturnDAte     Datetime,                –归还时间        

            …
      )

 用到的机能产生:
1.假如自身改变了生的学号,我期待他的借书记录还和之学生相关(也就是同时改变借书记录表的学号);
       

       
2.如该生早已毕业,我要去他的学号的还要,也抹其的借书记录。
     等等。

    
这时候可以用到触发器。对于1,创建一个Update触发器:

    
Create Trigger truStudent       

  On
Student                         –在Student表中创造触发器       

  for
Update                          –为什么事件触发     

  As                                      –事件触发后所要召开的工作
      

    if
Update(StudentID)                  

  begin

     
Update BorrowRecord           

  Set
StudentID=i.StudentID           

  From
BorrowRecord br , Deleted   d ,Inserted i      –Deleted和Inserted临时表
          

  Where
br.StudentID=d.StudentID

      
end                             

知情触发器里面的少独临时的申:Deleted
, Inserted 。注意Deleted
与Inserted分别代表触发事件的阐明“旧的同条记下”和“新的同样久记下”。一个数据库系统被有半点个虚拟表用于存储于表明中记录转的信,分别是:
                            

          
虚拟表Inserted                     虚拟表Deleted

在表明记录新增时      
存放新增的记录                         不存储记录         

改时          
   存放用来更新的初记录                   存放更新前的记录
        

剔除时          
   不存储记录                             存放于删除的记录

    
一个Update
的历程可扣押作为:生成新的记录到Inserted表,复制旧的笔录及Deleted表,然后去Student记录并形容副新记录。

    
对于2,创建一个Delete触发器     

  Create
trigger trdStudent       

  On
Student       

    for
Delete     

  As
      

  Delete
BorrowRecord         

  From
BorrowRecord br , Delted d         

  Where
br.StudentID=d.StudentID

    
从当下点儿个例子我们可看了触发器的要:A.2只临时之申;B.触发机制。

SQL触发器实例2

USE
Master

GO

IF EXISTS
(SELECT NAME FROM SYSOBJECTS WHERE XTYPE = ‘U’ AND NAME =
‘卷烟库存表’)

 

DROP
TABLE 卷烟库存表

GO

IF EXISTS
(SELECT NAME FROM SYSOBJECTS WHERE XTYPE = ‘U’ AND NAME =
‘卷烟销售表’)

DROP
TABLE 卷烟销售表

GO

–业务规则:销售金额
= 销售数据 * 销售单价 业务规则。

CREATE
TABLE 卷烟销售表

(
卷烟品牌 VARCHAR(40) PRIMARY KEY NOT NULL,

  购货商
VARCHAR(40) NULL,

 
销售数据 INT NULL,

 
销售单价 MONEY NULL,

 
销售金额 MONEY NULL

)

GO

–业务规则:库存金额
= 库存数量 * 库存单价 业务规则。

CREATE
TABLE 卷烟库存表

(
卷烟品牌 VARCHAR(40) PRIMARY KEY NOT NULL,

 
库存数据 INT NULL,

 
库存单价 MONEY NULL,

 
库存金额 MONEY NULL

)

GO

–创建触发器,示例1

/*
创建触发器[T_INSERT_卷烟库存表],这个触发器较简单。

说明:
每当[卷烟库存表]生 INSERT 动作,则抓住拖欠触发器。

触发器功能:
强制执行业务规则,保证插入的数目中,库存金额 = 库存数据 *
库存单价。

注意:
[INSERTED]、[DELETED]为系统表,不可创建、修改、删除,但足以调用。

重点:
这片个系统表的布局以及插入数据的阐发的结构。 */

IF EXISTS
(SELECT NAME FROM SYSOBJECTS WHERE XTYPE = ‘TR’ AND NAME =
‘T_INSERT_卷烟库存表’)

DROP
TRIGGER T_INSERT_卷烟库存表

GO

CREATE
TRIGGER T_INSERT_卷烟库存表

ON
卷烟库存表

FOR
INSERT

AS

–提交事务处理

BEGIN
TRANSACTION

–强制执行下列语句,保证工作规则

UPDATE
卷烟库存表

SET
库存金额 = 库存数量 * 库存单价

WHERE
卷烟品牌 IN (SELECT 卷烟品牌 from INSERTED)

COMMIT
TRANSACTION

GO

/*
针对[卷烟库存表],插入测试数据:

瞩目,第一久数(红塔山新势力)中之数码符合业务规则,

其次长长的数(红塔山人为峰)中,[库存金额]空,不吻合业务规则,

老三漫漫数(云南映像)中,[库存金额]不等于[库存数据]乘以[库存单价],不相符业务规则。

季长达数库存数量为0。

伸手小心在插入数据后,检查[卷烟库存表]遭逢之数据是否
库存金额 = 库存数量 * 库存单价。 */

INSERT
INTO 卷烟库存表(卷烟品牌,库存数据,库存单价,库存金额)

SELECT
‘红塔山初势力’,100,12,1200 UNION ALL

SELECT
‘红塔山人造峰’,100,22,NULL UNION ALL

SELECT
‘云南映像’,100,60,500 UNION ALL

SELECT
‘玉溪’,0,30,0 GO

–查询数据

SELECT *
FROM 卷烟库存表

GO

/*

结果集

RecordId
卷烟品牌 库存数量 库存单价 库存金额



1
红塔山新势力 100 12.0000 1200.0000

2
红塔山人为峰 100 22.0000 2200.0000

3
云南映像 100 60.0000 6000.0000

4 玉溪 0
30.0000 .0000

(所影响的行数为
4 行)

*/

–触发器示例2

/*
创建触发器[T_INSERT_雪茄销售表],该触发器较复杂。

说明:
每当[卷烟库存表]生 INSERT 动作,则抓住拖欠触发器。 触发器功能:
实现工作规则。

事务规则:
如果销售的雪茄品牌不在库存或库存为零星,则归错误。

要不则自动减[卷烟库存表]遇针对承诺品牌卷烟的库存数据与库存金额。
*/

IF EXISTS
(SELECT NAME FROM SYSOBJECTS WHERE XTYPE = ‘TR’ AND NAME =
‘T_INSERT_雪茄销售表’)

DROP
TRIGGER T_INSERT_卷烟销售表

GO

CREATE
TRIGGER T_INSERT_卷烟销售表

ON
卷烟销售表

FOR
INSERT

AS

BEGIN
TRANSACTION

–检查数据的合法性:销售的卷烟是否有库存,或者库存是否超越零

IF NOT
EXISTS (

SELECT
库存数据

FROM
卷烟库存表

WHERE
卷烟品牌 IN (SELECT 卷烟品牌 FROM INSERTED) )

BEGIN

–返回错误提示
RAISERROR(‘错误!该卷烟不在库存,不可知销售。’,16,1)

–回滚事务

ROLLBACK

RETURN

END

IF EXISTS
( SELECT 库存数量 FROM 卷烟库存表 WHERE 卷烟品牌 IN (SELECT 卷烟品牌
FROM INSERTED) AND 库存数量 <= 0 ) BEGIN –返回错误提示
RAISERROR(‘错误!该卷烟库存小于等于0,不克销售。’,16,1) –回滚事务
ROLLBACK RETURN END

–对官的数码开展处理

–强制执行下列语句,保证工作规则

UPDATE
卷烟销售表

SET
销售金额 = 销售数额 * 销售单价

WHERE
卷烟品牌

IN
(SELECT 卷烟品牌 FROM INSERTED)

DECLARE
@卷烟品牌 VARCHAR(40)

SET
@卷烟品牌 = (SELECT 卷烟品牌 FROM INSERTED)

 

DECLARE
@销售数量 MONEY

SET
@销售数目 = (SELECT 销售数额 FROM INSERTED)

 

UPDATE
卷烟库存表

SET
库存数据 = 库存数量 – @销售数量, 库存金额 = (库存数据 –
@销售数额)*库存单价

WHERE
卷烟品牌 = @卷烟品牌

COMMIT
TRANSACTION

GO

–请大家自行跟踪[卷烟库存表]和[卷烟销售表]的多少变化。

–针对[雪茄销售表],插入第一长测试数据,该数量是常规的。

INSERT
INTO 卷烟销售表(卷烟品牌,购货商,销售数额,销售单价,销售金额)

SELECT
‘红塔山新势力’,’某购货商’,10,12,1200

GO

–针对[雪茄销售表],插入第二长测试数据,该数额
销售金额 不抵 销售单价 * 销售数量。

–触发器将自动还凑巧数据,使
销售金额 等于 销售单价 * 销售数量。

INSERT
INTO 卷烟销售表(卷烟品牌,购货商,销售数据,销售单价,销售金额)

SELECT
‘红塔山人工峰’,’某购货商’,10,22,2000

GO

–针对[雪茄销售表],插入第三修测试数据,该数量中之卷烟品牌于
卷烟库存表中寻觅不交相应。

–触发器将报错。

INSERT
INTO 卷烟销售表(卷烟品牌,购货商,销售数目,销售单价,销售金额)

SELECT
‘红河V8′,’某购货商’,10,60,600

GO

/* 消息
50000,级别 16,状态 1,过程 T_INSERT_雪茄销售表,第 15 行
错误!

该卷烟不存库存,不可知销售。
消息 3609,级别 16,状态 1,第 1 行 事务在触发器中收。批处理就暂停。
*/

–针对[雪茄销售表],插入第三长测试数据,该数量中的卷烟品牌于
卷烟库存表中库存为0。

–触发器将报错。

INSERT
INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额)

SELECT
‘玉溪’,’某购货商’,10,30,300

GO

/* 消息
50000,级别 16,状态 1,过程 T_INSERT_雪茄销售表,第 29 行
错误!该卷烟库存小于等于0,不可知销售。 消息 3609,级别 16,状态 1,第 1
行 事务在触发器中结束。批处理就暂停。 */

–查询数据

SELECT *
FROM 卷烟库存表

SELECT *
FROM 卷烟销售表

GO

补充: 
1、本示例主要通过一个简的事务规则实现来开展触发器使用的证实,具体的若因需要灵活处理; 
2、关于触发器要理解并采取好
INSERTED ,DELETED 两独系统表; 
3、本示例创建的触发器都是
FOR INSERT ,具体的语法可参考:

Trigger语法 

CREATE
TRIGGER trigger_name 
ON {
table | view } 
[ WITH
ENCRYPTION ] –用于加密触发器 

{ { FOR |
AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ]  } 
[ 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 ] 

4、关于触发器,还应有专注 
(1)、DELETE
触发器不能够捕获 TRUNCATE TABLE 语句子。 
(2)、触发器中莫允以下
Transact-SQL 语句: 
ALTER
DATABASE CREATE DATABASE DISK INIT 
DISK
RESIZE DROP DATABASE LOAD DATABASE 
LOAD LOG
RECONFIGURE RESTORE  DATABASE 
RESTORE
LOG 
(3)、触发器最多好嵌套
32 层。 

*/ 

–修改触发器 
–实质上,是将
CREATE TRIGGER … 修改为 ALTER TRIGGER …即可。 

–删除触发器 
DROP 
TRIGGER xxx 
GO 

–删除测试环境 
DROP
TABLE 卷烟库存表 
GO 
DROP
TABLE 卷烟销售表 
GO 
DROP
TRIGGER T_INSERT_卷烟库存表 
GO 
DROP
TRIGGER  T_INSERT_雪茄销售表 
GO 
 
触发器的基础知识和例子 
:create
trigger tr_name 
on
table/view 
{for |
after |  instead of } [update][,][insert][,][delete] 
[with
encryption] 
as
{batch  | if update (col_name) [{and|or} update (col_name)]

说明: 
1
tr_name :触发器名称 
2 on
table/view :触发器所打算的表。一个触发器只能作用被一个表 
3 for
和after :同义 
4  after
与instead of :sql 2000初添项目afrer 与 instead of 的区分 
After 
每当接触事件产生后才让激活,只可起在表上 
Instead
of 
替了相应的点事件如果给实践,既可建立在表上也可以建立于视图上 

insert、update、delete:激活触发器的老三种植操作,可以又施行,也不过摘其平 
6 if
update 
(col_name):表明所作的操作对指定列是否生影响,有影响,则激活触发器。此外,因为delete
操作才对行有震慑, 
就此只要采取delete操作就无能够为此当下漫漫告句了(虽然用与否未失误,但是未可知激活触发器,没意义)。 
7
触发器执行时用到之片个奇特说明:deleted  ,inserted 
deleted
和inserted
可以说凡是相同栽特别的临时表,是以进行激活触发器时由于网自动生成的,其组织和触发器作用的说明结构是同一 
种的,只是存放
的数据来距离。 

续 
脚表格说明deleted
与inserted 数据的差异 
deleted
与inserted 数据的差异 
Inserted 
寄存进行insert和update
操作后底数据 
Deleted 
存放进行delete
和update操作前之数据 
在意:update
操作相当给事先进行delete 再进行insert 
,所以于拓展update操作时,修改前的数目拷贝一长达到deleted
表中,修改后 
的数额在存到触发器作用的申底以,也以充分成一漫长拷贝到insered表中

 

CREATE TRIGGER [TRIGGER admixture_receive_log] ON dbo.chl_lydj

 

FOR UPDATE

 

AS

 

begin

 

        declare @djsfxg char(10)      declare @wtbh char(20)

 

        select @wtbh=wtbh from inserted

 

       update ly_tzk set djsfxg=’已修改’ where wtbh=@wtbh

 

end

 

if (select data_sfjl from t_logsetup)=’是’

 

begin

 

declare @oldcjmc char (100)          declare @oldlyrq datetime

 

declare @oldbzbh char (60)             declare @oldzl char (20)

 

declare @olddj char (10)

 

declare @newcjmc char (100)          declare @newlyrq datetime

 

declare @newbzbh char (60)             declare @newzl char (20)

 

declare @newdj char (10)

 

             declare @xgr char (20)

 

             select
@oldcjmc=cjmc,@oldlyrq=lyrq,@oldbzbh=bzbh,@oldzl=zl,@olddj=dj from
deleted

 

             select
@newcjmc=cjmc,@newlyrq=lyrq,@newbzbh=bzbh,@newzl=zl,@newdj=dj from
inserted

 

             select @xgr=xgr from t_modifyuser where @wtbh=wtbh

 

           

 

         if @oldcjmc<>@newcjmc

 

             begin

 

                      insert into t_modifylog (wtbh, mod_time,
mod_table, mod_field, ori_value, now_value, mod_people) values

 

                      (@wtbh,getdate(), ‘chl_lydj’,’cjmc’, @oldcjmc,
@newcjmc, @xgr)

 

             end

 

 

 

end

 

//////////修改时,直接把‘create’改为‘alter’即可

 

/////////////////////////

 

CREATE TRIGGER [TRIGGER ly_tzk_syf] ON dbo.ly_tzk

 

FOR insert

 

AS

 

begin

 

         declare @clmc char(100)     declare @dwbh char(100) declare
@syf char(100)   declare @dwgcbh char(100) declare @wtbh char(50)

 

         declare @dj_1 money     declare @feiyong_z money   declare
@feiyong_xf money   declare @feiyong_sy money

 

         declare @dj char(20)

 

        select @wtbh=wtbh , @clmc=clmc , @dwbh=dwbh ,@syf=syf from
inserted

 

        select   @dj=dj from feihao_bz where clmc=@clmc

 

        select @feiyong_z=feiyong_z, @feiyong_xf=feiyong_xf,
@feiyong_sy=feiyong_sy from gongchengxinxi wheredwgcbh=@dwbh

 

        set @dj_1=convert(money ,@dj)

 

       if @dj_1 <>0

 

       begin

 

             set @feiyong_xf=@feiyong_xf+@dj_1

 

             set @feiyong_sy=@feiyong_sy-@dj_1

 

      

 

            update ly_tzk set syf=@dj where wtbh=@wtbh    

 

            update gongchengxinxi set feiyong_xf=@feiyong_xf,
feiyong_sy=@feiyong_sy where dwgcbh=@dwbh

 

       end

 

       else    update ly_tzk set syf=convert(char , 0.0) where
wtbh=@wtbh

 

end

 

//////////////////////

 

CREATE TRIGGER [TRIGGER ly_tzk_syf_shanchu] ON dbo.ly_tzk

 

FOR delete

 

AS

 

begin

 

         declare @clmc char(100)     declare @dwbh char(100)    declare
@dwgcbh char(100) declare @wtbh char(50)

 

         declare @feiyong_z money    declare @feiyong_xf money  
declare @feiyong_sy money

 

         declare @syf char(100)      declare @syf_1 money

 

         –declare @dj char(20)   declare @dj_1 money

 

        select @wtbh=wtbh , @clmc=clmc , @dwbh=dwbh ,@syf=syf from
inserted

 

        –select   @dj=dj from feihao_bz where clmc=@clmc

 

        select @feiyong_z=feiyong_z, @feiyong_xf=feiyong_xf,
@feiyong_sy=feiyong_sy from gongchengxinxi wheredwgcbh=@dwbh

 

        set @syf_1=convert(money ,@syf)

 

       if @syf_1 <>0

 

       begin

 

             set @feiyong_xf=@feiyong_xf-@syf_1

 

             set @feiyong_sy=@feiyong_sy+@syf_1

 

            update gongchengxinxi set feiyong_xf=@feiyong_xf,
feiyong_sy=@feiyong_sy where dwgcbh=@dwbh

 

       end

 

end

 

相关文章