OPENXML, UPDATES, and SET Criteria

I have been using OPENXML with great success to create INSERT
statments.
But with updates I am having an issue. Because the tables I am
updating use triggers on many of the fields, I am only allowed to
update fields that have actual changed values due to the fact the
triggers fire based on UPDATE, not an actual change. I do not have
authority to change the triggers.
When I try using OPENXML, sending only the fields that have changed,
the result is that the updated fields are changed, but all other fields
are emptied because they were not sent through in the XML file.
Because I do not know which fields will be changed from one execution
to the next, I need a way for the statement to be written which would
only update the changed fields.
I understand why it is happening, I am curious if there is a way around
it without having to go back to using plain old sql statements. I
cannot use Updategrams or Diffgrams as it is not available within our
application. Currently my only thought is to create all of the SQL
statments in an XSL and dynamically create normal, non-OPENXL
statements to be sent to the stored procedure.
This is not what I would consider the best solution as it makes coding
and debugging the SQL statements much more difficult.
Any insight would be greatly appreciated.
Thanks!
Here's an example:
Prior to Update:
Row 1: fielda = 1, fieldb = 2, fieldc = 3
Row 2: fielda = 1, fieldb = 2, fieldc = 3
Updated Fields:
Row 1: fieldb = 2a
Row 2: fielda= 2, fieldb=3
Update Statement using XML with only the updated fields:
UPDATE
table_with_triggers
SET
fielda = xmlDoc.fielda,
fieldb = xmlDoc.fieldb,
fieldc = xmlDoc.fieldc
FROM
table_with_triggers,
OPENXML (@.hDoc,'DatabaseTables/table_with_triggers', 2)
WITH table_with_triggers xmlDoc
WHERE table_with_triggers.id = @.int_id
After Update:
Row 1: fielda is empty, field b = 2b, fieldc is empty
Row 2: fielda = 2, fieldb = 3, fieldc is empty
Dynamically generating your statements is probably best in this case. If you
do not allow updates to NULL, you could also do conditionals on whether
OpenXML extracts a value or returns NULL.
Best regards
Michael
"box464" <box464@.gmail.com> wrote in message
news:1131306557.832830.295700@.z14g2000cwz.googlegr oups.com...
>I have been using OPENXML with great success to create INSERT
> statments.
> But with updates I am having an issue. Because the tables I am
> updating use triggers on many of the fields, I am only allowed to
> update fields that have actual changed values due to the fact the
> triggers fire based on UPDATE, not an actual change. I do not have
> authority to change the triggers.
> When I try using OPENXML, sending only the fields that have changed,
> the result is that the updated fields are changed, but all other fields
> are emptied because they were not sent through in the XML file.
> Because I do not know which fields will be changed from one execution
> to the next, I need a way for the statement to be written which would
> only update the changed fields.
> I understand why it is happening, I am curious if there is a way around
> it without having to go back to using plain old sql statements. I
> cannot use Updategrams or Diffgrams as it is not available within our
> application. Currently my only thought is to create all of the SQL
> statments in an XSL and dynamically create normal, non-OPENXL
> statements to be sent to the stored procedure.
> This is not what I would consider the best solution as it makes coding
> and debugging the SQL statements much more difficult.
> Any insight would be greatly appreciated.
> Thanks!
> Here's an example:
> Prior to Update:
> Row 1: fielda = 1, fieldb = 2, fieldc = 3
> Row 2: fielda = 1, fieldb = 2, fieldc = 3
> Updated Fields:
> Row 1: fieldb = 2a
> Row 2: fielda= 2, fieldb=3
> Update Statement using XML with only the updated fields:
> UPDATE
> table_with_triggers
> SET
> fielda = xmlDoc.fielda,
> fieldb = xmlDoc.fieldb,
> fieldc = xmlDoc.fieldc
> FROM
> table_with_triggers,
> OPENXML (@.hDoc,'DatabaseTables/table_with_triggers', 2)
> WITH table_with_triggers xmlDoc
> WHERE table_with_triggers.id = @.int_id
> After Update:
> Row 1: fielda is empty, field b = 2b, fieldc is empty
> Row 2: fielda = 2, fieldb = 3, fieldc is empty
>