473,595 Members | 2,474 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

triggers audit

I need audit triggers that change columns value in the same record
that fire trigger.
I need how to do..
Thanks..
Jul 20 '05 #1
11 6621
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_DET AIL
FOR INSERT
NOT FOR REPLICATION
AS
BEGIN

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

UPDATE TR_SHIPMENT_DET AIL
SET
subgroup_cd = dbo.f_get_subgr oup(tr_shipment _detail.comp_cd )
FROM TR_SHIPMENT_DET AIL 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_subgr oup() 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.e s> wrote in message
news:9b******** *************** **@posting.goog le.com...
I need audit triggers that change columns value in the same record
that fire trigger.
I need how to do..
Thanks..

Jul 20 '05 #2
ra****@ozu.es (raulgz) wrote in message news:<9b******* *************** ***@posting.goo gle.com>...
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.PrimaryKeyCol umn = i.PrimaryKeyCol umn
end
go

Simon
Jul 20 '05 #3
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.wash ington.edu
(replace 'x' with a 'u' to reply)

Jul 20 '05 #4


Thanks,

I know this solution using inserted table,
but when l make one insert this trigger
produce one insert and nine updates

I can avoid this behaviour.
Thank very much again.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #5

Thanks,

I know this solution using inserted table,
but when l make one insert this trigger
produce one insert and nine updates

I can avoid this behaviour.
Thank very much again.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #6
If you want you can turn off recursive triggers. Review SQL help file.
sp_configure.

raul garcia zamarra wrote:


Thanks,

I know this solution using inserted table,
but when l make one insert this trigger
produce one insert and nine updates

I can avoid this behaviour.
Thank very much again.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Jul 20 '05 #7
raul garcia zamarra (ra****@ozu.es) writes:
I know this solution using inserted table,
but when l make one insert this trigger
produce one insert and nine updates

I can avoid this behaviour.


The trigger that Simon posted will only cause one update, unless the
database option RECURSIVE_TRIGG ERS is active, in which case the trigger
will exceed the maximum nesting level and crash.

Maybe you could clarify what you mean with nine updates.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #8

"Daniel Morgan" <da******@x.was hington.edu> wrote in message
news:1075301899 .586131@yasure. ..
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.

They do have INSTEAD OF which I understand is not exactly the same, but
offers similar functionality.

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

Jul 20 '05 #9

Thanks,

I don`t change recursive_trigg ers to false
because this change affect all triggers in
my database sql server.

Do you know another solution ???
Thank you very much.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
3473
by: Keith | last post by:
Not sure if anyone in here knows the answer to this, but I asked in a SQL group and haven't had a suitable answer and since the front end app is ASP I though I'd give here a try. I am trying to create a simple trigger in my SQL DB so that when a record is updated or deleted a copy of the original record is placed in an audit table. However, I keep getting the following error:
3
3635
by: John Pan | last post by:
Hi I am looking to implement an audit/history table/tables but am looking at doing this without the use of triggers. The reason for doing this is that the application is highly transactional and speed in critical areas is important. I am worried that triggers would slow things down. I am more used to other database where by there is a utility to "dump"
1
6149
by: Gent | last post by:
am using FOR UPDATE triggers to audit a table that has 67 fields. My problem is that this slows down the system significantly. I have narrowed down the problem to the size (Lines of code) that need to be compiled after the trigger has been fired. There is about 67 IF Update(fieldName) inside the trigger and a not very complex select statement inside the if followed by an insert to the audit table. When I leave only a few IF-s in the...
2
3408
by: ecastillo | last post by:
i'm in a bit of a bind at work. if anyone could help, i'd greatly appreciate it. i have a web app connecting to a sql server using sql server authentication. let's say, for example, my login/password is dbUser/dbUser. the web app however, is using windows authentication. so if I am logged into the network as 'DOMAIN\Eric', when I access my web app, my web app knows that I am 'DOMAIN\Eric'. but to the sql server db, I am user...
3
6276
by: Zlatko Matiæ | last post by:
Hello. I tried to implement audit trail, by making an audit trail table with the following fileds: TableName,FieldName,OldValue,NewValue,UpdateDate,type,UserName. Triggers on each table were set to do the job and everything was fine except that in the audit trail you couldn't know which row exacltly was updated/inserted/deleted...Therefore I introduced 3 additional columnes (RowMark1, RowMark2, RowMark3) which should identify the...
1
1968
by: Jeff Magouirk | last post by:
Dear Group, I would like to create an audit table that is created with a trigger that reflects all the changes(insert, update and delete) that occur in table. Say I have a table with Subject_ID, visit_number, dob, weight, height, User_name, inputdate The audit table would have .
1
2354
by: Mark Flippin | last post by:
I'm evidently not understanding nested triggers and I'm looking for some help. I've an Invoice table (see below) that I want to enforce two actions via after triggers. The first trigger maintains a set of audit columns in the table indicating the date on which the row was inserted, the date of the last change, and the user who made the last change (see below)
5
6457
by: Michel Esber | last post by:
Audit trigger Hello, LUW DB2 V8 FP13 I am trying to create audit triggers in order to find out which user/application is deleting data from a table, as well as the statement the user entered.
0
931
by: chirnag | last post by:
I have and audit table to collection changes happening on an entity. This will be taken care by triggers. During a batch test, I found that out of 1000 inserts into the main table, I got only 997 entries into audit table. I suspected that there might be a problem in the way I am accessing inserted tuple( I am reading only one record from inserted tuple and inserting that record into audit table). So, I changed my trigger code to create a...
0
8261
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8379
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8019
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8251
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
6674
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
5839
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5418
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
2391
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
1223
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.