How to enforce a one-row table
-
What's the recommended method to enforce a rule that a table only contain one row?
Is there a downside to creating a before-insert trigger that deletes any rows in the table?
Thanks,
Fred
-
Well it's one of 2 ways.
In the instead of insert trigger, you simply don't insert the rows in the table and return an error message saying no row can be inserted.
Or you can add an identity field in the table with a check constraint on it. Something in the lines of : My_ID = 1
That way no new line can get added and you don't need to code the trigger.
BTW in either case I think you'd have to add an instead of delete so that the said line can't be deleted either.
-
I'd go with Remy's first suggestion.
-
Steve, why do you preffer my first option?
Is it because you can send back an error message or because you have less code to maintain, maybe some other reason?
-
I actually thaught over my answer and realised you could simply add 1 trigger (instead of 2) like so :
CREATE TRIGGER [trTest_IO_IUD] ON [dbo].[Test]
INSTEAD OF INSERT, DELETE
AS
SET NOCOUNT ON
raiserror('The table test cannot accept new rows nor deletes (trTest_IO_IUD)',13,1)
So I would also suggest my first answer as best option since you are returning an error message as well to the client and have basically no code to maintain.
Please note that if you have a foreign key in your table with cascade deletes ON you won't be able to use the instead of delete trigger. But I'm pretty sure it's not gonna be a problem in your case.
-
This database is on a test system, one of many which send their data to a central server. When a test is started it gets a unique sessionID from the central server. This table where I want only one row is a parent to many other tables ( which contain the test data of course) all referenced by the sessionID.
When the test first starts one row is inserted into the parent ( sessionID, startDate, finishdate, user, etc. ) table. After the test finishes all data is sent to the central server and then all data is deleted from the local test system.
I'd like to use the cascade delete to make it simple to delete just the one row and all other data in other tables is deleted. So that's why I'd like to be able to delete the row in this table. Given that I'd like to make sure that only one row can be inserted which makes it easy for anyone else writing code that needs to determine the current SessionID; they can just query this one row and always get the current SessionID without passing it around.
If this is sound thinking then the check constraint to make sure the identity column ("My_ID") is always 1 doesn't work since it will be changing with new inserts.
I hope this isn't to naive, but is it feasible to create a check constraint or trigger that only allows an insert if the table is empty? Returning an error message to the caller would be a nice touch. This would allow me to make full use of the cascading deletes.
Attempting to meet the idea of Occam's Razor is never quite as simple as we'd like it, is it?
Thanks to all again,
Fred
-
You could use a simple after insert trigger then.
Create trigger ....
FOR INSERT
AS
SET NOCOUNT ON
if select count(*) from MyTable = 1
begin
rollback tran
raiserror ('can''t have more than one row in this table ...', 13,1)
end
-
Remi,
That works perfect.
Thank you.
-
All of that seems like a fair amout of work to keep a table with only 1 row... why not just log in as the "sa" and revoke insert, update, and delete permissions from everyone? Trust me that if someone tries to do any of the 3 after that, they will get an error message (although it won't be a custom error message).
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanx Jeff for the input. It shows that there's always many different ways to achieve the same result with SQL server. But even with this new solution I'd keep the trigger since you can send a custom message to the user. Makes understanding the situation a lot easier when you don't know the system first hand.
Maybe the best of all ways would be the instead of insert trigger. That way you could avoid inserting the data then rolling back (which is a lot of work for absolutely nothing with a large set). But I wouldn't expect you to have a huge transactions made on that table to have conserns about performance... but then again you never know.
-
Put the customer error message in the App?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
No just for any developper that tries to insert in that table... The end user doesn't need to hear about this.
-
I'd think that the "permission denied" message would drive the point home to a developer but, I agree, it might save a couple of trouble calls to the DBA's.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I guess it all depends on the documentation system in place... but a well placed "this table can only have 1 row at the time for x reason" can save a lot of trouble, especially a few months after it's been created and forgotten.
-
Like I said, I agree especially in light of that fact that many companies just don't have a documentation system in place. Documentation always seems to take it in the socks and even when it is available, it is frequently not maintained and might not be used by "Developers in a hurry" at all. Because of this fact, I always "over-document" code within the code and a trigger of this nature would do the job very well.
I also agree that the "Before" trigger would be the best solution in this case. Once the trigger is in place, there is zero additional maintenance, no permissions to worry about, feed back as to "what and why" is instantaneous across all apps and direct insert attempts, and someone would really have to go out of their way to insert a row. If the DBA also limited "DDL Admin" permissions on the table, even a dbo couldn't disable or change the trigger.
The trigger idea is absolutely the best idea in this case. Good idea
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply