[数据库]一个利用触发器（trigger）实现数据库表的审计功能（audit）的例子针对ms sql实现

首先建立数据库Test，建立两个表，我们命名为grades和audit，利用audit实现对grades的审计功能，其中grades用于存放学生的成绩，包括“sdudentID，courseID，grade”三个字段，主键为sdudentID，courseID；audit仅仅比表grades增加了两个字段：changeType，changTime，用于记录grades表被修改的类型和修改时间。
建立2个表格的sql语句如下：
create  table grades(
               studentID   int,
               courseID    int,
               grade  int,
               primary key(studentID,courseID)
);
create table audit(
               changeType   char(15),
               changeTime   datetime,
                studentID   int,
               courseID    int,
               grade  int,
               primary key(changeType,changeTime,studentID,courseID,grade)
)

然后在表grades上建立触发器，如果对grades表进行“insert”，"delete","update"操作，在audit表中分别记录这些操作的类型（update(old,new)，insert或者delete），操作的时间，操作影响的数据（比如被删除的数据，插入的数据，修改前的数据，修改后的数据）。
建立触发器的sql语言如下：
/*the beginning of the trigger definition*/
create trigger tr_GradesChanged  on grades  for delete,insert,update
as
declare @insertedCount int
declare @deletedCount int
declare @changeType char(10)
declare @changeTime datetime
declare @updateType char(4)
/*在ms sql server中有两个临时表保存着被删除和被插入的记录，分别叫“deleted”，“inserted”。update可以看作一次删除和一次添加*/
select @insertedCount=count(*) from inserted   
select @deletedCount=count(*) from deleted
select @changeType=
case
         when @insertedCount>0 and @deletedCount>0
                     then 'update'
         when @insertedCount=0 and @deletedCount>0
                     then 'delete'
          else 'insert'
end
select @changeTime=getdate()
select @updateType=''
if @changeType='update'  select @updateType=' old'

insert into audit(changeType,changeTime,studentID,courseID,grade) select @changeType+@updateType, @changeTime,studentID,courseID,grade  from deleted

if @changeType='update'  select @updateType=' new'

insert into audit(changeType,changeTime,studentID,courseID,grade) select @changeType+@updateType, @changeTime,studentID,courseID,grade  from inserted

/*the end of the trigger definition*/

至此，整个触发器就建立好了，我们可以对此触发器进行测试

运行以下语句：
delete from grades
delete from audit
insert into grades values (19224,002,90)
update grades set grade=100 where studentID =19224
delete from grades where studentID=19224
select * from audit order by changeTime asc
将得到以下结果：


可见，audit表对grades表的操作进行了监控，记载了其变化情况。由于上面的几条测试语句运行的时间间隔太短，所以changetime相同，查询结果首先按changtime升序排列，然后按changtype升序（默认）排列，所以和实际结果有点差别，理论上应该是：
   changType      changeTime   studentID      courseID     grade
1   insert            -----------      19224               2               90
2   update old      -----------     19224              2               90
3   update new    -----------     19224               2              100
4   delete            ----------        19224              2              100
<!---------------------------------------the  end--------------------------------------------------------------------->