May 14, 2013 at 1:37 pm
Hi,
My Problem is that Data-Updates are done via an .asp-Application which uses a Stored Procedure to update Records in our SS 2008 DB when a User hits the Save-Button on an Edit-Page, and this Stored Procedure passes all Values as Updated, but I need a particular Field to be checked for Value Changes and only be updated when the value actually changes, as there is an Update-Trigger on it, that fires all the times this Stored Procedure is executed ... the trigger works fine from within the DB, it's just the sproc that kinda messes it up and I don't know how to change it.
Here's the code of the Stored Procedure (I took some fields out for readability):
CREATE PROCEDURE [dbo].[pALPHACustomerUpdate]
@pk_CU_ID int,
@p_Resort int,
@p_RoomNo int,
@p_DepartDate datetime,
@p_Paid_Accomm bit,
@p_prevConValue nvarchar(4000),
@p_force_update char(1)
AS
DECLARE
@l_newValue nvarchar(4000),
@return_status int,
@l_rowcount int
BEGIN
IF NOT EXISTS (SELECT * FROM [dbo].[Customer] WHERE [CU_ID] = @pk_CU_ID)
RAISERROR ('Concurrency Error: The record has been deleted by another user. Table [dbo].[Customer]', 16, 1)
IF (@p_force_update = 'Y')
BEGIN
UPDATE [dbo].[Customer]
SET
[Resort] = @p_Resort,
[RoomNo] = @p_RoomNo,
[DepartDate] = @p_DepartDate,
[Paid_Accomm] = @p_Paid_Accomm
WHERE [CU_ID] = @pk_CU_ID
SET @l_rowcount = @@ROWCOUNT
IF @l_rowcount = 0
RAISERROR ('The record cannot be updated.', 16, 1)
IF @l_rowcount > 1
RAISERROR ('duplicate object instances.', 16, 1)
END
ELSE
BEGIN
Select @l_newValue = CAST(BINARY_CHECKSUM([CU_ID],[Resort],[RoomNo],[DepartDate],[Paid_Accomm]) AS nvarchar(4000))
FROM [dbo].[Customer] with (rowlock, holdlock)
WHERE [CU_ID] = @pk_CU_ID
IF (@p_prevConValue = @l_newValue)
SET @return_status = 0 -- pass
ElSE
SET @return_status = 1 -- fail
IF (@return_status = 0)
BEGIN
UPDATE [dbo].[Customer]
SET
[Resort] = @p_Resort,
[RoomNo] = @p_RoomNo,
[DepartDate] = @p_DepartDate,
[Paid_Accomm] = @p_Paid_Accomm
WHERE [CU_ID] = @pk_CU_ID
SET @l_rowcount = @@ROWCOUNT
IF @l_rowcount = 0
RAISERROR ('The record cannot be updated.', 16, 1)
IF @l_rowcount > 1
RAISERROR ('duplicate object instances.', 16, 1)
END
ELSE
RAISERROR ('Concurrency Error: The record has been updated by another user. Table [dbo].[Customer]', 16, 1)
END
END
GO
This updates all Field Values, even if the data hasn't changed, which then causes my Trigger to fire ...
I tried to change the behavior of that SProc by adding either ISNULL or COALESCE in the SET Statement, but the Outcome is the same ...
[RoomNo] = COALESCE(@p_RoomNo,RoomNo),
or:
[RoomNo] = ISNULL(@p_RoomNo,RoomNo),
any help is appreciated to be able to update only values that are changed within this Stored Procedure ...
May 14, 2013 at 2:42 pm
The only way to fix this in the SP is to check the value of the column you care about and not include that column in the update if it hasn't changed.
An update trigger fires for the entire update and included all the columns that are included in the update statement. So if you do:
Update dbo.person
Set middleName = middleName
As far as the trigger is concerned the middleName column IS being updated. In a trigger you can do a check like:
IF EXISTS(Select 1 from inserted as I JOIN deleted as D on I.primaryKey = D.primaryKey and I.middleName = D.middleName)
Begin;
PRINT 'Middle Name was not really updated in at least one row';
End;
ELSE
Begin;
PRINT 'Middle Name was really updated';
End;
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 14, 2013 at 3:14 pm
The only way to fix this in the SP is to check the value of the column you care about and not include that column in the update if it hasn't changed.
That was exactely my question: how can I alter the stored procedure to NOT include that column if the value of the field hasn't changed ...
My Trigger works fine with the IF UPDATE (RoomNo) Statement as long as the Stored Procedure is not executed ...
So if I alter a row in the Customer-Table and I don't change the RoomNo Field ... the Trigger won't fire.
Unfortunately the Stored Procedure is being called from FrontEnd-Application to update all Fields for a particular row, that's what I would like to prevent for that particular column.
I am very grateful for your reply, but I would love to get a better idea as to how change my Stored Procedure based on the information I provided (which I think is enough) ... do I need to declare another @Room and do the Select within that part or is there an easier way like within the Set / Where part of the procedure?
Some code would be appreciated 🙂 Thanks for your help already! ... 🙂
May 14, 2013 at 3:25 pm
Get the value of room# in a variable based on the PK of the table. Compare that with the Vale passed the SP and then write your logic based on if value is matching or not.
May 14, 2013 at 3:32 pm
Maybe you can split this into 2 updates?
Something like this:
UPDATE [dbo].[Customer]
SET
[Resort] = @p_Resort,
[RoomNo] = @p_RoomNo,
[DepartDate] = @p_DepartDate,
[Paid_Accomm] = @p_Paid_Accomm
WHERE [CU_ID] = @pk_CU_ID
and [RoomNo] <> @p_RoomNo
SET @l_rowcount = @@ROWCOUNT
IF @l_rowcount = 0
UPDATE [dbo].[Customer]
SET
[Resort] = @p_Resort,
[RoomNo] = @p_RoomNo,
[DepartDate] = @p_DepartDate,
[Paid_Accomm] = @p_Paid_Accomm
WHERE [CU_ID] = @pk_CU_ID
SET @l_rowcount = @l_rowcount + @@ROWCOUNT--this will increment in case both updates find a row to update
IF @l_rowcount = 0
RAISERROR ('The record cannot be updated.', 16, 1)
IF @l_rowcount > 1
RAISERROR ('duplicate object instances.', 16, 1)
Without setting everything up this seems like it would work.
It would be better to change the trigger or you will have to do this type of thing everyplace you need to update this table.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 15, 2013 at 5:09 am
Hi Sean et. al,
What a waste of time that was 🙂 After many fruitless attempts to change the Stored Procedure, which either ended in not updating the desired Field anymore at all or updating it all times I came to the Final Conclusion, that this is impractical anyways ... with a further look into the Application Design - it recreates the Stored Procedures everytime an update is build - lol - that would have been some constant work for me with that solution ...
It would be better to change the trigger or you will have to do this type of thing everyplace you need to update this table.
And yes, it was actually pretty simple in the end - seeing that the if update(ColumnName) doesn't really check if a Value has actually changed, but merely if there's an Update query (in this case from the Stored Procedure) initialized, it couldn't work without joining Inserted and Deleted ... so I added the extra join in my Trigger and it works 🙂
join Deleted Del
ON INS.[CU_ID] = DEL.[CU_ID]
WHERE INS.[RoomNo] <> DEL.[RoomNo]
I hate Triggers in a way, they are really hard to understand and look pretty complicated if I would have to maintain more than a few columns to trigger some actions.
Thought I post this anyways, maybe someone else stumbles upon the same problem and looks for a solution 🙂
Thanks for your help ... now I am back to my Constraints 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply