sql server 實現(xiàn)對表的審計功能（轉(zhuǎn)帖） — Windows Live

pipizhang 2010-09-01

展開全文

sql server 實現(xiàn)對表的審計功能（轉(zhuǎn)帖）

首先建立數(shù)據(jù)庫Test,，建立兩個表,，我們命名為grades和audit,，利用audit實現(xiàn)對grades的審計功能,，其中g(shù)rades用于存放學生的成績,，包括“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上建立觸發(fā)器,，如果對grades表進行“insert”,，"delete","update"操作，在audit表中分別記錄這些操作的類型（update(old,new),，insert或者delete）,，操作的時間，操作影響的數(shù)據(jù)（比如被刪除的數(shù)據(jù),，插入的數(shù)據(jù),，修改前的數(shù)據(jù)，修改后的數(shù)據(jù)）,。
建立觸發(fā)器的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*/

至此,，整個觸發(fā)器就建立好了，我們可以對此觸發(fā)器進行測試

運行以下語句：
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
將得到以下結(jié)果：

可見,，audit表對grades表的操作進行了監(jiān)控,，記載了其變化情況。由于上面的幾條測試語句運行的時間間隔太短,，所以changetime相同,，查詢結(jié)果首先按changtime升序排列，然后按changtype升序（默認）排列,，所以和實際結(jié)果有點差別,，理論上應該是：
   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