October 26, 2016 at 2:02 pm
Hi,
I have a table with many columns . I can insert the records in this table but cannot update few fields. It does not throw any error and message is "1 row updated". When I check the fields it remain unchanged. Any idea if there could be any lock ? if yes, how do i check this? For example, I like to change Createid and updateid in my table . somehow updateid is changed to current session loginid and createid remains unchanged. any help is appreciated!
October 26, 2016 at 2:06 pm
Most likely an update trigger on the table. I'd check that first.
Cheers!
October 26, 2016 at 2:26 pm
Hi,
There is update trigger and it has something like below code,
UPDATE [dbo].
SET
.[CreateId] = ISNULL(deleted.[CreateId],
.[CreateId]),
.[CreateDate] = ISNULL(deleted.[CreateDate],
.[CreateDate]),
.[LastUpdate] = GETDATE(),
.[UpdateId] = SUSER_SNAME()
FROM [dbo].
JOIN inserted
ON (inserted.[tableId] =
.[tableId])
LEFT JOIN deleted
ON (deleted.[tableId] =
.[tableId])
WHERE (deleted.[CreateId] IS NOT NULL AND inserted.[CreateId] != deleted.[CreateId])
OR (deleted.[CreateDate] IS NOT NULL AND inserted.[CreateDate] != deleted.[CreateDate])
OR (NOT UPDATE([LastUpdate]))
OR (inserted.[LastUpdate] NOT BETWEEN @LowDate AND @HighDate)
OR (NOT UPDATE([UpdateId]))
OR (inserted.[UpdateId] != SUSER_SNAME())]
does this mean i cannot modify the createid field?
October 26, 2016 at 2:48 pm
It means somebody wanted to make sure that the createid stayed the original and that the updateid was the last person to update it.
That's fairly reasonable, and there could be stringent requirements around that.
Technically you could make sure your updates stuck by disabling the trigger, but again, the trigger is likely there to enforce some business rules or fulfill some sort of auditing function.
Unless you have both a very good reason to manually change the values and the explicit blessing of all the data owners and other relevant powers that be, I'd leave that alone.
Why are you wanting to manually change those values?
Cheers!
October 26, 2016 at 2:54 pm
thank you !!
This makes sense!
Well, client have a web service which inserts the values and lot of bugs with webservice and wrong value is brrn assigned to create ID. and client wants to change them to actual field .
October 26, 2016 at 3:12 pm
hegdesuchi (10/26/2016)
thank you !!This makes sense!
Well, client have a web service which inserts the values and lot of bugs with webservice and wrong value is brrn assigned to create ID. and client wants to change them to actual field .
It sounds like the right thing to do is to fix the web service to work properly. Then you won't keep accumulating bad data.
Like Jacob pointed out, make sure you have the approval of the business owners and your boss before overriding the trigger.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply