-
实用标准文案
初学
sql server
2008
之触发器
触发器
(trigger)
:
是一种特殊的存储过程,可以用来对表实施复杂的完整性约束,保
p>
持数据的一致性。
当触发器所保护的数据发生改变时,
触发器会自动被激活,
并执行触发器
中所定义的相关
操作,从而保证对数据的不完整性约束或不正确的修改
。
在
SQL
SERVER 2008
中,有三种类型的触发器:
(1)DML
触发器:是指触发器在数据库中发生数据操作语言
(DML)
事件时将启用。
DML
事件即
指在表或视图中修改数据的
insert
p>
、
update
、
delete
语句也。
(2)DDL
触发器:是指当服务器或数据库中发生数据定义语言
(DDL)
事件时将启用。
DDL
事件
即指在表或索引中的
create
、
alter
、
drop
语句
也。
(3)
登陆触发器:是指当用户
登录
SQL
SERVER
实例建立会话时触发。
不过根据
DML
触发器触发的方式不同又分为以下两种情况:
(
1
)
AFTER
触发器
:它是在执行
INSERT
、
UPDA
TE
、
DELETE
语句操作之后执行
触发器操作。
它主要是用于记录变更后的处理或检查,一旦发生错误,可以用
Rollback Transaction
语
句
来回滚本次扣件,
不过不能对视图定义
AFTER
触发器
。
(
2
)
INSTEAD OF
触发器:它在执行
INSERT
、
UP
DATE
、
DELETE
语句操作之前
执行触发器本
身所定义的操作。
而
IN
STEAD
OF
触发器是可以定义在视图上的
。
在
SQL
SERVER
2008
中,
DML
触发
器的实现使用两个逻辑表
DELETED
和
INSERTED
。这
两个表是建立在数据库服务器的内存
中,
由系统管理的逻辑表,
我们对于它只有只读的权限。
DELETED
和
INSERED
表的结构和触发器所在的数据表的结构是一样的。
当触发器执行完成后,<
/p>
它们也就会被自动删除。
INSERE
D
表用于存放你在操件
insert
、
update
、
delete
语句后,更新的记录。比如你插入
一条数据,那么就会把这条记录插入到
INSERTED
表。
DELETED
表用于存放你在操作
insert
、
update
、
delete
语句前,
你创建触发器表中数据库。
比如你原来的表中有三条数据,那么他也有三条数据。
p>
下面我们就开始创建触发器吧
!
触发器定义的格式我就不打一遍,你打开帮助里有的事。
我们先创建一张如下的表吧!然后以它为例吧。
create
table
table1
(
user_id
int
primary
key
identity
(
1
,
1
),
user_name
varchar
(
3
)
not
null
)
下面就是创建一个简单的
after
触发器:
精彩文档
实用标准文案
create
trigger
trigMessageList
on
table1
after
insert
,
update
as
raise
rror
(
'
数据一致性验证
'
,
16
,
1
)
此时如果我们手动打开
table1
,输入一条,根本就插入不了,我们只能写一条如下的
SQL
语句插入一条数据
insert
into
table1(
user_
name
)
values
(
'23'
)
结果数据是插入成功了,但是会出显如下图所示的提示:
下面我再创建一个稍微有点复杂的
a
fter
触发器:
--
该触发器作用是:如果向
table1
中插入数据时,
--
先
检查要向
table1
插入的这条的
U
SER_ID
是否
USERS(
我创建
的另一个表
)
表
中的
< br>USER_ID
字段中有,如果不存在则不允许插入
create
trigger
trigInsertedMessages
on
table1
after
insert
as
if
exists
(
select
*
from
inserted
a
where
a.
user_id
not
in
(
select
USER_ID
from
USERS))
begin
raiserror<
/p>
(
'
数据一致性验证
'
,
16
,
1
)
rollback
transaction
end
inserted of
触发器和<
/p>
after
触发器写法差不多,在此就不多写了。
下面创建一个简单的
DDL
触发器:
--
它的作用是:防止数据库
Test(
我创建的一个实验数据
库
)
中的任一表被修改
或删除
create
trigger
trigDB
on
database
for
drop_table,alter_table
as
精彩文档
实用标准文案
print
'
你一定要禁用触发器“
trigDB
才能删除或修改这个数据
库的表
'
rollback
删除触发器:
drop
trigger
trigDB
ON
DATABASE
一定要记得加上
ON
DATABASE
这条,否则就删除不了。
最后再来条开启和禁用触发器的语句:
disable
trigger
trigDB
on
database
--
禁用触发器
enable
trigger
trigDB
on
database
--
开启触发器
一﹕
<
/p>
触发器是一种特殊的存储过程﹐它不能被显式地调用﹐而是在往表中插入记录﹑更新记录或
者删除
记录时被自动地激活。所以触发器可以用来实现对表实施复杂的完整性约
`
束。
二﹕
SQL Server
为每个触发器都创建了两个专用表﹕
Inserted
表和
Deleted
表。这两个表由系统来
维护﹐它们存在于内存中而不是在数据库中。这两个表的结构总是与被该触发器作用的表的结构相同 。触
发器执行
完成后﹐与该触发器相关的这两个表也被删除。
Delete
d
表存放由于执行
Delete
或
p>
Update
语
句而要从表中删除的所有行
。
Inserted
表存放由于执行
Insert
或
Update
语句而要
向表中插入的所有行。
三﹕
Instead of
和
After
触发器
SQL
Server2000
提供了两种触发器﹕
Instead
of
和
After
触发器。
这两种触发器的差别在于他们被激活的同﹕
Instead of
触发器用于替代引起触发器执行的
T-SQL
语句。除表之外﹐
Instead
of
触发器也可以用于视
图﹐用来扩展视图可以支持的更新操
作。
p>
After
触发器在一个
Insert,U
pdate
或
Deleted
语句之后
执行﹐进行约束检查等动作都在
After
触发器被
激活之前发生。
After
触发器只能用于表。一
个表或视图的每一个修改动作
(insert,update
和
delete)
都
可以有一个
instead of
触发器﹐一个表的每个修改动作都可以有多个<
/p>
After
触发器。
四﹕触发器的执行过程
如果一个
p>
Insert
﹑
update
或者
delete
语句违反了约束﹐那幺
After
触发器不
会执行﹐因为对约束的检查是
在
After
触发器被激动之前发生的。所以
< br>After
触发器不能超越约
束。
Instead of
触发器可以取代激发它的操作来执行。它在
Inserted
表和
Deleted
表刚刚建立﹐其它任何操
p>
作还没有发生时被执行。因为
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.
如果该学生已经毕业,
我希望删除他的学号的同时,
也删除它的借书记录。
等
等。
p>
这时候可以用到触发器。对于
1
,创建一个
Update
触发器:
Create Trigger truStudent
精彩文档
实用标准文案
On Student
--
在
Student
表中创建触发
器
for Update
--
为什么事件触发
As
--<
/p>
事件触发
后所要做的事情
if Update(StudentID)
begin
Update BorrowRecord
Set
StudentID=tID
From BorrowRecord br ,
Deleted
d ,Inserted i
--Deleted
和
Insert
ed
临时
表
Where tID=tID
end
理解触发器里面的两个临时的表:
Deleted ,
Inserted
。注意
Deleted
< br>与
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 tID=tID
精彩文档
实用标准文案
p>
从这两个例子我们可以看到了触发器的关键:
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
_
卷烟库存表
]
,这个触发器较简单。
精彩文档
-
-
-
-
-
-
-
-
-
上一篇:带圈的数字1-100
下一篇:01-100数字编码表