技术文章 > 利用触发器控制记录的删除和更新

利用触发器控制记录的删除和更新

2019-06-26 20:36

文档管理软件,文档管理系统,知识管理系统,档案管理系统的技术资料:
在一个共享数据库表中,我们往往需要给用户自由地在表中添加记录(发布信息)、维护自己所发布的信息(更新和删除所添加的记录),同时也要避免用户更改或删除别人发布的记录。在SQL SERVER等大型数据库系统中,通过把数据库表、视图的UPDATE、DELETE等权限分配给指定的用户,可以使得他们对表或视图中的记录执行更新和删除操作,但是不能达到只有记录的创建者才能更新和删除记录的目的。本文介绍如何利用触发器来控制表XINXI中记录的更新和删除,实现只有记录创建者才能更新和删除记录的功能。
本文代码在SQL SERVER 7.0,WINDOWS NT SERVER 4.0 SP4下执行通过。
表XINXI结构如下:
create table xinxi (rtitle char(50) null ,rnote text null,ruserid int null)
其中RUSERID字段用来保存创建记录的用户的用户ID。
1、用户的识别
在SQL SERVRE数据库系统中,每个用户都有不同的用户ID——USER ID,就算是某用户先删除然后再重新创建(同名)其USER ID也不相同,因此USERID可以作为一个用户的识别标志。我们可以通过系统函数USER_ID(USER)获得用户的USERID。
2、设计INSERT触发器实现用户USERID的自动保存
为了实现表XINXI的RUSERID字段能自动保存记录创建者的USERID,并且不给记录创建者为该字段赋值,我们为表XINXI创建INSERT触发器XINXI_INSERT。当一个新的记录插入XINXI表时,XINXI_INSERT触发器自动地执行,把插入记录的RUSERID字段值更新为当前用户的USERID,不论用户在插入记录时是否提供RUSERID字段的值。比如某用户的USERID是5,他执行下列两个插入命令:
插入命令1:
insert into xinxi (rtitle,rnote,ruserid) values (“hello“,“good“,1)
插入命令2:
insert into xinxi (rtitle,rnote) values (“hello“,“good“)
其结果都是在表XINXI中插入如下记录,尽管命令1中用户给RUSERID赋值为1:
RTITLE RNOTE RUSERID
Hello good 5
XINXI_INSERT触发器代码如下:
create trigger xinxi_insert on xinxi for insert
as
declare @userid int
--获得当前用户的USERID
set @userid=user_id(user)
--更新当前记录的RUSERID字段
--INSERTED表是一个逻辑的表,它包含了当前插入到XINXI表中的记录
update xinxi set ruserid=@userid where xinxi.rid=(select rid from inserted)
3、设计UPDATE触发器控制用户对记录的更新操作
为了控制用户对记录的UPDATE操作,我们为XINXI表设计UPDATE触发器XINXI_UPDATE,当用户对XINXI中的记录作更新(UPDATE)操作时,触发器XINXI_UPDATE被自动执行,在XINXI_UPDATE中通过比较被更新记录中的RUSERID是否与执行更新操作用户的USERID相同来决定更新操作是能完成或被取消,这样实现只有记录的创建者才能更新记录。
定义XINXI_UPDATE触发器的代码如下:
create trigger xinxi_update on xinxi for update
as
declare @userid int
--获得当前用户的USERID
set @userid=user_id(user)
--判定是否所有被更新记录的RUSERID字段的值都是当前用户的USERID
--如果不是,则不能更新
--DELETED表是一个逻辑的表,它包含当前被更新的记录
if (select count(ruserid) from deleted where ruserid=@userid)<>(select count(ruserid)
from deleted)
begin
raiserror(“包含有不是有当前用户创建的记录,不能更新记录“,10,1)
--事务回滚,取消更新操作,恢复更新前的状态
rollback transaction
end
4、设计DELETE触发器控制用户对记录的删除操作
为控制用户仅能删除自己录入的记录,我们为XINXI表设计了DELETE触发器XINXI_DELETE,当用户删除XINXI表中的记录时XINXI_DELETE触发器被自动执行。在XINXI_DELETE中我们加入了判断执行删除操作用户的USERID与被删除记录的RUSERID是否相同,如果不相同则取消删除操作,从而实现控制用户仅能删除自己录入的记录。
create trigger xinxi_delete on xinxi for delete
as
declare @userid int
--获得用户的USERID
set @userid=user_id(user)
--判断是否所有被删除记录的RUSERID字段值都是当前用户的USERID
--DELETED表是一个逻辑的表,它包含当前被删除的记录
if (select count(ruserid) from deleted where ruserid=@userid)<>(select count(ruserid)
from deleted)
begin
raiserror (“不能删除当前记录“,16,1)
--事务回滚,取消删除操作,恢复原来的记录
rollback tran
end