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

Item counts...

I have a group w/ two fields, such as:

INCIDENT TYPE; NUMBER OF EVENTS
Burglary; 1
Burglary; 1
Burglary; 1
Arrest; 1
Citation Issued; 1

However, I need it to do the following:

INCIDENT TYPE; NUMBER OF EVENTS
Burglary; 3
Arrest; 1
Citation Issued; 1

I have set the INCIDENT TYPE fields to 'suppress on duplications'; however, how do I get the field in the NUMBER OF EVENTS field to successfully "count" the same type of events in the INCIDENT TYPE fields if I do this?

Sorry, I haven't used Crystal Reports (now using version 11) since my VB6.0 days (been awhile).I'm no guru myself, but try this:

Suppress the detail line and make a group footer with two fields, field1 (incident type from your example), and a formula field that is Sum(field2, field1). If you didn't have a group on field1, make one.

Good Luck,
Larry

Issue with fields without value

Hi all of you,

I'm loading a Sql destination table via flat file. Everything is file but when one concrete field is empty in my file

it generates an error. Why?

The first 10000 lines have data from 75 position till 85 in my file. But the 10001 no but at the same time I want to load

the rest of the fields

In my Sql table, of course that fields keep nulls.

When I ran DTS this casuistry worked fine.

[Destino 193_TMP [28]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification".

Thanks in advance for your help and thoughts,

Hi ya,

It seems that when you're casting it, it doesn't appear to be of right value for the casting function. If you don't have a casting function do put in the casting function so that null value is ignored or recognised.

Hope that helps

Cheers

Rizwan

|||

I belive the problem is that you're trying to insert a value with the wrong type in your destination. That's what it is with me when i get this error.