Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Table constraints Expand / Collapse
Author
Message
Posted Monday, September 13, 2010 2:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 25, 2013 8:05 AM
Points: 4, Visits: 13
I am trying to update records in a table that had a check constraint to set the table to read only. That constraint was removed (dropped) and it seems that now I can't update any record that was put in while the constraint was on the table. I can modify any record that is written to the table after the constraint was removed. Let me further clarify this. I can go into Mgmt studio and update any record in this table, but using a program, written in Delphi, using dataset components....it won't work on any record that existed prior to the constraint being removed. I am wondering if there is anything that is lingering on the pre-removal records?
Post #985087
Posted Monday, September 13, 2010 3:53 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, May 27, 2013 3:39 PM
Points: 31, Visits: 116
mfayard (9/13/2010)
I am trying to update records in a table that had a check constraint to set the table to read only. That constraint was removed (dropped) and it seems that now I can't update any record that was put in while the constraint was on the table. I can modify any record that is written to the table after the constraint was removed. Let me further clarify this. I can go into Mgmt studio and update any record in this table, but using a program, written in Delphi, using dataset components....it won't work on any record that existed prior to the constraint being removed. I am wondering if there is anything that is lingering on the pre-removal records?


Hi you say the table was read only, but then you you say you cant update any records that were added while the table was read only (how did you rows get there?). How was your check constraint written?


MCITP SQL Server 2005/2008 DBA/DBD
Post #985120
Posted Tuesday, September 14, 2010 10:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 25, 2013 8:05 AM
Points: 4, Visits: 13
The process that used the table would drop the constraint, then add the records needed, then add the constraint back. Following is the code to add the constraint and to drop it.

Drop Constraint -

ALTER TABLE [dbo].[BranchFundType] WITH NOCHECK ADD CONSTRAINT [chk_read_only] CHECK (((1)=(0)))
ALTER TABLE [dbo].[BranchFundType] CHECK CONSTRAINT [chk_read_only]Show Bart Talley (dev1) added a comment - 11/Aug/10 11:22 AM Constraint has been removed see code below. --remove
IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[chk_read_only]') AND parent_object_id = OBJECT_ID(N'[dbo].[BranchFundType]'))
ALTER TABLE [dbo].[BranchFundType] DROP CONSTRAINT [chk_read_only]

-- add constraint
ALTER TABLE [dbo].[BranchFundType] WITH NOCHECK ADD CONSTRAINT [chk_read_only] CHECK (((1)=(0)))
ALTER TABLE [dbo].[BranchFundType] CHECK CONSTRAINT [chk_read_only]
Post #985673
Posted Tuesday, September 14, 2010 10:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:22 PM
Points: 12,890, Visits: 31,849
interesting use of a check constraint to make a table read only;

could there be a trigger on the table that is also preventing your modifications? depending on the loging in such a trigger, it might explain your situation.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #985695
Posted Tuesday, September 14, 2010 11:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, May 27, 2013 3:39 PM
Points: 31, Visits: 116
Hi

I doubt the inability to update this table has anything to do with the check constraint (if it has been dropped). What exception message do you get when you try to update a row? do you get the same message if you do it? does the Delphi application have update permissions?

(FYI permissions of this sort applied directly to tables is generally considered to be bad practice, you should use stored procedures )


MCITP SQL Server 2005/2008 DBA/DBD
Post #985721
Posted Wednesday, September 15, 2010 8:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 25, 2013 8:05 AM
Points: 4, Visits: 13
There is no trigger on this table. The delphi app has permissions to update the table. I can insert any record into the table. The app can update any record that it inserts. It cannot, however, update any record that it does not insert. It does not give an exception or error message. It just does not do the update.
Post #986280
Posted Wednesday, September 15, 2010 8:12 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
mfayard (9/15/2010)
It cannot, however, update any record that it does not insert. It does not give an exception or error message. It just does not do the update.

Does the transaction completes?
Would you mind in tracing such scenario?


_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Post #986285
Posted Wednesday, September 15, 2010 8:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 25, 2013 8:05 AM
Points: 4, Visits: 13
Yes. It completes have put breakpoints in the code and traced it. All fields are being loaded and the dataset component is in edit mode. It then does a post with all data elements populated with new values. No errors, messages of any type. I also did this for new records and the code traces (steps) through the same code. So there is no deviation in the steps when it does the update successfully or not.
Post #986293
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse