July 17, 2002 at 2:47 pm
Is there any function or return code check method in MS SQL 2000 that will let you know if any data was actually changed when updating a record? I'm developing an Web app were the user can update their address info and I want to send an email if data was actually changed. I would prefer not to have to do a field by field compare of the before and after.
July 17, 2002 at 3:48 pm
I beleivbe there is a RecordsAffected object in the ADO connector which if all you do is run an Update and it is not an SP with SET NOCOUNT ON it will be populated.
See here for details on .Execute which contains this.
http://www.devguru.com/Technologies/ado/quickref/command_execute.html
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
July 17, 2002 at 5:29 pm
Or you can do the update in a proc and explicitly return the record count via an output parameter. Either way I think the count is the number of rows processed that matched the where statement, not the number where changes were made. If you really want that number you could do a compare in a trigger and write the count out (or maybe get it back directly, havent tried) to a table.
Andy
July 17, 2002 at 10:20 pm
The problem is not knowing how many records were updated, the problem is if data was actually changed in the users record. They could bring up the form with their information and press the "Update" button instead of "Cancel" and my code will update the record with the information in the form. I want to know if any data was actually changed. In affect, I'd like SQL to compare the entire record after the update to the record before the update.
July 17, 2002 at 10:48 pm
Have to ask - why do you care? If you just want to send the minimum data over you can let ADO do the work for you with a recordset, just adjust the updatecriteria.
Andy
July 18, 2002 at 6:12 am
One possible way you could handle this would be a trigger. Just a thought but you could try something like.
CREATE TRIGGER tr_CheckChanges
FOR UPDATE
AS
IF NOT EXISTS (SELECT * FROM inserted i INNER JOIN deleted d ON i.PKFld = d.PKFld ... WHERE i.col1 != d.col1 OR i.col2 != d.col2...)
BEGIN
ROLLBACK TRANSACTION
RAISERROR('No changes detected',11,-1)
RETURN
END
This should save a bit of table access and allow you to catch no changes without need to recode. I haven't tested yet but will when I get a chance.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply