sql大全二

一:SELECT INTO和 INSERT INTO SELECT两种表复制语句

1.INSERT INTO SELECT语句

      语句形式为：Insert into Table2(field1,field2,...) select value1,value2,... from Table1

      要求目标表Table2必须存在，由于目标表Table2已经存在，所以我们除了插入源表Table1的字段外，还可以插入常量。示例如下：

INSERT INTO SELECT语句复制表数据
   --1.创建测试表
    create TABLE Table1
    (
        a varchar(10),
        b varchar(10),
        c varchar(10),
        CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
        (
            a ASC
        )
    ) ON [PRIMARY]

    create TABLE Table2
    (
        a varchar(10),
        c varchar(10),
        d int,
        CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
        (
            a ASC
        )
    ) ON [PRIMARY]
    GO
    --2.创建测试数据
    Insert into Table1 values('赵','asds','90')
    Insert into Table1 values('钱','asds','100')
    Insert into Table1 values('孙','asds','80')
    Insert into Table1 values('李','asds',null)
    GO
    select * from Table2

    --3.INSERT INTO SELECT语句复制表数据
    Insert into Table2(a, c, d) select a,c,5 from Table1
    GO

    --4.显示更新后的结果
    select * from Table2
    GO
    --5.删除测试表
    drop TABLE Table1
    drop TABLE Table2

      2.SELECT INTO FROM语句

      语句形式为：SELECT vale1, value2 into Table2 from Table1

      要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中。示例如下：

onclick="this.style.display='none'; document.getElementById('Code_Open_Text_082138').style.display='none'; getElementById('Code_Closed_Image_082138').style.display='inline'; getElementById('Code_Closed_Text_082138').style.display='inline';" align=top v:shapes="Code_Open_Image_082138">SELECT INTO FROM创建表并复制表数据
   --1.创建测试表
    create TABLE Table1
    (
        a varchar(10),
        b varchar(10),
        c varchar(10),
        CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
        (
            a ASC
        )
    ) ON [PRIMARY]
    GO

    --2.创建测试数据
    Insert into Table1 values('赵','asds','90')
    Insert into Table1 values('钱','asds','100')
    Insert into Table1 values('孙','asds','80')
    Insert into Table1 values('李','asds',null)
    GO

    --3.SELECT INTO FROM语句创建表Table2并复制数据
    select a,c INTO Table2 from Table1
    GO

    --4.显示更新后的结果
    select * from Table2
    GO
    --5.删除测试表
    drop TABLE Table1
    drop TABLE Table2

 

二:SQL时间函数整理

一、sql server日期时间函数
Sql Server中的日期与时间函数 
1.  当前系统日期、时间 
    select getdate()  

2. dateadd  在向指定日期加上一段时间的基础上，返回新的 datetime 值
   例如：向日期加上2天 
   select dateadd(day,2,'2004-10-15')  --返回：2004-10-17 00:00:00.000 

3. datediff 返回跨两个指定日期的日期和时间边界数。
   select datediff(day,'2004-09-01','2004-09-18')   --返回：17

4. datepart 返回代表指定日期的指定日期部分的整数。
  select DATEPART(month, '2004-10-15')  --返回 10

5. datename 返回代表指定日期的指定日期部分的字符串
   select datename(weekday, '2004-10-15')  --返回：星期五

6. day(), month(),year() --可以与datepart对照一下

select 当前日期=convert(varchar(10),getdate(),120) 
,当前时间=convert(varchar(8),getdate(),114) 

select datename(dw,'2004-10-15') 

select 本年第多少周=datename(week,'2004-10-15')
      ,今天是周几=datename(weekday,'2004-10-15')

二、日期格式转换
    select CONVERT(varchar, getdate(), 120 )
 2004-09-12 11:06:08 
 select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','')
 20040912110608
 
 select CONVERT(varchar(12) , getdate(), 111 )
 2004/09/12
 
 select CONVERT(varchar(12) , getdate(), 112 )
 20040912

 select CONVERT(varchar(12) , getdate(), 102 )
 2004.09.12
 
 其它我不常用的日期格式转换方法:

 select CONVERT(varchar(12) , getdate(), 101 )
 09/12/2004

 select CONVERT(varchar(12) , getdate(), 103 )
 12/09/2004

 select CONVERT(varchar(12) , getdate(), 104 )
 12.09.2004

 select CONVERT(varchar(12) , getdate(), 105 )
 12-09-2004

 select CONVERT(varchar(12) , getdate(), 106 )
 12 09 2004

 select CONVERT(varchar(12) , getdate(), 107 )
 09 12, 2004

 select CONVERT(varchar(12) , getdate(), 108 )
 11:06:08
 
 select CONVERT(varchar(12) , getdate(), 109 )
 09 12 2004 1

 select CONVERT(varchar(12) , getdate(), 110 )
 09-12-2004

 select CONVERT(varchar(12) , getdate(), 113 )
 12 09 2004 1

 select CONVERT(varchar(12) , getdate(), 114 )
 11:06:08.177

举例：
1.GetDate() 用于sql server :select GetDate()

2.DateDiff('s','2005-07-20','2005-7-25 22:56:32')返回值为 514592 秒
DateDiff('d','2005-07-20','2005-7-25 22:56:32')返回值为 5 天

3.DatePart('w','2005-7-25 22:56:32')返回值为 2 即星期一(周日为1，周六为7)
DatePart('d','2005-7-25 22:56:32')返回值为 25即25号
DatePart('y','2005-7-25 22:56:32')返回值为 206即这一年中第206天
DatePart('yyyy','2005-7-25 22:56:32')返回值为 2005即2005年
附图

函数

参数/功能

GetDate( )

返回系统目前的日期与时间

DateDiff (interval,date1,date2)

以interval 指定的方式，返回date2 与date1两个日期之间的差值 date2-date1

DateAdd (interval,number,date)

以interval指定的方式，加上number之后的日期

DatePart (interval,date)

返回日期date中，interval指定部分所对应的整数值

DateName (interval,date)

返回日期date中，interval指定部分所对应的字符串名称

参数 interval的设定值如下：

值

缩 写（Sql Server）

说明

Year

Yy

年 1753 ~ 9999

Quarter

Qq

季 1 ~ 4

Month

Mm

月1 ~ 12

Day of year

Dy

一年的日数,一年中的第几日 1-366

Day

Dd

日，1-31

Weekday

Dw

一周的日数，一周中的第几日 1-7

Week

Wk

周，一年中的第几周 0 ~ 51

Hour

Hh

时0 ~ 23

Minute

Mi

分钟0 ~ 59

Second

Ss

秒 0 ~ 59

Millisecond

Ms

毫秒 0 ~ 999

 

三：字符串函数整理

1、ASCLL(character),将具体字符转换为相应的整数(ASCII)代码，结果为正数。

      例：select  ASCII('A')，结果为65

 

      2、CHAR(int)，将ASCII代码转换为相应的字符，结果为CHAR(1).

      例：select CHAR(65)，结果为A

 

      3、CHARINDEX(str1,str2),返回部分字符串str1在字符串str2中首次出现的起始位置。如果str1没有在str2中出现，那么返回值为0。

      例：select CHARINDEX('world','hello world!'); 结果为7

       select CHARINDEX('z','hello world!'); 结果为0

 

      4、DIFFERENCE(str1,str2)，返回值为0-4（发音越相似越大，4为相同发音）之间的整数，这就是str1和str2这两个字符串 SOUNDEX之间的区别。SOUNDEX返回的数字指定的是字符串的语音。这种方法能够判断有相同发音的字符串。

      例：select DIFFERENCE('spelling','telling'); 结果为2

       select DIFFERENCE('hello','hello');结果为4

 

      5、LEFT(str,length)，截取str字符串length长度的字符串，当length大于str字符串的长度时，返回str。

      例：select LEFT('hello',3); 结果为'hel’

       select LEFT('hello',10);结果为'hello’

 

      6、LEN(str)，放回指定的字符串表达式的字符个数，包括后面的空格。

      例：select LEN('hello world! '); 结果为12

 

      7、LOWER(str),将字符串str中所有的大写字母转换成小写字母。

      例：select  LOWER('Hello'); 结果为为'hello’

 

      8、LTRIM(str)，去掉字符串str开头的空格。

      例：select LTRIM('  Hello World  '); 结果为’Hello World  ’

 

      9、NCHAR(i),返回由统一码标准定义的、有指定整数代码的统一码字符。

      例：select NCHAR(65);  结果为’A’

      10、QUOTENAME(char_string),返回有分隔符的统一码字符串，使输入字符串编程有效分隔符。

 

      例：select QUOTENAME('-'); 结果为‘[-]’

 

      11、PATINDEX(%p%,expr)，返回指定表达式expr中模式p第一次出现的起始位置，如果没有找到匹配模式的，就返回0。

      例：select PATINDEX('%e%','hello'); 结果为2

 

      12、REPLACE(str1,str2,str3)，将所有str1中出现的str2替换为str3。

      例：select REPLACE('hello','l','a'); 结果为‘heaao’

 

      13、REPLICATE(str,times)，将字符串str重复times次。

      例：select REPLICATE('a',5)。结果为‘aaaaa’

 

      14、REVERSE(str)，将字符串str显示为倒序。

      例：select REVERSE('hello')，结果为‘olleh’

 

      15、RIGHT(str,length)，在字符串str中截取最后length长度的字符。

      例：select RIGHT('hello world',5)，结果为‘world’

 

      16、RTRIM(str)，取消字符串str最后的空格。

      例：select RTRIM('  Hello World   ')，结果为‘Hello World’

 

      17、SOUNDEX(word)，返回四个字符的SOUNDEX代码判断字符中的相似性。

      例：select SOUNDEX('spelling'); 结果为‘S315’

 

      18、SPACE(length)，返回一个字符串，length为其指定的空间长度。

      例：select SPACE(3)； 结果为‘   ’

 

      19、STR(f,[len [,d]]，将指定的float表达式f转换为字符串。len是指定字符串的长度，包括小数点、正负号、数字和空格(默认值为10)，d为小数点右边的被返回的数字。在转换过程中会使用四舍五入法。

      例：select STR(123.45678,5,2); 结果为123,5

 

      20、STUFF(str1,a,lenth,str2)，用字符串str2中位于a处的部分字符串代替str1中的部分字符串，代替str1中length字符。

      例：select STUFF('Hello World',6,1,' wonderful ')，结果为‘Hello wonderful World’

 

      21、SUBSTRING(str,index,length)。在字符串str中的index处开始截取length长度的字符串。

      例：select SUBSTRING('Hello World',7,6)，结果为‘World’

 

      22、UNICODE，放回由同一定义的整数值，该值为数值表达式的第一个字符。

      例：select UNICODE('Hello World')，结果为72，即‘H’的ASCII码

 

      23、UPPER(str)，将字符串str中的所有字母转换为大写字母。

      例：select UPPER('Hello')，结果为‘HELLO’

 

四：数学函数整理

1.ABS（数值型表达式）--求绝对值

select abs(1)

结果：1

select abs(-1)

结果：1

2.ASCII（字符型表达式）--求ASCII

select ascii('1')

结果：49

select ascii('a')

结果：97

3.AVG([ALL | DISTINCT] 表达式)--求平均值

ALL默认

4.COUNT([ALL | DISTINCT] 表达式 | *)--计数

ALL默认

5.SIGN(数值表达式)--判断正负0

select sign(2)

结果：1

select sign(-2)

结果：-1

select sign(0)

结果：0

6.RAND(整型表达式)--产生随机数(0-1之间)

declare @a int

set @a = 1

select rand(@a)

7.CEILING(数值表达式)--返回最小的“大于或等于”给定数值型表达式的整数值。

select ceiling(2)

结果：2

select ceiling(2.2)

结果：3

select ceiling(-2.2)

结果：-2

select ceiling(-2)

结果：-2

8.FLOOR(数值表达式)--返回最大的“小于或等于”给定数值型表达式的整数值。

select floor(2)

结果：2

select floor(2.2)

结果：2

select floor(-2.2)

结果：-3

select floor(-2)

结果：-2

9.ROUND(数值表达式，整数)--四舍五入

正数，从小数点往后数+1

负数，从小数点往前数

0，常规四舍五入

select round(888.88, -1), round(888.88, 1), round(888.88, 0)

结果：890.00,  888.9, 889.00

五：触发器

{{

 

       

 一、创建一个简单的触发器

触发器是一种特殊的存储过程，类似于事件函数，SQL Server™允许为 INSERT、UPDATE、DELETE创建触发器，即当在表中插入、更新、删除记录时，触发一个或一系列 T-SQL语句。

触发器可以在查询分析器里创建，也可以在表名上点右键->“所有任务”->“管理触发器”来创建，不过都是要写 T-SQL 语句的，只是在查询分析器里要先确定当前操作的数据库。

创建触发器用CREATE TRIGGER

CREATE TRIGGER 触发器名称
ON 表名
FOR INSERT、UPDATE或 DELETE
AS
    T-SQL 语句
注意：触发器名称是不加引号的。

如下是联机丛书上的一个示例，当在 titles表上更改记录时，发送邮件通知 MaryM。
CREATE TRIGGER reminder
ON titles
FOR INSERT, UPDATE, DELETE
AS
   EXEC master..xp_sendmail 'MaryM',
      'Don''t forget to print a report for the distributors.'

二、删除触发器

用查询分析器删除
在查询分析器中使用 drop trigger 触发器名称来删除触发器。
也可以同时删除多个触发器：drop trigger触发器名称,触发器名称...
注意：触发器名称是不加引号的。在删除触发器之前可以先看一下触发器是否存在：
if Exists(select name from sysobjects where name=触发器名称 and xtype='TR')

用企业管理器删除
在企业管理器中，在表上点右键->“所有任务”->“管理触发器”，选中所要删除的触发器，然后点击“删除”。

三、重命名触发器

用查询分析器重命名
exec sp_rename 原名称, 新名称
sp_rename是 SQL Server™自带的一个存储过程，用于更改当前数据库中用户创建的对象的名称，如表名、列表、索引名等。

用企业管理器重命名
在表上点右键->“所有任务”->“管理触发器”，选中所要重命名的触发器，修改触发器语句中的触发器名称，点击“确定”。

四、more....

INSTEAD OF
执行触发器语句，但不执行触发触发器的 SQL语句，比如试图删除一条记录时，将执行触发器指定的语句，此时不再执行 delete语句。例：
create trigger f
on tbl
instead of delete
as
    insert into Logs...

IF UPDATE(列名)
检查是否更新了某一列，用于 insert或 update，不能用于 delete。例：
create trigger f
on tbl
for update
as
    if update(status) or update(title)
        sql_statement --更新了 status或 title列

inserted、deleted
这是两个虚拟表，inserted保存的是 insert或 update 之后所影响的记录形成的表，deleted保存的是 delete或 update 之前所影响的记录形成的表。例：
create trigger tbl_delete
on tbl
for delete
as
    declare @title varchar(200)
    select @title=title from deleted
    insert into Logs(logContent) values('删除了 title为：' + title + '的记录')
说明：如果向 inserted或 deleted虚拟表中取字段类型为 text、image的字段值时，所取得的值将会是 null。

五、查看数据库中所有的触发器

在查询分析器中运行：

use数据库名
go
select*fromsysobjectswhere xtype='TR'

sysobjects保存着数据库的对象，其中 xtype为 TR的记录即为触发器对象。在 name一列，我们可以看到触发器名称。

六、sp_helptext查看触发器内容

用查询分析器查看

use数据库名
go
execsp_helptext'触发器名称'

将会以表的样式显示触发器内容。
除了触发器外，sp_helptext还可以显示规则、默认值、未加密的存储过程、用户定义函数、视图的文本

用企业管理器查看

在表上点右键->“所有任务”->“管理触发器”，选择所要查看的触发器存储过程

七、sp_helptrigger用于查看触发器的属性

sp_helptrigger有两个参数：第一个参数为表名；第二个为触发器类型，为 char(6)类型，可以是 INSERT、UPDATE、DELETE，如果省略则显示指定表中所有类型触发器的属性。

例：

use数据库名
go
execsp_helptrigger tbl

 

八、递归、嵌套触发器

递归分两种，间接递归和直接递归。我们举例解释如下，假如有表1、表2名称分别为 T1、T2，在 T1、T2上分别有触发器 G1、G2。

·             间接递归：对 T1操作从而触发 G1，G1对 T2 操作从而触发 G2，G2对 T1操作从而再次触发 G1...

·             直接递归：对 T1操作从而触发 G1，G1对 T1 操作从而再次触发 G1...

嵌套触发器

类似于间接递归，间接递归必然要形成一个环，而嵌套触发器不一定要形成一个环，它可以 T1->T2->T3...这样一直触发下去，最多允许嵌套 32层。

设置直接递归

默认情况下是禁止直接递归的，要设置为允许有两种方法：

·             T-SQL：exec sp_dboption 'dbName', 'recursive triggers', true

·             EM：数据库上点右键->属性->选项。

设置间接递归、嵌套

默认情况下是允许间接递归、嵌套的，要设置为禁止有两种方法：

·             T-SQL：exec sp_configure 'nested triggers', 0 --第二个参数为 1 则为允许

·             EM：注册上点右键->属性->服务器设置。

 

九、触发器回滚

我们看到许多注册系统在注册后都不能更改用户名，但这多半是由应用程序决定的，如果直接打开数据库表进行更改，同样可以更改其用户名，在触发器中利用回滚就可以巧妙地实现无法更改用户名。

use数据库名
go
create trigger tr
on表名
for update
as
    if update(userName)
        rollback tran

关键在最后两句，其解释为：如果更新了 userName列，就回滚事务。

十、禁用、启用触发器

禁用：alter table表名 disable trigger触发器名称
启用：alter table表名 enable trigger触发器名称

如果有多个触发器，则各个触发器名称之间用英文逗号隔开。

如果把“触发器名称”换成“ALL”，则表示禁用或启用该表的全部触发器。

 

 

 

SQL触发器实例讲解1

 

SQL触发器实例1

定义：何为触发器？在SQL Server里面也就是对某一个表的一定的操作，触发某种条件，从而执行的一段程序。触发器是一个特殊的存储过程。
      常见的触发器有三种：分别应用于Insert , Update , Delete事件。

      我为什么要使用触发器？比如，这么两个表：

      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
错误！该卷烟不存在库存，不能销售。
*/

--针对[卷烟销售表]，插入第三条测试数据，该数据中的卷烟品牌在卷烟库存表中库存为0。
--触发器将报错。

INSERT INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额)
SELECT ’玉溪’,’某购货商’,10,30,300
GO

/*
结果集
服务器: 消息 50000，级别 16，状态 1，过程 T_INSERT_卷烟销售表，行 29
错误！该卷烟库存小于等于0，不能销售。
*/
--查询数据
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
代替了相应的触发事件而被执行,既可以建立在表上也可以建立在视图上
5 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表中

 

 

}}