How to enforce a one-row table

Question

How to enforce a one-row table

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Fred Wahlquist SSC Eights! Points: 874 More actions · Answer 1

It's not clear to me in BOL what the syntax is for a BEFORE trigger. I shouldn't need to do the rollback if I do a before trigger.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

You're trigger would look something like this if you do my final idea

CREATE TRIGGER [trTest_IO_Insert] ON [dbo].[Test]

INSTEAD OF INSERT

AS

SET NOCOUNT ON

--makes sure that the table is empty and that only 1 line is trying to be inserted in the transaction ((0) + (1)) = 1

if (select count(*) from dbo.test) + (Select count(*) from Inserted) = 1

begin

Insert into dbo.Test (PkTest, etc) Select PkTest, etc from Inserted

end

else

begin

--no need to rollback

raiserror ('This table can only contain 1 row...........', 13, 1)

end

Fred Wahlquist SSC Eights! Points: 874 More actions · Answer 3

Remi,

That really tightens it up doesn't it. Thanks again, and thanks to all who have helped with their input.

Fred

Steven Hadfield SSC Enthusiast Points: 191 More actions · Answer 4

Another way of acheiving your objective would be to insert 1 row when the table is created, then deny insert and delete permissions and allow updates.

This would allow for the updating of the session information without any chance of multiple rows appearing.

Steve

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

A similar suggestion had already come up but the prospect of a custom error message was appreciated. But your point still stands that updates are not denied with this script so there's no need to delete and reinsert the data.. a simple update will do just that.

Phil Parkin SSC Guru Points: 246940 More actions · Answer 6

Why not just create a check constraint on the primary key (eg ID = 1). PK is NOT an identity field, so repeated insert/delete should be no problem.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004279 More actions · Answer 7

Great idea but I think they really want a custom error message and a check constraint won't do it. Ran into the same problem with permissions... no custom error message.

Switching gears...

Remi knew what I meant when I said a "before" trigger... it's really called an "Instead of" trigger in BOL which, unlike the standard or "after" trigger types, is fired "before" the data is actually inserted into the table.

--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)