触发审计 [英] triggers audit

问题描述

我需要在同一记录中更改列值的审计触发器

触发触发器。

我需要怎么做..

谢谢..

解决方案

Raulgz，

不幸的是，MS SQL Server 2000不提供BEFORE触发器，其中

允许您在插入/更新之前按下触发器中的数据。

我希望在下一个主要版本中使用此功能。

做这种数据按摩的最佳方法是首先将数据上传到

临时表，然后按摩数据，然后将其插入到您的生产

表中，或者让应用程序进行数据按摩。但是，如果那是不可能的，那么就是这样做的方法：


创建触发器

TIB_TR_SHIPMENT_DETAIL_GROUP
ON TR_SHIPMENT_DETAIL

FOR INSERT

不用于复制

AS

BEGIN


如果SYSTEM_USER =''SQL_Repl''或SYSTEM_USER ='''SQLSrvLogin''

返回


UPDATE TR_SHIPMENT_DETAIL
SET

subgroup_cd = dbo.f_get_subgroup（tr_shipment_detail.comp_cd）

FROM TR_SHIPMENT_DETAIL tr

WHERE EXISTS

（SELECT i。*

FROM FROM i

WHERE i.SHIP_KEY = tr.SHIP_KEY）

END


说明：首先，如果数据是从另一个数据库复制的，则不允许触发任何触发器。这会产生反弹效果，从而
更改可能会从db反弹到db并且永不停止。所以这就是

NOT FOR REPLICATION和If SYSTEM_USER ...语句的原因。两者都不需要
，因为要么应该做这个工作。

其次，触发器不会逐行触发（哦，我多么希望

这是一个选项），但是它们会在桌面上的每个

交易中被触发一次。因此，如果一个事务插入/更新多个

记录，则插入记录中将存在多个记录。表。这是

WHERE EXISTS子句的原因。
dbo.f_get_subgroup（）函数是用户定义的函数来获取

子组并且特定于此数据库，但它确实显示了您如何根据函数设置触发器中的值。


希望这会有所帮助。

在我们关于触发器的部分中有更详细的解释 www.TechnicalVideos.net 。

祝你好运，

Chuck Conover
www.TechnicalVideos.net


" raulgz" < RA **** @ ozu.es>在消息中写道

新闻：9b ************************* @ posting.google.co m ... < blockquote class =post_quotes>我需要审核触发器来更改同一记录中的列值
触发触发器。

我需要怎么做..

谢谢..

ra **** @ ozu.es （raulgz）在留言新闻中写道：< 9b ************************* @ posting.google.c om> ...

我需要在同一记录中更改列值的审计触发器
触发触发器。

我需要怎么做..

谢谢..

我不确定我到底知道你需要什么，但也许是这样的？


创建触发器MyTrigger

on MyTable

更新后

as

开始

如果@@ rowcount = 0

返回


更新MyTable

set UpdatedTime = getdate（），UpdatedBy = suser _sname（）

来自MyTable t

加入插入i

on t.PrimaryKeyColumn = i.PrimaryKeyColumn

end

go


Simon

Chuck Conover写道：

Raulgz ，不幸的是，MS SQL Server 2000不提供BEFORE触发器，它允许您在插入/更新之前按下触发器中的数据。

鉴于BEFORE触发器已经存在于其他RDBMS产品系列中近乎20年，它们肯定会很好的解决它：

也许在期间当前的十年。然后也许他们可以开始考虑产品中还缺少的其他基本功能。


-

Daniel Morgan
da ****** @ x.washington.edu

（将''x''替换为''u''来回复）

I need audit triggers that change columns value in the same record
that fire trigger.
I need how to do..
Thanks..

解决方案

Raulgz,
Unfortunately, MS SQL Server 2000 does not offer BEFORE triggers, which
would allow you to massage the data in a trigger before the insert/update.
I am hoping for this functionality in the next major release.
The best way to do this kind of data massaging is to upload data to a
temp table first, then massage the data, then insert it into your production
table, or to have the application do the data massaging. But, if that is
not possible, here is the way to do it:

CREATE TRIGGER
TIB_TR_SHIPMENT_DETAIL_GROUP
ON TR_SHIPMENT_DETAIL
FOR INSERT
NOT FOR REPLICATION
AS
BEGIN

if SYSTEM_USER = ''SQL_Repl'' OR SYSTEM_USER = ''SQLSrvLogin''
RETURN

UPDATE TR_SHIPMENT_DETAIL
SET
subgroup_cd = dbo.f_get_subgroup(tr_shipment_detail.comp_cd)
FROM TR_SHIPMENT_DETAIL tr
WHERE EXISTS
(SELECT i.*
FROM inserted i
WHERE i.SHIP_KEY = tr.SHIP_KEY)
END

Explanation: First, don''t allow any trigger to fire if the data is
replicated from another database. This creates a bounce effect whereby
changes may bounce from db to db and never stop. So this is the reason for
the NOT FOR REPLICATION and the If SYSTEM_USER... statements. Both are not
needed, since either should do the job.
Second, triggers are not fired on a row by row basis (oh, how I wish
that was an option), but instead they are triggered once for each
transaction on the table. So, if one transaction inserts/updates multiple
records, there will be multiple records in the "inserted" table. This is
the reason for the WHERE EXISTS clause.
The dbo.f_get_subgroup() function is a user-defined function to get the
subgroup and is specific to this database, but it does show how you might
set a value in a trigger based on a function.

Hope this helps.
There is a much more detailed explanation in our section on Triggers at
www.TechnicalVideos.net.
Best regards,
Chuck Conover
www.TechnicalVideos.net

"raulgz" <ra****@ozu.es> wrote in message
news:9b*************************@posting.google.co m...

I need audit triggers that change columns value in the same record
that fire trigger.
I need how to do..
Thanks..

ra****@ozu.es (raulgz) wrote in message news:<9b*************************@posting.google.c om>...

I need audit triggers that change columns value in the same record
that fire trigger.
I need how to do..
Thanks..

I''m not sure I understand exactly what you need, but perhaps something like this?

create trigger MyTrigger
on MyTable
after update
as
begin
if @@rowcount = 0
return

update MyTable
set UpdatedTime = getdate(), UpdatedBy = suser_sname()
from MyTable t
join inserted i
on t.PrimaryKeyColumn = i.PrimaryKeyColumn
end
go

Simon

Chuck Conover wrote:

Raulgz,
Unfortunately, MS SQL Server 2000 does not offer BEFORE triggers, which
would allow you to massage the data in a trigger before the insert/update.

Given that BEFORE triggers have existed in other RDBMS product lines for
almost 20 years it certainly would be nice of them to get around to it:
Maybe during the current decade. Then perhaps they can start
contemplating the other basic functionality that is still missing from
the product.

--
Daniel Morgan
da******@x.washington.edu
(replace ''x'' with a ''u'' to reply)

这篇关于触发审计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！