Error updating data in table that uses a trigger?

Hello. I am new to SQL Server Management Studio 2005 and am using it to connect to a database on another machine running SQL Server 2000. Everything seems to work well, except when I try to edit table data on a particular table that has an update trigger associated with it. I am receiving the error:

-

The data in row 1 was not committed.

Error Source: Microsoft.VisualStudio.DataTools.

Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(5 rows).

Correct the errors and retry or press ESC to chancel the changes.

--

I want it to update those the 5 rows in the other tables...that's the whole point of having the trigger!

This used to work fine from Enterprise Manager 2000 and If I issue the update query manually from the new integrated "Query Analyzer" it is also ok. Is there some way to deactivate this new feature from the table viewer?

Thanks very much!

That error message occurred once for because there was a constraint which the deletion violated. I believe I had a field set to "NOT NULL" and the deletion was attempting to delete just that field for some reason.|||Also make sure that there are not two rows where every column is exactly the same.|||

Thanks for responding. My modification is not causing duplicate records to be created, nor am I violating any of my table rules (such as not NULL).

What I have is a trigger associated with my table so that whenever a record is updated it runs a stored procedure which then updates related information in other tables. It is not an error, but a very essential function of my database.

If I issue a manual update query (with query analyzer) I receive no errors and my trigger runs perfectly. It's only when I try to update a record interactively with the "Open Table" viewer/interface that I receive the error.

This new feature is very disruptive to the way I work and I was hoping that there was an option somewhere to turn it off, or make it so that it only applies to real errors such as duplicate records being created.

Thanks....

|||Can you run Profiler and gather a trace to see what it's doing differently than QA?|||

It's not really doing anything different, it's just that the new interactive "table viewer" has some sort of error checking routine that prohibits the update from executing because it sees that the query will affect data in other tables... which is what it was designed to do and worked flawlessly with Enterprise Manager 2000.

Am I the only person here who uses triggers to update data in other tables?

Thanks...

|||

You're absolutely not... I have the same problem. Very frustrating having to run the old version of Enterprise Manager just to update a few values.

Did you manage to find a fix for this?

|||Nope. No fix yet (that I'm aware of). Maybe if we keep this thread visible someone higher up can recommend that it be addressed in the next service pack.|||

Please file a defect report with the MSDN Feedback Center (http://lab.msdn.microsoft.com/productfeedback/).

If you can include a minimal script to create sample tables and a stored procedure to set up the test, that will help quite a bit.

Thanks,
Steve

|||

This appears to be a bug in the how SMSS is interperting the ADO 'feedback' area.

I have a table (T1) with a trigger that updates another table (T2) on INSERT and UPDATE.

1. UPDATE T1 SET C1 = 'value' WHERE ID=2 works fines

2. Trapping the SSMS generated 'sp_executesql' statement using Profiler and then executing it works fine.

3. Using SSMS to update the same row results in the error above.

Note that both1 and 2 above result in messages as follows:

(1 row(s) affected)

(1 row(s) affected)

If memory serves me right these 'messages' are both passed back in the ADO 'feedback' area and can be processed by the code of SSMS. Seems the code wasn't tested with replications scenarios.

Im on W2K3 and SQL 2005 SP plus hot fixes.

|||

There is an open Microsoft Connect issue... you can track the progress here:
https://connect.microsoft.com:443/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125566

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||I'm not sure if this will help any of you but I was able to get around this by putting a SET NOCOUNT ON at the beginning of my trigger.

Error updating data in table that uses a trigger?

Hello. I am new to SQL Server Management Studio 2005 and am using it to connect to a database on another machine running SQL Server 2000. Everything seems to work well, except when I try to edit table data on a particular table that has an update trigger associated with it. I am receiving the error:

-

The data in row 1 was not committed.

Error Source: Microsoft.VisualStudio.DataTools.

Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(5 rows).

Correct the errors and retry or press ESC to chancel the changes.

--

I want it to update those the 5 rows in the other tables...that's the whole point of having the trigger!

This used to work fine from Enterprise Manager 2000 and If I issue the update query manually from the new integrated "Query Analyzer" it is also ok. Is there some way to deactivate this new feature from the table viewer?

Thanks very much!

That error message occurred once for because there was a constraint which the deletion violated. I believe I had a field set to "NOT NULL" and the deletion was attempting to delete just that field for some reason.|||Also make sure that there are not two rows where every column is exactly the same.|||

Thanks for responding. My modification is not causing duplicate records to be created, nor am I violating any of my table rules (such as not NULL).

What I have is a trigger associated with my table so that whenever a record is updated it runs a stored procedure which then updates related information in other tables. It is not an error, but a very essential function of my database.

If I issue a manual update query (with query analyzer) I receive no errors and my trigger runs perfectly. It's only when I try to update a record interactively with the "Open Table" viewer/interface that I receive the error.

This new feature is very disruptive to the way I work and I was hoping that there was an option somewhere to turn it off, or make it so that it only applies to real errors such as duplicate records being created.

Thanks....

|||Can you run Profiler and gather a trace to see what it's doing differently than QA?|||

It's not really doing anything different, it's just that the new interactive "table viewer" has some sort of error checking routine that prohibits the update from executing because it sees that the query will affect data in other tables... which is what it was designed to do and worked flawlessly with Enterprise Manager 2000.

Am I the only person here who uses triggers to update data in other tables?

Thanks...

|||

You're absolutely not... I have the same problem. Very frustrating having to run the old version of Enterprise Manager just to update a few values.

Did you manage to find a fix for this?

|||Nope. No fix yet (that I'm aware of). Maybe if we keep this thread visible someone higher up can recommend that it be addressed in the next service pack.|||

Please file a defect report with the MSDN Feedback Center (http://lab.msdn.microsoft.com/productfeedback/).

If you can include a minimal script to create sample tables and a stored procedure to set up the test, that will help quite a bit.

Thanks,
Steve

|||

This appears to be a bug in the how SMSS is interperting the ADO 'feedback' area.

I have a table (T1) with a trigger that updates another table (T2) on INSERT and UPDATE.

1. UPDATE T1 SET C1 = 'value' WHERE ID=2 works fines

2. Trapping the SSMS generated 'sp_executesql' statement using Profiler and then executing it works fine.

3. Using SSMS to update the same row results in the error above.

Note that both1 and 2 above result in messages as follows:

(1 row(s) affected)

(1 row(s) affected)

If memory serves me right these 'messages' are both passed back in the ADO 'feedback' area and can be processed by the code of SSMS. Seems the code wasn't tested with replications scenarios.

Im on W2K3 and SQL 2005 SP plus hot fixes.

|||

There is an open Microsoft Connect issue... you can track the progress here:
https://connect.microsoft.com:443/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125566

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||I'm not sure if this will help any of you but I was able to get around this by putting a SET NOCOUNT ON at the beginning of my trigger.

Error updating data in table that uses a trigger?

Hello. I am new to SQL Server Management Studio 2005 and am using it to connect to a database on another machine running SQL Server 2000. Everything seems to work well, except when I try to edit table data on a particular table that has an update trigger associated with it. I am receiving the error:

-

The data in row 1 was not committed.

Error Source: Microsoft.VisualStudio.DataTools.

Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(5 rows).

Correct the errors and retry or press ESC to chancel the changes.

--

I want it to update those the 5 rows in the other tables...that's the whole point of having the trigger!

This used to work fine from Enterprise Manager 2000 and If I issue the update query manually from the new integrated "Query Analyzer" it is also ok. Is there some way to deactivate this new feature from the table viewer?

Thanks very much!

That error message occurred once for because there was a constraint which the deletion violated. I believe I had a field set to "NOT NULL" and the deletion was attempting to delete just that field for some reason.|||Also make sure that there are not two rows where every column is exactly the same.|||

Thanks for responding. My modification is not causing duplicate records to be created, nor am I violating any of my table rules (such as not NULL).

What I have is a trigger associated with my table so that whenever a record is updated it runs a stored procedure which then updates related information in other tables. It is not an error, but a very essential function of my database.

If I issue a manual update query (with query analyzer) I receive no errors and my trigger runs perfectly. It's only when I try to update a record interactively with the "Open Table" viewer/interface that I receive the error.

This new feature is very disruptive to the way I work and I was hoping that there was an option somewhere to turn it off, or make it so that it only applies to real errors such as duplicate records being created.

Thanks....

|||Can you run Profiler and gather a trace to see what it's doing differently than QA?|||

It's not really doing anything different, it's just that the new interactive "table viewer" has some sort of error checking routine that prohibits the update from executing because it sees that the query will affect data in other tables... which is what it was designed to do and worked flawlessly with Enterprise Manager 2000.

Am I the only person here who uses triggers to update data in other tables?

Thanks...

|||

You're absolutely not... I have the same problem. Very frustrating having to run the old version of Enterprise Manager just to update a few values.

Did you manage to find a fix for this?

|||Nope. No fix yet (that I'm aware of). Maybe if we keep this thread visible someone higher up can recommend that it be addressed in the next service pack.|||

Please file a defect report with the MSDN Feedback Center (http://lab.msdn.microsoft.com/productfeedback/).

If you can include a minimal script to create sample tables and a stored procedure to set up the test, that will help quite a bit.

Thanks,
Steve

|||

This appears to be a bug in the how SMSS is interperting the ADO 'feedback' area.

I have a table (T1) with a trigger that updates another table (T2) on INSERT and UPDATE.

1. UPDATE T1 SET C1 = 'value' WHERE ID=2 works fines

2. Trapping the SSMS generated 'sp_executesql' statement using Profiler and then executing it works fine.

3. Using SSMS to update the same row results in the error above.

Note that both1 and 2 above result in messages as follows:

(1 row(s) affected)

(1 row(s) affected)

If memory serves me right these 'messages' are both passed back in the ADO 'feedback' area and can be processed by the code of SSMS. Seems the code wasn't tested with replications scenarios.

Im on W2K3 and SQL 2005 SP plus hot fixes.

|||

There is an open Microsoft Connect issue... you can track the progress here:
https://connect.microsoft.com:443/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125566

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||I'm not sure if this will help any of you but I was able to get around this by putting a SET NOCOUNT ON at the beginning of my trigger.

Error updating data in table that uses a trigger?

Hello. I am new to SQL Server Management Studio 2005 and am using it to connect to a database on another machine running SQL Server 2000. Everything seems to work well, except when I try to edit table data on a particular table that has an update trigger associated with it. I am receiving the error:

-

The data in row 1 was not committed.

Error Source: Microsoft.VisualStudio.DataTools.

Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(5 rows).

Correct the errors and retry or press ESC to chancel the changes.

--

I want it to update those the 5 rows in the other tables...that's the whole point of having the trigger!

This used to work fine from Enterprise Manager 2000 and If I issue the update query manually from the new integrated "Query Analyzer" it is also ok. Is there some way to deactivate this new feature from the table viewer?

Thanks very much!

That error message occurred once for because there was a constraint which the deletion violated. I believe I had a field set to "NOT NULL" and the deletion was attempting to delete just that field for some reason.|||Also make sure that there are not two rows where every column is exactly the same.|||

Thanks for responding. My modification is not causing duplicate records to be created, nor am I violating any of my table rules (such as not NULL).

What I have is a trigger associated with my table so that whenever a record is updated it runs a stored procedure which then updates related information in other tables. It is not an error, but a very essential function of my database.

If I issue a manual update query (with query analyzer) I receive no errors and my trigger runs perfectly. It's only when I try to update a record interactively with the "Open Table" viewer/interface that I receive the error.

This new feature is very disruptive to the way I work and I was hoping that there was an option somewhere to turn it off, or make it so that it only applies to real errors such as duplicate records being created.

Thanks....

|||Can you run Profiler and gather a trace to see what it's doing differently than QA?|||

It's not really doing anything different, it's just that the new interactive "table viewer" has some sort of error checking routine that prohibits the update from executing because it sees that the query will affect data in other tables... which is what it was designed to do and worked flawlessly with Enterprise Manager 2000.

Am I the only person here who uses triggers to update data in other tables?

Thanks...

|||

You're absolutely not... I have the same problem. Very frustrating having to run the old version of Enterprise Manager just to update a few values.

Did you manage to find a fix for this?

|||Nope. No fix yet (that I'm aware of). Maybe if we keep this thread visible someone higher up can recommend that it be addressed in the next service pack.|||

Please file a defect report with the MSDN Feedback Center (http://lab.msdn.microsoft.com/productfeedback/).

If you can include a minimal script to create sample tables and a stored procedure to set up the test, that will help quite a bit.

Thanks,
Steve

|||

This appears to be a bug in the how SMSS is interperting the ADO 'feedback' area.

I have a table (T1) with a trigger that updates another table (T2) on INSERT and UPDATE.

1. UPDATE T1 SET C1 = 'value' WHERE ID=2 works fines

2. Trapping the SSMS generated 'sp_executesql' statement using Profiler and then executing it works fine.

3. Using SSMS to update the same row results in the error above.

Note that both1 and 2 above result in messages as follows:

(1 row(s) affected)

(1 row(s) affected)

If memory serves me right these 'messages' are both passed back in the ADO 'feedback' area and can be processed by the code of SSMS. Seems the code wasn't tested with replications scenarios.

Im on W2K3 and SQL 2005 SP plus hot fixes.

|||

There is an open Microsoft Connect issue... you can track the progress here:
https://connect.microsoft.com:443/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125566

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||I'm not sure if this will help any of you but I was able to get around this by putting a SET NOCOUNT ON at the beginning of my trigger.

Error updating data in table that uses a trigger?

Hello. I am new to SQL Server Management Studio 2005 and am using it to connect to a database on another machine running SQL Server 2000. Everything seems to work well, except when I try to edit table data on a particular table that has an update trigger associated with it. I am receiving the error:

-

The data in row 1 was not committed.

Error Source: Microsoft.VisualStudio.DataTools.

Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(5 rows).

Correct the errors and retry or press ESC to chancel the changes.

--

I want it to update those the 5 rows in the other tables...that's the whole point of having the trigger!

This used to work fine from Enterprise Manager 2000 and If I issue the update query manually from the new integrated "Query Analyzer" it is also ok. Is there some way to deactivate this new feature from the table viewer?

Thanks very much!

That error message occurred once for because there was a constraint which the deletion violated. I believe I had a field set to "NOT NULL" and the deletion was attempting to delete just that field for some reason.|||Also make sure that there are not two rows where every column is exactly the same.|||

Thanks for responding. My modification is not causing duplicate records to be created, nor am I violating any of my table rules (such as not NULL).

What I have is a trigger associated with my table so that whenever a record is updated it runs a stored procedure which then updates related information in other tables. It is not an error, but a very essential function of my database.

If I issue a manual update query (with query analyzer) I receive no errors and my trigger runs perfectly. It's only when I try to update a record interactively with the "Open Table" viewer/interface that I receive the error.

This new feature is very disruptive to the way I work and I was hoping that there was an option somewhere to turn it off, or make it so that it only applies to real errors such as duplicate records being created.

Thanks....

|||Can you run Profiler and gather a trace to see what it's doing differently than QA?|||

It's not really doing anything different, it's just that the new interactive "table viewer" has some sort of error checking routine that prohibits the update from executing because it sees that the query will affect data in other tables... which is what it was designed to do and worked flawlessly with Enterprise Manager 2000.

Am I the only person here who uses triggers to update data in other tables?

Thanks...

|||

You're absolutely not... I have the same problem. Very frustrating having to run the old version of Enterprise Manager just to update a few values.

Did you manage to find a fix for this?

|||Nope. No fix yet (that I'm aware of). Maybe if we keep this thread visible someone higher up can recommend that it be addressed in the next service pack.|||

Please file a defect report with the MSDN Feedback Center (http://lab.msdn.microsoft.com/productfeedback/).

If you can include a minimal script to create sample tables and a stored procedure to set up the test, that will help quite a bit.

Thanks,
Steve

|||

This appears to be a bug in the how SMSS is interperting the ADO 'feedback' area.

I have a table (T1) with a trigger that updates another table (T2) on INSERT and UPDATE.

1. UPDATE T1 SET C1 = 'value' WHERE ID=2 works fines

2. Trapping the SSMS generated 'sp_executesql' statement using Profiler and then executing it works fine.

3. Using SSMS to update the same row results in the error above.

Note that both1 and 2 above result in messages as follows:

(1 row(s) affected)

(1 row(s) affected)

If memory serves me right these 'messages' are both passed back in the ADO 'feedback' area and can be processed by the code of SSMS. Seems the code wasn't tested with replications scenarios.

Im on W2K3 and SQL 2005 SP plus hot fixes.

|||

There is an open Microsoft Connect issue... you can track the progress here:
https://connect.microsoft.com:443/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125566

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||I'm not sure if this will help any of you but I was able to get around this by putting a SET NOCOUNT ON at the beginning of my trigger.

Error Trapping

I have a linked server setup in a SQL Server 2000 environment that links to
an Oracle database. I then have a Trigger on a SQL Server table, that on
insert, inserts a row of data into the Oracle table via the linked server.
All of this is driven by a front-end application written in VB.NET. I want
the front end to be oblivious to trigger. In other words, if the linked
server loses connection to the Oracle database I don't want the trigger to
execute. I have a "clean-up" stored proc that will insert any rows that
weren't inserted by the trigger. Any ideas on how I can accomplish this? I
am unable to trap the error before it actually occurs. I would like to be
able to somehow check the status of the linked server before executing the
trigger. If it is offline (or errors) then the trigger should not execute
and no errors should be sent to the VB.NET front end. Thanks for any advice
.
Marcocould you not just write a sProc that uses the below and execute it from .ne
t
as follows:
Private Function RunExecRoutines()
Try
cn = New SqlClient.SqlConnection("user id=" & UserName.Text & ";password=" &
Password.Text & ";database=" & Database.Text & ";server=" & Server.Text)
cn.Open()
Catch ex As Exception
sqlCnError = ("Error: Could not establish database connection")
End Try
cmd = New SqlClient.SqlCommand
cmd.Connection = cn
cmd.CommandText = ProcToExec -- whatever sProc you wanna run
cmd.CommandTimeout = 9000
cmd.CommandType = CommandType.StoredProcedure
-- any params you want to pass or receive back...
cmd.Parameters.Add("@.pWho", UNameRoutine)
cmd.Parameters(0).Direction = ParameterDirection.Input
cmd.Parameters.Add("@.pRESULT", SqlDbType.Int)
cmd.Parameters(1).Direction = ParameterDirection.Output
Try
cmd.ExecuteNonQuery()
Catch ex As Exception
sqlCnError = ("Error: Could not execute " + ProcToExec + " stored procedure"
)
MessageBox.Show(sqlCnError, "AppName", MessageBoxButtons.OK, _
MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
End Try
cmd.Dispose()
cn.Close()
cn.Dispose()
end fuction
select srvid, srvstatus, srvname, srvproduct, providername, catalog from
master.dbo.sysservers where srvstatus & 32 <> 0
sp_helptext sp_sqlagent_get_perf_counters -- msdb
sp_helptext sp_enum_oledb_providers -- master
sp_helptext xp_enum_oledb_providers -- master|||Marc,
Thanks for the advice but I am working with a trigger that is executed in
SQL Server. The error occurs in SQL Server and is then reported to the app.
I had thought about the try...catch method but it does me no good since I am
working with a trigger that is idenpendent of .NET. Do you have any other
ideas? I would rather not reengineer my front-end app if at all possible.
Thanks,
Marco
"marcmc" wrote:

> could you not just write a sProc that uses the below and execute it from .
net
> as follows:
> Private Function RunExecRoutines()
> Try
> cn = New SqlClient.SqlConnection("user id=" & UserName.Text & ";password="
&
> Password.Text & ";database=" & Database.Text & ";server=" & Server.Text)
> cn.Open()
> Catch ex As Exception
> sqlCnError = ("Error: Could not establish database connection")
> End Try
> cmd = New SqlClient.SqlCommand
> cmd.Connection = cn
> cmd.CommandText = ProcToExec -- whatever sProc you wanna run
> cmd.CommandTimeout = 9000
> cmd.CommandType = CommandType.StoredProcedure
> -- any params you want to pass or receive back...
> cmd.Parameters.Add("@.pWho", UNameRoutine)
> cmd.Parameters(0).Direction = ParameterDirection.Input
> cmd.Parameters.Add("@.pRESULT", SqlDbType.Int)
> cmd.Parameters(1).Direction = ParameterDirection.Output
> Try
> cmd.ExecuteNonQuery()
> Catch ex As Exception
> sqlCnError = ("Error: Could not execute " + ProcToExec + " stored procedur
e")
> MessageBox.Show(sqlCnError, "AppName", MessageBoxButtons.OK, _
> MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
> End Try
> cmd.Dispose()
> cn.Close()
> cn.Dispose()
> end fuction
>
> select srvid, srvstatus, srvname, srvproduct, providername, catalog from
> master.dbo.sysservers where srvstatus & 32 <> 0
> sp_helptext sp_sqlagent_get_perf_counters -- msdb
> sp_helptext sp_enum_oledb_providers -- master
> sp_helptext xp_enum_oledb_providers -- master|||would this be any use?
if @.varName <= 0 goto trig_error
trig_error:
raiserror('Allocation Error - Unable to Allocate Zero Quantity',16,-1)|||Marc,
Thanks for the ideas. What I ended up doing was adding a status indicator
field to my table. In the front end I try to connect to the database. If I
cannot connect it sets this indicator field to a 1. If on insert that field
is set to one the Trigger then ignores the inserted row. This will also
allow me to monitor, a bit more closely, any connectivity issues I may have
with the Oracle database. thanks again for your help.
Marco
"marcmc" wrote:

> would this be any use?
> if @.varName <= 0 goto trig_error
> trig_error:
> raiserror('Allocation Error - Unable to Allocate Zero Quantity',16,-1)
>