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 cannot be found in the collection corresponding to the requested name or ordinal.

I am retrieving the data using the below sql statement in VB 6.0 & it giving me the Error
Item cannot be found in the collection corresponding to the requested name or ordinal.
I have tried to run the same sql statement it ran fine
I am using a recordset to retrive a data & it looks like recorset do have a data init but when I am assigning it to a temp variable it ging me th eabove stated error
strsql = "select sum(isnull(Amount,0)) from Payments where Name ='" & Name & "'"
objRS.CursorLocation = enmCursorLocation
Call objRS.Open(vntSPNameOrSQLOrCmdObject, vntConn, enmCursorType, enmLockType, lngOptions) If (Not objRS Is Nothing) Then
If (Not objRS Is Nothing) Then
dblAmount = CDbl(Trim$(objRS.Fields.Item("Amount").Value))
end if
Please advice
Thanks
Your column is no longer called amount, since you are performing
aggregations against it. You can change your SQL statement to provide an
alias, e.g.
SELECT Amount = SUM(...) FROM ...
Or you can use a different, more meaningful name, e.g.
SELECT SumAmount = SUM(...) FROM ...
Or in your code you can refer to the ordinal position of the column, instead
of the name.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"SAMAY" <anonymous@.discussions.microsoft.com> wrote in message
news:6C1456DD-FBC0-4ADC-8B99-F7BFA10821F1@.microsoft.com...
> I am retrieving the data using the below sql statement in VB 6.0 & it
giving me the Error
> Item cannot be found in the collection corresponding to the requested name
or ordinal.
> I have tried to run the same sql statement it ran fine
> I am using a recordset to retrive a data & it looks like recorset do have
a data init but when I am assigning it to a temp variable it ging me th
eabove stated error
> strsql = "select sum(isnull(Amount,0)) from Payments where Name ='" & Name
& "'"
> objRS.CursorLocation = enmCursorLocation
> Call objRS.Open(vntSPNameOrSQLOrCmdObject, vntConn, enmCursorType,
enmLockType, lngOptions) If (Not objRS Is Nothing) Then
> If (Not objRS Is Nothing) Then
> dblAmount = CDbl(Trim$(objRS.Fields.Item("Amount").Value))
> end if
> Please advice
> Thanks
|||Thanks for your help
it worked
Thanks again

Item cannot be found in the collection corresponding to the requested name or ordinal.

I am retrieving the data using the below sql statement in VB 6.0 & it giving
me the Error
Item cannot be found in the collection corresponding to the requested name o
r ordinal.
I have tried to run the same sql statement it ran fine
I am using a recordset to retrive a data & it looks like recorset do have a
data init but when I am assigning it to a temp variable it ging me th eabov
e stated error
strsql = "select sum(isnull(Amount,0)) from Payments where Name ='" & Name &
"'"
objRS.CursorLocation = enmCursorLocation
Call objRS.Open(vntSPNameOrSQLOrCmdObject, vntConn, enmCursorType, enmLockTy
pe, lngOptions) If (Not objRS Is Nothing) Then
If (Not objRS Is Nothing) Then
dblAmount = CDbl(Trim$(objRS.Fields.Item("Amount").Value))
end if
Please advice
ThanksYour column is no longer called amount, since you are performing
aggregations against it. You can change your SQL statement to provide an
alias, e.g.
SELECT Amount = SUM(...) FROM ...
Or you can use a different, more meaningful name, e.g.
SELECT SumAmount = SUM(...) FROM ...
Or in your code you can refer to the ordinal position of the column, instead
of the name.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"SAMAY" <anonymous@.discussions.microsoft.com> wrote in message
news:6C1456DD-FBC0-4ADC-8B99-F7BFA10821F1@.microsoft.com...
> I am retrieving the data using the below sql statement in VB 6.0 & it
giving me the Error
> Item cannot be found in the collection corresponding to the requested name
or ordinal.
> I have tried to run the same sql statement it ran fine
> I am using a recordset to retrive a data & it looks like recorset do have
a data init but when I am assigning it to a temp variable it ging me th
eabove stated error
> strsql = "select sum(isnull(Amount,0)) from Payments where Name ='" & Name
& "'"
> objRS.CursorLocation = enmCursorLocation
> Call objRS.Open(vntSPNameOrSQLOrCmdObject, vntConn, enmCursorType,
enmLockType, lngOptions) If (Not objRS Is Nothing) Then
> If (Not objRS Is Nothing) Then
> dblAmount = CDbl(Trim$(objRS.Fields.Item("Amount").Value))
> end if
> Please advice
> Thanks|||Thanks for your help
it worked
Thanks again

Issue programatically building oledbTransformation.

I get the following validation error when i run a package which contains an OledbTransformation.

The external metadata column collection is out of synchronization with the data source columns.
The column "Param_0" needs to be updated in the external metadata column collection.
The column "Param_1" needs to be updated in the external metadata column collection.

Please advice as to how to add the external columns of oledbTransformation to external meatadata and map them to the input columns programatically using C#.

Please stop starting new threads for the same issue.