一: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中。示例如下:
οnclick="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 | | 季 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 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表中
}}