How do I make sure only one record is checked?

I have a table of magazine issues. The table are defined as below:

issueID int Unchecked
name varchar(50) Unchecked
title varchar(100) Checked
description varchar(500) Checked
crntIssue bit Checked
archived bit Checked
navOrder int Checked
dateCreate datetime Checked

And here is what I want. Is there a way when inserting/updating or on the table itself to make sure that there is only one record that is marked as the current issue? The way I have it here in my table, any records can have the current issue (crntIssue) field checked. I only want one crntIssue field checked regardless of how many records or issues are in the table. If there is no way to automatically have SQL Server to manage that then that means I must check all the records before hand before the update/insert query, correct?

Hi Charlie,

Code Snippet

CREATE TRIGGER SOMETrigger
ON SomeTable
FOR INSERT,UPDATE
AS
IF EXISTS
(
SELECT * FROM SomeTable S
WHERE crntIssue = 0x1 AND --Select all current checked ones
NOT EXISTS
(
SELECT * FROM Inserted I
WHERE I.IssueId = S.IssueId --except the already existing one which is determined with the correlated subquery
)
)
RAISERROR('There is already an issue flagged as active',16,1)

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||Thanks so much for the help. Will you explain the NOT EXISTS section?|||Done. :-)

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Jens,

Many thanks for al your help so far. I didn't get a chance to implement it until now. Anway, what do you mean when you wrote "Inserted I"?

|||Inserted and deleted are tables which are available in the trigger context (and only there)

They are present in the following tables:

Update

Insert

Delete

Table Inserted

Containing the new values of the updated rows.

Containing the new values of the Inserted rows.

Table Deleted

Containing the old values of the updated rows.

Containing the deleted rows.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Okay, the more I thought about this the more I got confused. Here's the scenero.

In the Issue table I have four issues:

Summer Issue

Fall Issue

Winter Issue

Spring Issue

All of these issues have a crrntIssue field. Currently the Summer Issue has a true value in the crntIssue field and the rest of the issues have a false value in the crntIssue field. If later on, I decide to update the Issue page and make Fall as the current issue, I want the triger to automatically change the crntIssue field of Fall to true and the rest of issues crntIssue field to false. In the suggested trigger solution above, I don't see where the changes occur. In both cases of query, it's a select statement. So where is the update statement to make all the other issues crntIssue field false? And where is the statement to make the current issue's crntIssue field true?

|||

OK, I guess the problem was not stated clearly, so I assumed that you only want to check for wring entered values, not changing the flag automatically.

Code Snippet

CREATE TRIGGER SOMETrigger
ON SomeTable
FOR INSERT,UPDATE
AS
IF EXISTS (SELECT * FROM Inserted Where crrntIssue = 0x1)
Update SomeTable
SET crrntIssue = False
FROM SomeTable S1
INNER JOIN Inserted I
In I.IssueId = S.IssueId

WHERE I.crrntIssue = S.True

AND S.IssueId != I.crrntIssue --except the already existing one which is determined with the correlated subquery

That should be pretty much of it (did not check wheter syntax or compiling)

Jens K. Suessmeyer

http://www.sqlserver2005.de|||

Thanks so much for your patience.

Okay, so in your code above, you have two tables involved or just one table (SomeTable)? It seems to me that you have two tables (SomeTable, Inserted) and then I'm not sure what the "S" and "I" stand for. In my scenero (I'm not sure if I even doing this right), it only involve one table (magIssue). So here's what I'm thinking.

If there is an update/insert of magazine issue, check to see if the insert/update query changes the existing crntIssue field to some other issue, if not, leave it alone. If the insert/update query changes the crntIssue of let's say Summer to Fall, then go ahead and make other issues' crntIssue field in the magIssue table false and the crntIssue field of Fall true.

Sorry for my poor explanation.

|||

Sometable was just a sample. In my example I avoid using the same names to make the samples more educational as the posters need to convert it to their environment to manifest the used technolgoy while adopting the sample to their situation:

Code Snippet

CREATE TRIGGER TRG_INS_UPD_magIssue
ON SomeTable
FOR INSERT,UPDATE
AS
IF EXISTS (SELECT * FROM Inserted Where crrntIssue = 0x1)
Update magIssue
SET crrntIssue = False
FROM magIssue S1
INNER JOIN Inserted I
On I.IssueId = S.IssueId

WHERE I.crrntIssue = S.True

AND S.IssueId != I.crrntIssue --except the already existing one which is determined with the correlated subquery

The S and I are just aliases for the used tables. You will need the inserted table (which is only virtual within the trigger) to know if and which values changed during the inserted / update.

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

Sorry to bother again. I tried this:

CREATETRIGGER tgrOLissue

ON magIssue

FORINSERT,UPDATE

AS

IFEXISTS(SELECT*FROM magIssue Where crrntIssue = 0x1)

Update magIssue

SET crrntIssue = False

FROM magIssue

INNERJOIN magIssue

In magIssue.IssueId = magIssue.IssueId

WHERE magIssue.crrntIssue = magIssue.True

AND S.IssueId != I.crrntIssue --except the already existing one which is determined with the correlated subquery

GO

I tried to parse in MS SQL Server Management Studio and here is the error I got:

Incorrect syntax near the keyword 'In'.

|||Try 'on' instead of 'in'. They are close together on the keyboard.
|||

Okay, this is what I have so far.

Code Snippet

CREATE TRIGGER tgrmagIssue
ON magIssue
FOR INSERT,UPDATE
AS
IF EXISTS (SELECT * FROM Inserted Where crntIssue = 0x1)
Update magIssue
SET crntIssue = False
FROM magIssue
INNER JOIN Inserted
ON magIssue.issueID = magIssue.issueID
WHERE magIssue.crntIssue = magIssue.True
AND magIssue.issueID != magIssue.crntIssue --except the already existing one which is determined with the correlated subquery

And the error is:

Invalid column name 'True'.

|||Sorry, the part should read:

WHERE magIssue.crntIssue = 0x1

But can you send over a complete list of values for one issue (summer, winter, spring and autuumn ? This would be to redefine the query written above.

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

Right now I don't have all the issues entered as I'm just starting to create the table. In addition, the issue name or title may change. However, here is what the magIssue table look like:

Colomn Name Data Type Allow Nulls issueID int Unchecked name varchar(50) Unchecked title varchar(100) Checked description varchar(500) Checked crntIssue bit Checked frntPage int Checked archived bit Checked navOrder int Checked dateCreate datetime Checked