求一触发器的写法

Question

数据源表A结构：
ID   name  dep  arr 
1      a           想写一个触发器，当A表的数据变更时，插入数据到B表，包括变更的关键字（rowkey)，命令(insert 还是update,delete),
changeColum(哪个字段的数据发生了变化），oldeValue(变更前的值),变更后的值（newValue),changeTime(更改时间)
B表的结构如下：
ItemID  rowkey command changeColum oldValue newValue changeTime比如，如果开始A表的数据如上，过一段时间后，数据变成了
ID   name  dep  arr 
1      a   12       那B表则添加了以下数据：
ItemID  rowkey  command   changeColum oldValue  newValue changeTime
1         1       update    dep                   12     2011-4-15 12:27:34
如果A表数据变成
ID   name  dep  arr 
1      a   12       
2      b   34    67
那B表则添加了以下数据：
ItemID  rowkey  command   changeColum oldValue  newValue changeTime
1         1       update    dep                   12     2011-4-15 12:27:34
2         2       insert    name                   b     2011-4-15 12:30:01
3         2       insert    dep                    34    2011-4-15 12:30:01
4         2       insert    arr                    67    2011-4-15 12:30:01求高手帮忙！不胜感激。 

Answer 1

create trigger tgr_t on A
after insert,update,delete
as
begin
    if exists (select 1 from deleted) and exists(select 1 from inserted) -- update
    -- 这里你确定只更新了一个字段
     beign
    if update (dep)
       insert into b ....
    else if update (..)
    ...
    end
    else if exists(select 1 from inserted) -- insert
    
    else -- delete
end貌似不爽。

Answer 2

表A更新前某个column的值怎么取得？

Answer 3

create table a
(
 id int,
 [name] varchar(100),
 dep varchar(100),
 arr  varchar(100)
)create table b
(
ItemID  int identity(1,1)   not   null,
rowkey  int,
command varchar(10),
changeColum varchar(100),
oldValue  varchar(100),
newValue  varchar(100),
changeTime datetime
)
insert into  a (id, [name]) values('1','a')create trigger test_trg on a
for insert, update, delete
as 
begin
declare @insert_flag int
declare @delete_flag int
declare @id_old int
declare @id_new int
declare @name_old varchar(100)
declare @name_new varchar(100)
declare @dep_old varchar(100)
declare @dep_new varchar(100)
declare @arr_old varchar(100)
declare @arr_new varchar(100)
declare @command varchar(10)
declare @changeColum varchar(100)
select @insert_flag = count(1) from inserted
select @delete_flag = count(1) from deleted
if @insert_flag >0 and @delete_flag=0 
begin
--insert
set @command='insert'
select @id_new=id, @name_new=name, @dep_new=dep, @arr_new=arr from inserted if(@name_new is not null)
begin 
 set @changeColum='name'
 insert into b(rowkey, command, changeColum, oldValue, newValue, changeTime)  values(@id_new,@command,@changeColum, '',@name_new, getdate() )
end 
if(@dep_new is not null)
begin 
 set @changeColum='dep'
--.....
end 
if(@arr_new is not null)
begin 
 set @changeColum='arr'
--.....
end 
end
--update
if @insert_flag >0 and @delete_flag>0 
begin
--....
end
--delete
if @insert_flag =0 and @delete_flag>0 
begin
....
end
end

Answer 4

to:lxpbs8851
 消息 156，级别 15，状态 1，过程 test_trg，第 53 行
关键字 'if' 附近有语法错误。
消息 156，级别 15，状态 1，过程 test_trg，第 57 行
关键字 'end' 附近有语法错误。

Answer 5

IF OBJECT_ID('table_A', 'u') IS NOT NULL
DROP TABLE table_A
IF OBJECT_ID('table_B', 'u') IS NOT NULL
DROP TABLE table_B
CREATE TABLE table_A
(
ID INT IDENTITY,
[name] VARCHAR(10),
dep VARCHAR(10),
arr VARCHAR(10)
)
CREATE TABLE table_B
(
ItemID INT IDENTITY,
rowkey INT,
command VARCHAR(10),
changeColum VARCHAR(10),
oldValue VARCHAR(10),
newValue VARCHAR(10),
changeTime DATETIME
)
GO
--SQL:
CREATE TRIGGER trigger_test ON table_A
FOR INSERT, UPDATE, DELETE AS
BEGIN
IF NOT EXISTS(SELECT 1 FROM DELETED)
AND EXISTS(SELECT 1 FROM INSERTED) --insert
BEGIN
INSERT table_b
(
rowkey,
command,
changeColum,
oldValue,
newValue,
changeTime
)
SELECT 
ID,
'insert',
field_name,
null,
field_value,
getdate()
FROM INSERTED A
UNPIVOT
(field_value FOR field_name IN([name], [dep], [arr])) B
END
ELSE IF EXISTS(SELECT 1 FROM DELETED)
AND NOT EXISTS(SELECT 1 FROM INSERTED) --delete
BEGIN
INSERT table_b
(
rowkey,
command,
changeColum,
oldValue,
newValue,
changeTime
)
SELECT 
ID,
'delete',
field_name,
field_value,
null,
getdate()
FROM DELETED A
UNPIVOT
(field_value FOR field_name IN([name], [dep], [arr])) B
END
ELSE IF EXISTS(SELECT 1 FROM DELETED)
AND EXISTS(SELECT 1 FROM INSERTED) --udpate
BEGIN
INSERT table_b
(
rowkey,
command,
changeColum,
oldValue,
newValue,
changeTime
)
SELECT
M.ID,
'update',
M.field_name,
N.field_value,
M.field_value,
getdate()
FROM(
SELECT 
ID,
field_name,
field_value
FROM INSERTED A
UNPIVOT
(field_value FOR field_name IN([name], [dep], [arr])) B
) M
INNER JOIN(
SELECT 
ID,
field_name,
field_value
FROM DELETED C
UNPIVOT
(field_value FOR field_name IN([name], [dep], [arr])) D
) N
ON M.ID = N.ID AND M.field_name = N.field_name AND M.field_value <> N.field_value
END
END
GO
--test
INSERT table_A
SELECT 'a', 'dep', 'arr'update table_A
set [name] = 'b',
dep = 'dep1',
arr = 'arr1'DELETE TABLE_A
SELECT * FROM table_b
--result
select * from table_b
/*
1 1 insert name NULL a 2011-04-15 14:25:17.610
2 1 insert dep NULL dep 2011-04-15 14:25:17.610
3 1 insert arr NULL arr 2011-04-15 14:25:17.610
4 1 update name a b 2011-04-15 14:25:17.610
5 1 update dep dep dep1 2011-04-15 14:25:17.610
6 1 update arr arr arr1 2011-04-15 14:25:17.610
7 1 delete name b NULL 2011-04-15 14:25:17.613
8 1 delete dep dep1 NULL 2011-04-15 14:25:17.613
9 1 delete arr arr1 NULL 2011-04-15 14:25:17.613
*/

Answer 6

谢谢各位
消息 325，级别 15，状态 1，过程 trigger_test，第 25 行
'UNPIVOT' 附近有语法错误。您可能需要将当前数据库的兼容级别设置为更高的值，以启用此功能。有关存储过程 sp_dbcmptlevel 的信息，请参见帮助。请问怎样将当前数据库的兼容级别设置为更高的值？

Answer 7

create trigger X_insert_update on a
after insert,update
begin
--update
if exists(select 1 from inserted) and exists(select 1 from deleted)
begin insert into b(rowkey, command, changeColum, oldValue, changeTime)
select inserted.ID,
'update',
'name',
inserted.[name],
getdate()
from inserted
inner join deleted
on isnull(inserted.[name],'') <> isnull(deleted.[name],'')
union
select inserted.ID,
'update',
'dep',
inserted.dep,
getdate()
from inserted
inner join deleted
on isnull(inserted.dep,'') <> isnull(deleted.dep,'')
union
select inserted.ID,
'update',
'arr',
inserted.arr,
getdate()
from inserted
inner join deleted
on isnull(inserted.arr,'') <> isnull(deleted.arr,'')
end
else if exists(select 1 from inserted) 
begin
insert into b(rowkey, command, changeColum, oldValue, changeTime)
select ID,
'insert',
'name',
[name],
getdate()
from inserted
union
select ID,
'insert',
'dep',
dep,
getdate()
from inserted
union
select ID,
'insert',
'arr',
arr,
getdate()
from inserted
end
end