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 make Newspaper Columns in RS

I have 3 columns on my report in a table region. I would like for these 3 cols to print across the page (landscape)width such that more data can fit on to a page - like using newspaper columns in word. How can I do this in reporting services? Thanks in advance for your help.

You can add columns to a report. However, they only show up in certain rendering extensions. In the PDF and TIFF rendering extensions. This means you will not see the column layout in Report Manager or when Previewing the reports in Report Designer. See this link:

http://msdn2.microsoft.com/en-us/library/ms155816.aspx

sql

How do I make it use my index?

Hello!

I have two tables

users and pictures.

table users have a clustered (PK) index on userid
table pictures have a clustered (PK) index on userid

when I do this query:

"select userid from pictures where userid=123"

then It will do a clustered index seek

But If I do any of those:

"select t2.userid from users t1 left join t2 on t1.userid = t2.userid"
or
"select (select userid from pictures where usedid = t1.userid) from users t1"

It will do a clustered index scan.

How can I force it to seek my index instead of scan?

Thanks!do you have some where clause at the end of the query? when not, then the plan created by sql server is perhaps really the best!
if the where clause shrinks the number of rows in t1 to a fraction of total table row count, you could use

option(loop join)

at the end of the query. this should solve your problem

How do I make Field_B and Field_G to look at Field_A?

My Current Table
Field_A Field_B Field_G
100 Memo Memo
-50 Memo Memo
200 Memo Memo
-100 Memo Memo
How do I make my Field_B and Field_G to Reference/Evaluate Field_A
For Example: If Field_A > 0 then Field_B and Field_G = Debit Memo
If Field_A < 0 then Field_B and Field_G = Credit Memo
I would like to see the result in this Format
Field_A Field_B Field_G
100 Debit Memo Debit Memo
-50 Credit Memo Credit Memo
200 Debit Memo Debit Memo
-100 Credit Memo Credit Memo
Thanks,
JohnYou can create calculated fields.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_structure_objects_v1_8i44.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"John" <John@.discussions.microsoft.com> wrote in message
news:01D9F330-83C3-4E1E-9C3F-002633E38129@.microsoft.com...
> My Current Table
> Field_A Field_B Field_G
> 100 Memo Memo
> -50 Memo Memo
> 200 Memo Memo
> -100 Memo Memo
> How do I make my Field_B and Field_G to Reference/Evaluate Field_A
> For Example: If Field_A > 0 then Field_B and Field_G = Debit Memo
> If Field_A < 0 then Field_B and Field_G = Credit Memo
> I would like to see the result in this Format
> Field_A Field_B Field_G
> 100 Debit Memo Debit Memo
> -50 Credit Memo Credit Memo
> 200 Debit Memo Debit Memo
> -100 Credit Memo Credit Memo
> Thanks,
> John

How do I make a field automatically get its values from another fi

Hi,
In my table I have a "PaymentDate" field which is used to store payment
schedules for our clients. I want to add a new field in the same table and
call it "UpdatedPaymentDate" field as most of the time our clients don't
stick to original payment schedules.
How do I make the default value of this new "UpdatedPaymentDate" field to be
the values in the "PaymentDate" field? Do I have to use a trigger for this?
Is there a way to do this without using triggers?
If necessary I'll post the table script but I don't it's necessary for this
simple question. Both fields are in the same table and their data type is
smalldatetime for both.
--
Thanks,
SamSam wrote:
> Hi,
> In my table I have a "PaymentDate" field which is used to store payment
> schedules for our clients. I want to add a new field in the same table and
> call it "UpdatedPaymentDate" field as most of the time our clients don't
> stick to original payment schedules.
> How do I make the default value of this new "UpdatedPaymentDate" field to
be
> the values in the "PaymentDate" field? Do I have to use a trigger for this
?
> Is there a way to do this without using triggers?
> If necessary I'll post the table script but I don't it's necessary for thi
s
> simple question. Both fields are in the same table and their data type is
> smalldatetime for both.
> --
> Thanks,
> Sam
I'm assuming you'll use stored procs for your inserts of course. So use
an optional parameter and assign the default in the proc:
CREATE TABLE dbo.PaymentSchedule (PaymentDate SMALLDATETIME NOT NULL,
UpdatedPaymentDate SMALLDATETIME NOT NULL /* ... key? */);
GO
CREATE PROCEDURE dbo.usp_PaymentScheduleInsert
(
@.PaymentDate SMALLDATETIME,
@.UpdatedPaymentDate SMALLDATETIME = NULL
)
AS
INSERT INTO dbo.PaymentSchedule (PaymentDate, UpdatedPaymentDate)
VALUES (@.PaymentDate, COALESCE(@.UpdatedPaymentDate,@.PaymentDat
e));
GO
EXEC dbo.usp_PaymentScheduleInsert
@.PaymentDate = '2006-04-30T00:00:00.000' ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

How do I make a field automatically get its values from anothe

David,
Thanks for your response. We're using an Access Form as a front-end to enter
data into this SQL Server table. So I'm not really using a storedproc to
enter data. Would using a trigger the only way to handle this then?
Thanks,
Sam
"David Portas" wrote:

> Sam wrote:
> I'm assuming you'll use stored procs for your inserts of course. So use
> an optional parameter and assign the default in the proc:
> CREATE TABLE dbo.PaymentSchedule (PaymentDate SMALLDATETIME NOT NULL,
> UpdatedPaymentDate SMALLDATETIME NOT NULL /* ... key? */);
> GO
> CREATE PROCEDURE dbo.usp_PaymentScheduleInsert
> (
> @.PaymentDate SMALLDATETIME,
> @.UpdatedPaymentDate SMALLDATETIME = NULL
> )
> AS
> INSERT INTO dbo.PaymentSchedule (PaymentDate, UpdatedPaymentDate)
> VALUES (@.PaymentDate, COALESCE(@.UpdatedPaymentDate,@.PaymentDat
e));
> GO
> EXEC dbo.usp_PaymentScheduleInsert
> @.PaymentDate = '2006-04-30T00:00:00.000' ;
>
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>Sam (Sam@.discussions.microsoft.com) writes:
> Thanks for your response. We're using an Access Form as a front-end to
> enter data into this SQL Server table. So I'm not really using a
> storedproc to enter data. Would using a trigger the only way to handle
> this then?
Yes, but I guess David's hint is that you should start using stored
procedures.
The trigger would look like:
CREATE TRIGGER sams_trigger ON tbl FOR INSERT AS
UPDATE tbl
SET updatedpaymentdate = i.paymentdate
FROM tbl
JOIN inserted i ON tbl.pkcol = i.pkcol
However, judging from the narrative, it seems to me that it would be
better to leave the column NULL. I'm assuming then that when the data is
entered, there has been no update to the payment date yet.
Then again, it was not clear to me whether this column is intended to
catch the date the client actually paid, or if this is a date agreeed-on
beforehand as the new date for the payment.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

How do i make a column using 3 other columns together within the same table

Hello all,

The example to this question is better:

ID PName Ppurchased PSold PSellPrice AvgSellprice
1 Water 50 10 100 20
2 Water 40 20 200 100
3 Water 70 35 50 25

What i want to happen within the table or maybe on a different table is to add the AvgSellprice of all 3 together to where it will look like this in a table:

ID PName Ppurchased PSold PSellPrice AvgSellprice TotalSellPrice
1 Water 50 10 100 20 145
2 Water 40 20 200 100 145
3 Water 70 35 50 25 145

Or maybe a new table which i want to look like this:

ID PName TotalSellPrice
1 Water 145

I know i can get the TotalSellPrice from the query:
SELECT SUM(AvgSellprice) AS TotalSellPrice
FROM tablename
WHERE PName='Water'

Also i am using MSSQL 2005 if this may differ. Thanks for any help.Also within this table there are other entries that do not pertain to the water. They have different names which may complicate things a little, not to sure as i don't know how to write the code for this table.

How do I make a column not nullable

This is what I would like to do...

1. Alter Table Status
Add ConsiderOpenFlag int null

2. UPDATE values...

3. Alter Table Status
Alter ConsiderOpenFlag int not null

Steps 1 and 2 are easy. What I cannot figure out is step three.

I don't want to have a default on that column, though I wouldn't mind adding it and then dropping it later if it would help.

Jonathan

The correct syntax is:

ALTER TABLE Status ALTER COLUMN ConsiderOpenFlag int not null

Best is probably to add the column as not null with default and then drop the default later. If there are large number of rows in the table then you may want to go with the UPDATE method and perform the UPDATE in batches to reduce the logging / locking resources.

sql

How do i make a autonumber field in my table!

Hi
im new to ms sql server, having previously used mysql. How do i make a auto number field? What datatype shall i use for it? like autonumber for mysql.

Ive tried setting my primary key field touniqueidentifier data type but then i still need to manually add a guid key in there. i want it so it automatically generates a unique key everytime i add a new row. is this possible?!

hope someone can help!
thanks
SQL Server uses IDENTITY for the auto number, in SQL Server it is a property to the column it is not a column, there are many IDENTITY in SQL Server what you need is the property. The Uniqueidentifier is different that uses a GUID which is a 16bytes Binary data type in SQL Server while IDENTITY is INT. Run a search for SET IDENTITY property in SQL Server BOL (books online). Hope this helps.|||thanks didnt know it was that easy!

how do I make 30 sec running query (select c1 sum(x) from t1 where c1 > 1000 group by c1) run

It seems when I run the query with the set staticts IO on then statistic reports back with the 'work table', and the query takes 30+ sec. if the worktable is ommited(whatever the reason?) the query take less 1 sec.

Here is my take, I believe work table is created in tempdb...and if not then whole query is using the cached page, am I right?

if I am right then the theory is, if I increase the (via sp_configure) server min memory setting and min query memory, the query ought use the cached page and return in less 1 sec. (specially there is absolutely no one but me on the server), so far I can't make it go faster...what setting am I missing to make it run faster?

Another question is if the query can not avoid but use the tempdb, is it going to always be 30 sec+ time? why is tempdb involvement make it go so much slower?

Thanks in for you help in advance

if the memory available is not enough for internal operations like aggregation and ordering, SQL Server will implictly go to Tempdb and will store the results intermediately here. You cannot avoid is, beside putting more available RAM on the process. Don′t know why this slows down your process that much, did you had a look in the SQL Server logs, esprically on database growth ? Maybe SQL Server is increasing the data files one by one, leading to the problem that the query wioll be halted for the time needed to extend the database.

Jens K. Suessmeyer

http://www.sqlserver2005.de