|
|
Posted on
2009-10-19 08:52
杨彬Allen
阅读( 176)
评论()
收藏
举报
 Trigger做日志
-- =============================================================================
-- 標題: 觸發器Trigger、inserted | update表的應用
-- 目的: 利用Trigger做日志,當A表的內容有變化時,B表會記錄下這些變化.
-- 原創: takako_mu
-- 时间: 2009-10-19
-- 地点: 昆山
-- =============================================================================
if not object_id('TempA') is null drop table TempA
create table TempA(uid int identity(1,1) not null primary key,department nvarchar(10),uname nvarchar(15))
if not object_id('TempB') is null drop table TempB
create table TempB(uid int identity(1,1) not null primary key,old_department nvarchar(10),
new_department nvarchar(10),old_uname nvarchar(15),new_uname nvarchar(15),uaction nvarchar(10),actionDate datetime)
go

if object_id('Tri_Test')is not null drop trigger Tri_Test
go
create trigger Tri_Test on TempA
for insert,update
as
if not exists(select 1 from deleted)
insert TempB select null,department,null,uname,'Insert',getdate() from inserted
else
begin
declare @department nvarchar(10),@uname nvarchar(15)
select @department=department,@uname=uname from deleted
insert TempB select @department,department,@uname,uname,'Update',getdate() from inserted
end
go

--測試一
--先插入一筆資料進TempA,看看TempB有什么變化.
insert into TempA select 'IT-SCM','Takako'
go
select * from TempA
select * from TempB
 /**//*
(1 個資料列受到影響)

(1 個資料列受到影響)
uid department uname
----------- ---------- ---------------
1 IT-SCM Takako

(1 個資料列受到影響)

uid old_department new_department old_uname new_uname uaction actionDate
----------- -------------- -------------- --------------- --------------- ---------- -----------------------
1 NULL IT-SCM NULL Takako Insert 2009-10-19 09:03:15.210

(1 個資料列受到影響)
*/


--測試二
--對TempA的一筆資料做update動作,看看TempB會有什么變化.
update TempA set department='IT-ERP',uname='Snoopy'
select * from TempA
select * from TempB
 /**//*
(1 個資料列受到影響)

(1 個資料列受到影響)
uid department uname
----------- ---------- ---------------
1 IT-ERP Snoopy

(1 個資料列受到影響)

uid old_department new_department old_uname new_uname uaction actionDate
----------- -------------- -------------- --------------- --------------- ---------- -----------------------
1 NULL IT-SCM NULL Takako Insert 2009-10-19 08:49:33.233
2 IT-SCM IT-ERP Takako Snoopy Update 2009-10-19 08:49:38.737

(2 個資料列受到影響)
*/
|