How do I make use of begin transaction and commit transaction in SSIS.

Hi

How do I make use of begin transaction and commit transaction in SSIS.

As am not able to commit changes due to certain update commands I want to explicitly write begin and commit statements. but when i make use of begin and commit in OLEDB commnad stage it throws an error as follows:

Hresult:0x80004005

descriptionyntax error or access violation.

its definately not an syntax error as i executed it in sql server. also when i use it in execute sql task out side the dataflow container it doesnt throw any error but still this task doesnt serve my purpose of saving/ commiting update chanages in the database.

Thanks,

Prashant

Take a look at this:

http://www.sqlservercentral.com/columnists/jthomson/transactionsinsqlserver2005integrationservices.asp

Basically, if you want the scope of your transaction to be bigger than a single Execute SQL task, you need to define your transactions declaratively through the way you build your packages. The key is the TransactionOption property of containers, which include packages, Sequence containers and the "invisible" TaskHost container that wraps each task. This property works much like transactions did in COM+ and MTS, so if you've done work there it should look at least a little familiar.

|||

thanks mattew, the paper was very useful

But I used a different way to achieve my purpose.

I used same connection manager for all Execute SQL tasks with RetainSameConnection = True.

Thanks,

Prash

How do I make use of begin transaction and commit transaction in SSIS.

Hi

How do I make use of begin transaction and commit transaction in SSIS.

As am not able to commit changes due to certain update commands I want to explicitly write begin and commit statements. but when i make use of begin and commit in OLEDB commnad stage it throws an error as follows:

Hresult:0x80004005

descriptionyntax error or access violation.

its definately not an syntax error as i executed it in sql server. also when i use it in execute sql task out side the dataflow container it doesnt throw any error but still this task doesnt serve my purpose of saving/ commiting update chanages in the database.

Thanks,

Prashant

Take a look at this:

http://www.sqlservercentral.com/columnists/jthomson/transactionsinsqlserver2005integrationservices.asp

Basically, if you want the scope of your transaction to be bigger than a single Execute SQL task, you need to define your transactions declaratively through the way you build your packages. The key is the TransactionOption property of containers, which include packages, Sequence containers and the "invisible" TaskHost container that wraps each task. This property works much like transactions did in COM+ and MTS, so if you've done work there it should look at least a little familiar.

|||

thanks mattew, the paper was very useful

But I used a different way to achieve my purpose.

I used same connection manager for all Execute SQL tasks with RetainSameConnection = True.

Thanks,

Prash

How do I make the name of a variable dynamic?

Hi guys, I have the following store procedure:

PROCEDURE dbo.AddSearchColumn (@.A1, @.A2. @.A3, @.A4, @.A5) AS
Declare @.cElements cursor,@.DocNum varchar(100)
BEGIN
Set @.cElements = cursor for select FirstNaname rom dbo.DocTable1
open @.cElements
fetch NEXT from @.cElements into @.DocNum

while (@.@.FETCH_STATUS = 0)
begin
if (@.DocNum==@.A1 //Here is what I need to do: I want use @.A1 at the first loop step, @.A2 at the second, and so on
...... // @.A1, @.A2 are the parameters in input
end
END

close @.cElements
DEALLOCATE @.cElements

I hope my explanation is clear.

Please, give some hints.
Thanks for your time,

Christian Malatesti

Can you explain in more detail what it is that you are trying to do? From what I understand you want to compare the values in the parameters against "FirstNaname" from table docTable1?

|||

What I am trying to do is this:

I have several parameters in input: @.A1, @.A2, @.A3, @.A4,...and so on (they are more than four) that I need to use

while looping through the elements of the cursor.

I know the order that the input parameters need to be used.

At the first step of the loop I have to compare the first element retrieved by the cursor with @.A1.

At the second step of the loop I have to compare the second element retrieved by the cursor with @.A2.

At the third step of the loop I have to compare the third element retrieved by the cursor with @.A3.

and so on...

What I would like to accomplish is creating a variable (@.dynamicVariable) that at the first contains the value of @.A1, atthe second step, @.dynamicVariable contains the value of @.A2, andso on...

Below is a basic example.

Declare@.dynamicVariable , @.counter int

set @.counter=1;

while (@.@.FETCH_STATUS = 0)
begin

set @.dynamicVariable = "@.A" + ToString(@.counter)
if (@.DocNum==@.dynamicVariable....

//something like this:

set @.counter=@.counter + 1;
end
END

Thanks for responding me.

Christian Malatesti

|||Is the number of parameters fixed?|||YES, i have 17 parameters in input|||And you will always have 17 values returned from your SQL statement?|||

I do not have any parameters in output.

The input parameters are used to perfrom various INSERT, that I did not include in the post.

Christian

|||

Perhaps you can get the results into 2 tables (table variables) each with identity columns and compare by rowid?

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