求写触发器

[Oer]

如果一个表中的记录被修改，那么就要在log表中记录修改的是哪个字段，修改前的值，修改后的值。其实就是日志。由于需要记录的表比较多，就想做一个比较通用的触发器。

[Dhammacakkhu]

要么一个一个字段去判断，要么把全部字段内容保存，一份旧内容，一份新内容，这么就可以比较那个字段值被改动了

暂时只知道这么做，高手上。

[hongrisoft]

CREATE trigger Chargeinfo_Update on StuChargeInfo
for update
as
begin tran
begin
if update(ChargePNum)
update SchBasicInfo set SchBasicInfo.CPNum=SchBasicInfo.CPNum+i.ChargePNum-j.ChargePNum from inserted i,deleted j where left(i.CodeID,13)=SchBasicInfo.SchCode and left(j.CodeID,13)=SchBasicInfo.SchCode
update SchGCCount set SchGCCount.CPNum=SchGCCount.CPNum+i.ChargePNum-j.ChargePNum from inserted i,deleted j where left(i.CodeID,13)=SchGCCount.SchCode and left(j.CodeID,13)=SchGCCount.SchCode and substring(j.CodeID,14,4)=SchGCCount.Grade and substring(i.CodeID,14,4)=SchGCCount.Grade and substring(i.CodeID,20,2)=SchGCCount.ClassID and substring(j.CodeID,20,2)=SchGCCount.ClassID

end

if @@error=0
begin
commit tran
end
else
begin
rollback tran
raiserror('更新数据失败,错误号为d%',12,1,@@error)
end

[Yanacoder]

--IN BANK AND BROKERAGE
--使用SQL-SERVER自带的数据库作范例
CREATE TRIGGER trAuthors_InsUpd ON Authors
FOR UPDATE
AS
DECLARE @intRowCount int
SELECT @intRowCount = @@RowCount
IF @intRowCount > 0
BEGIN
IF (NOT EXISTS
(
SELECT account FROM bank WHERE account IN
(SELECT account FROM inserted)
UNION
SELECT account FROM brokerage WHERE account IN
(SELECT account FROM inserted)
)
)
BEGIN
RAISERROR(56000, 10, 1)
ROLLBACK TRANSACTION
RETURN
END
END

[Yanacoder]

--使用SQL-SERVER自带的数据库作范例
create trigger trtitles_upd on titles
for update
as
declare @mnyoldprice money,
@mnynewprice money,
@chvtitleid varchar(6),
@chvmsg varchar(255),
@inttotalqty integer
if update(price)
begin
declare cupricechange cursor
for
select d.title_id, d.price, i.price
from inserted i inner join deleted d
on i.title_id = d.title_id
open cupricechange
fetch next from cupricechange into
@ c h v titleid, @mnyoldprice, @mnynewprice
while (@@fetch_status <> -1)
begin
select @inttotalqty = sum(qty)
from sales
where title_id = @chvtitleid
if @mnynewprice > @mnyoldprice * 1.25 and @inttotalqty < 100
begin
select @chvmsg = 'an attempt has been made to reduce'
+ ' the price of title'
+ ' ' + @chvtitleid + ' by more than 25% from'
+ ' ' + convert ( varchar(10), @mnyoldprice) + ' to'
+ ' ' + convert ( varchar(10), @mnynewprice) + '.'
+ ' this is transaction has been rolled back.'
print @chvmsg
print ' '
update titles
set price = @mnyoldprice
where title_id = @chvtitleid
end
fetch next from cupricechange
into @chvtitleid, @mnyoldprice, @mnynewprice
end
deallocate cupricechange
end
go
--将print换成insert语句

[Yanacoder]

需要使用deleted,inserted 系统表和cursor配合完成你的任务，以上为inserted,deleted 和cursor 的使用方法样例，当然还有 @@RowCount变量的使用。

[Oer]

你这个好像只能用于ChargePNum字段，我想要的是对多个字段然后写个通用的触发器

[Oer]

up,请各位大侠帮帮忙,急

[hongrisoft]

关注中.................