Iterating updated fields within a Trigger

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 Vazquezjv (julie_vazquez@.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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.|||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_SCHEMA.COLUMNS view.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||jv (julie_vazquez@.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

It may need to be a trigger

Hi,

I needed to perform an update of a database table row when another table gets an insert.

Would I use a trigger to do this?, If so, how?

I will need to pass two of the feilds just inserted to perform the update on the second table.

Yes, you can use a trigger to do that.

Issue during update and insert (trigger problems)

Hello,

I have created a job that fill and update a database from a source db with same structure.

INSERT code is made up comparing the pk column in the source and dest db, the missed ones are filled in the destination.

UPDATE: check col by col, if any change value exists, updated is performed with the following statement for any tables in the DB

UPDATE tableADest

SET col1=source.Col1, col2=source.col2, ... coln=source.coln

FROM tableASource source

INNER JOIN tableAdest dest

ON dest.colpk = source.colpk

The main problem is that I cannot identify the row update in the souce db, everytime I have to compare the whole equivalent tables (source and dest db), because there are not timestamp, updated cols or any cols usefuls, to have a subset of data and find any new or upadeted rows.

I tried replication, but it does not work on that db.

So I created a trigger for each table where new insert or updated row must be detected. The PKs are saved on tables.

The problem is that when I run the application it hold-on, when triggers are disabled the application run fine.

How can I use trigger on several tables without affectrunning application?

Thank

can you post your triggers?|||can you post your triggers?
|||

That's the piece of code:

INSERT INTO TempPkDB.dbo.tabella (pk) SELECT i.pk FROM inserted i

WHERE NOT EXISTS (SELECT pk FROM TempPkDB.dbo.tabella t

WHERE t.pk = i.pk)

This code is applied to each tables where tables must be moved to the destination table.

thank