473,583 Members | 3,049 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Iterating updated fields within a Trigger

jv
Hi,
I want to log updates to specific fields, storing the new and old
values. Is there any way I can iterate the collection of updated
fields within a trigger in order accomplish this?
Thanks in advance,
Julie Vazquez

Jul 23 '05 #1
4 1857
jv (ju***********@ hotmail.com) writes:
I want to log updates to specific fields, storing the new and old
values. Is there any way I can iterate the collection of updated
fields within a trigger in order accomplish this?


Not in a way that I would call painless. You could use something
with dynamic SQL, but that would come with a performance cost, and
you really don't want to spend to much time in triggers, since you
are in a transaction. You can easliy get into locking issues.

It is better - although boring - to type the SQL for each column to
log. Writing a program that generates the trigger code could be an
option to save time.

If you are in for massive auditing, consider using a third-party
product. A trigger-based solution is SQLAudit from Red Matrix.
Lumigent offers Entegra which works from the transaction log.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2
jv
Do you know if there is away I could iterate through a table and get
all the field names? That would also be a great help.

Thanks.

Jul 23 '05 #3
On 17 Jan 2005 17:02:45 -0800, jv wrote:
Do you know if there is away I could iterate through a table and get
all the field names? That would also be a great help.

Thanks.


Hi jv,

Check out the INFORMATION_SCH EMA.COLUMNS view.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #4
jv (ju***********@ hotmail.com) writes:
Do you know if there is away I could iterate through a table and get
all the field names? That would also be a great help.


Yes. But don't do it. If you are going roll your own, write code for
each column.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

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

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

Similar topics

2
7177
by: Rigs | last post by:
Hi, I'm a SQL Server newbie, so I'd appreciate if someone would tell me if this is possible. I'm running SQL Server 2000 on Win2k Server I have one table with a large number of columns. I have two pieces of logic that I'd like to execute depending upon whether an insert or an update statement was executed on that table. I'd prefer this...
3
3826
by: lphuong | last post by:
When someone modifies a field in a table, I like to find out which field he/she changed. Currently, only the LAST person to modify an object is saved. For example, if I modify an Application record, I will see "MyName" in the "tblApplications.UpdatedBy" field and the date and time I updated it. But it doesn't keep an historical record. We...
2
1759
by: dubian | last post by:
I would like to propagate an event signal to an external application when a table in my MSSql2000 server is updated. Prog A; I have an external application adding records to a table. Prog B; I have another external application using this table as well. When Prog A creates a new record in the Table, how can I have Prog B be notified of the...
12
8684
by: Jim Hammond | last post by:
I am passing the whole object instead or parameters in my select and update methods. I can get the updated object if I set UpdateMethod, let ASP.NET autogenerate an update button, and then press update after making changes, but I don't want that update button. How can I get the updated object when the user presses one of my other action...
0
1059
by: Bernard Cheung | last post by:
I am writing a trigger function. How can I know which fields are being updated in the PL/SQL function? For example I have a table here: CREATE TABLE COMPANY ( COMPANY_ID VARCHAR(10) NOT NULL, NAME VARCHAR(30), ADDRESS VARCHAR(30));
3
4551
by: Jim Archer | last post by:
Hi All... I'm been fighting this problem for a few days now, and it seems like it should be simple. But the solution has eluded me so far... I need to flag a record when it is updated or when it is a new insert. Then I SELECT for the changed records and do something not related to Postgres. Easy enough, I created a trigger procedure and...
3
2033
by: mark blackall | last post by:
Hi all, I am new to vb.net and I am trying to use the vb.net components, rather than relying on the VB6 compatibility stuff with which I am more familiar. However, I seem to have fallen at the first hurdle. I have created a dataset linked to a single SQL table and would like to iterate through the records in it, it, changing one of the...
10
4813
by: Cliff72 | last post by:
Is there a way in VBA to check if a linked table in the database has been updated? Example: I have a table "LedgerTemp" which is a direct link to a text file on the LAN "Ledger.txt" This text file is periodically updated (overwritten) through out the day and night by some mainframe jobs. Right now I just manually run a macro that just...
1
3135
by: rverghese | last post by:
I have a BEFORE UPDATE trigger on a table that calls the tsvector_update_trigger() built in function to update a tsvector field. Since this trigger is called for every update to the table, the trigger function will be called even when the tsvector related field is not updated. My question is, does the index on that tsvector column get updated...
0
7828
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8197
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...
0
6583
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...
1
5704
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...
0
5377
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...
0
3847
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2335
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
1
1436
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1160
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...

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.