October 31, 2008 at 11:10 am
I need to check if data already exist in a column \(record), if data is present i don't want to allow update. If data not present than allow update.
I'm trying to validate some fields before allowing the record to be updated.
I used a instead trigger, but the trigger locks down whole table for update.
thanks,
October 31, 2008 at 1:29 pm
Can you post the trigger code? It's hard to debug/tune something you can't see.
I'm also not sure what you mean. Do you mean that if someone tries to update the data column MiddleName for Jack Corbett you do not want to allow an update if there is already data in the Column for Jack Corbett? Or do you mean you only want to allow unique values in a column for the entire table?
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
November 5, 2008 at 8:39 am
Also, if you are getting the table locked up, it could be that your query within the trigger has decided that it needs to issue a table lock. So, take some time to review the query outside of the trigger, and even review the query plan. And make sure you have the correct indexes on the table to support the query.
- note that sense the trigger is referencing the inserted and/or deleted tables, you most likely will have to make some adjustments to the query to get it to work outside of a trigger.
The more you are prepared, the less you need it.
November 5, 2008 at 2:44 pm
Do you mean that if someone tries to update the data column MiddleName for Jack Corbett you do not want to allow an update if there is already data in the Column for Jack Corbett?
Yes, this is just what is i am looking for.
CREATE TRIGGER [TrgName]
ON [dbo].[Table]
FOR UPDATE
AS
IF UPDATE(Column name)
BEGIN
ROLLBACK TRAN
END
This trigger works, It stops the update, but if the column is empty or has a null value, i don't want the trigger to stop the update.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply