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.

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:

CREATE TRIGGER tgrOLissue

ON magIssue

FOR INSERT,UPDATE

AS

IF EXISTS (SELECT * FROM magIssue Where crrntIssue = 0x1)

Update magIssue

SET crrntIssue = False

FROM magIssue

INNER JOIN 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

sql

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:

CREATE TRIGGER tgrOLissue

ON magIssue

FOR INSERT,UPDATE

AS

IF EXISTS (SELECT * FROM magIssue Where crrntIssue = 0x1)

Update magIssue

SET crrntIssue = False

FROM magIssue

INNER JOIN 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

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

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:

CREATE TRIGGER tgrOLissue

ON magIssue

FOR INSERT,UPDATE

AS

IF EXISTS (SELECT * FROM magIssue Where crrntIssue = 0x1)

Update magIssue

SET crrntIssue = False

FROM magIssue

INNER JOIN 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

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:

CREATE TRIGGER tgrOLissue

ON magIssue

FOR INSERT,UPDATE

AS

IF EXISTS (SELECT * FROM magIssue Where crrntIssue = 0x1)

Update magIssue

SET crrntIssue = False

FROM magIssue

INNER JOIN 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

how do i get the returned id from the store proceedure in asp c#?

my store proceedure gets the id:

CREATE PROCEDURE createpost(
@.userID integer,
@.categoryID integer,
@.title varchar(100),
@.newsdate datetime,
@.story varchar(250),
@.wordcount int
)
as
DECLARE @.newNewsID integer

Insert Into TB_News(UserID, CategoryID, title, newsdate, StoryText, wordcount)
Values (@.userID, @.categoryID, @.title, @.newsdate, @.story, @.wordcount)

SELECT @.newNewsID = @.@.IDENTITY

then im calling it in the asp:

con = new SqlConnection ("server=declt; uid=c1400046; pwd=c1400046; database=c1400046");
con.Open();

cmdselect = new SqlCommand("createpost", con);
cmdselect.CommandType = CommandType.StoredProcedure;

cmdselect.Parameters.Add("@.userID", userID);
cmdselect.Parameters.Add("@.categoryID", categoryID );
cmdselect.Parameters.Add("@.title", title.Text );
cmdselect.Parameters.Add("@.newsdate", newsdate.Text );
cmdselect.Parameters.Add("@.story", story.Text );
cmdselect.Parameters.Add("@.wordcount", "1" );


int valueinserted = cmdselect.ExecuteNonQuery();


Response.Redirect("http://declt/websites/c1400046/newpicture.aspx?id="+valueinserted);

con.Close();

as you can see im using the valueinserted but thats just returning 1, but im guessing that means it sucessful. but i want the id of the new record! any idea how ?

Hi,

you need to either specify a RETURN or make the @.newNewsID an output parameter.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconinputoutputparametersreturnvalues.asp

|||

Or use the SqlDataReader object like this:

1CREATE PROCEDURE createpost(2 @.userID integer,3 @.categoryID integer,4 @.titlevarchar(100),5 @.newsdatedatetime,6 @.storyvarchar(250),7 @.wordcountint8)9as1011Insert Into TB_News(UserID, CategoryID, title, newsdate, StoryText, wordcount)12Values (@.userID, @.categoryID, @.title, @.newsdate, @.story, @.wordcount)1314SELECT@.@.IDENTITY

and in  code:2con =new SqlConnection ("server=declt; uid=c1400046; pwd=c1400046; database=c1400046");3int valueinserted = 0;45try6 cmdselect =new SqlCommand("createpost", con);7 cmdselect.CommandType = CommandType.StoredProcedure;89 cmdselect.Parameters.Add("@.userID", userID);10 cmdselect.Parameters.Add("@.categoryID", categoryID );11 cmdselect.Parameters.Add("@.title", title.Text );12 cmdselect.Parameters.Add("@.newsdate", newsdate.Text );13 cmdselect.Parameters.Add("@.story", story.Text );14 cmdselect.Parameters.Add("@.wordcount","1" );1516 cmdselect.Connection.Open();17 SqlDataReader reader = command.ExecuteReader();18if( reader.HasRows() )19 {20 reader.Read();21 valueinserted = Convert.ToInt32( reader[0] );22 }23catch( Exception ex)24{25//handle the exception somehow26}27finally28{29if( con !=null and conn.State != ConnectionState.Closed )30 con.Close();31}3233Response.Redirect("http://declt/websites/c1400046/newpicture.aspx?id="+valueinserted);

sql

How do I escape ampersands in stored procedure?

I am using the following stored procedure to gather information from one of my tables:
CREATE PROCEDURE usr_GetCustomerNumber
@.custName varchar(20)
AS
SELECT CUNO FROM CIPNAME0
WHERE CUNM LIKE @.custName
GO
The problem I am facing is that some of the customers have ampersands (&) in their names, i.e. A & M Auto Supply. When I feed in anything with an ampersand to @.custName, procedure doesn't return any information. I am using the following code to call the stored procedure:
Private Sub btnSelect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSelect.Click
Dim custSelected As String
Dim custNum As String
Dim objDataSet As New DataSet
Dim objCmd As New SqlCommand("usr_GetCustomerNumber", objConn)
Dim objReader As SqlDataReader
Try
lblStatus.Text = ""
custSelected = lstResults.SelectedItem.Text
objCmd.CommandType = CommandType.StoredProcedure
objCmd.Parameters.Add("@.custName", custSelected)
objConn.Open()
objReader = objCmd.ExecuteReader
While objReader.Read
custNum = objReader("CUNO")
End While
objReader.Close()
objConn.Close()
Catch ex As SqlException
lblStatus.ForeColor = Color.Red
lblStatus.Text = ex.Message
End Try
lblStatus.Text = custNum
End Sub
I am trying to determine exactly how I would escape the & and still get the desired results. Should I search the custSelected string for the & and try to escape it before it gets to the stored procedure? I have no clue.
Thanks!
Ariston Collander
ariston@.coxcomputer.comAmpersand character does not have any special meaning in LIKE pattern. Only characters %, _, [, ], ^ has special meaning. Your C# code looks find to me. The ampersand character might be getting encoded before reaching your click routine. You can verify it by either setting a break point in the C# code and looking at the value or running a SQL Profiler trace to see the executed SP call with the parameter values.

How do i do ?

CREATE PROCEDURE SP_Login
(
@.Email Varchar(60) = '',
@.Password Varchar(100) = ''
)
As
Declare @.UserEmail Int
Set Nocount On
If not exists( Select CdUser From User Where Email = @.Email And Password =
@.Password)
Begin
Raiserror(13001,18,1) --User not found
End
it should continues with a else to get the CdUser...I tryed:
Else
Begin
Set @.UserEmail = Select CdUser From User Where Email = @.Email
End
I'm trying to accomplish this, but doesn' work, What is the way ?You need a return statement to stop execution of the SP in the fiirst case..
.
and just setting the @.UserEmail variable does nopt return it to the client,
if that's what you're trying to do. Also, you can return different error
messages if the user exists and the password is wrong, if you want...
CREATE PROCEDURE SP_Login
@.Email Varchar(60) = '',
@.Password Varchar(100) = ''
As
Set Nocount On
Declare @.Err Integer
Declare @.Msg VarChar(500)
Set @.Msg = 'User: '+ @.Email + ' not found.'
If not Exists
(Select * From User
Where Email = @.Email)
Goto ErrHandler
-- ---
Set @.Msg = 'Incorrect password for User: '+ @.Email
If Not Exists
(Select * From User
Where Email = @.Email
And Password = @.Password)
Goto ErrHandler
-- --
-- Sp Only gets here if it passes
-- validation checks above
Select CdUser From User -- This returns CDUser to client
Where Email = @.Email
-- --
Return(0) -- This terminates SP processing
-- --
-- -- Rest only runs if error occored
ErrHandler:
Raiserror(@.Msg, 16,1)
Return(-1)|||Better not to raise an error at all - errors are for exceptional situations,
someone typing invalid login credentials is an expected situation. You could
return @.msg as an output param, look at the return value of the proc, or som
e
other method on the client rather than have to catch an exception.
KH
"CBretana" wrote:

> You need a return statement to stop execution of the SP in the fiirst case
..
> and just setting the @.UserEmail variable does nopt return it to the client
,
> if that's what you're trying to do. Also, you can return different error
> messages if the user exists and the password is wrong, if you want...
> CREATE PROCEDURE SP_Login
> @.Email Varchar(60) = '',
> @.Password Varchar(100) = ''
> As
> Set Nocount On
> Declare @.Err Integer
> Declare @.Msg VarChar(500)
>
> Set @.Msg = 'User: '+ @.Email + ' not found.'
> If not Exists
> (Select * From User
> Where Email = @.Email)
> Goto ErrHandler
> -- ---
> Set @.Msg = 'Incorrect password for User: '+ @.Email
> If Not Exists
> (Select * From User
> Where Email = @.Email
> And Password = @.Password)
> Goto ErrHandler
> -- --
> -- Sp Only gets here if it passes
> -- validation checks above
> Select CdUser From User -- This returns CDUser to client
> Where Email = @.Email
> -- --
> Return(0) -- This terminates SP processing
> -- --
> -- -- Rest only runs if error occored
> ErrHandler:
> Raiserror(@.Msg, 16,1)
> Return(-1)