How do I make sure only one record is the current issue?

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?

You can actually make the change to the remaining records if you used a Trigger.

|||

Thanks for the response. Will you explain in more details? What do you mean by using Trigger? I'm quite new to SQL Server. I don't use it extensively.

|||

A trigger is an object contained within an Sql Server database that is used to execute a batch of SLQ code whenver a specific event occurs such as an UPDATE or INSERT. They can be defined to execute in place of or after data modifications. Therefore you could use an AFTER trigger when inserting a new record to change the 'current issue' flag of all other records. I don't recommend there usage very often because they are hard to know they are there, but your situation might warrant one.

There are many good articles on how to write a trigger. Here's a few:

http://www.sql-server-performance.com/nn_triggers.asp

http://www.codeproject.com/database/SquaredRomis.asp

http://msdn2.microsoft.com/en-us/library/aa258254(SQL.80).aspx

Good luck!

|||

Many thanks for the help. I'll give those articles some reading.

|||

Okay, so I would put the Trigger in the same store procedure as the Insert or Update store procedure, correct?

|||

No, it's written seperately as an Action against a certain table.

CREATE TRIGGER reminder
ON titles
FOR INSERT, UPDATE

'Reminder' is the name of the trigger

'titles' is the name of the table

"FOR INSERT, UPDATE" is the action

Make sure and look at that last link I sent you as far as the details are concerned.

|||

Okay, so the Trigger is created in its own separate store procedure. How about the insert or update query? Does it has to be a store procedure for the trigger to work? Right now many of my insert and update quries are from code behind at run time. And one more thing, although the Trigger is written as a store procedure but I do not have to call it, correct? It will automatically detect any upates or insert, right?

|||

You're correct, the Trigger will just run when one of these actions on the table has ocurred. Your insert code happen by stored procedure or by manually inserting a row into the table. It doesn't matter how the Insert happens, it just knows to run when one does occur.

|||

Thanks so much! I'll give Trigger a try now.