| Author |
Topic |
|
dduplessis
Starting Member
9 Posts |
 Posted - 2006-10-05 : 17:54:44
|
| select * into [LOG_UPDATE_BEFORE] from deletedselect * into [LOG_UPDATE_AFTER] from insertedexec sp_LOG_OnTableUpdateHi, I was wondering if anyone could tell me why the following is happening. The code code is the body of an update trigger on a table. The code executes fine when run in 2000. However, after porting to 2005, it generates the following error:No row was updatedThe data in row 4 was not committed.Error Source: Microsoft.VisualStudio.Data.ToolsError Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows (3 rows).Any help will be much appreciated.Thanks. |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-10-05 : 18:00:28
|
| You'll need to post the code for sp_LOG_OnTableUpdate and - are you sure the table definitions are exactly the same on the two servers (especially the primary key and/or unique indexes/constraints)? |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-10-05 : 18:34:13
|
  |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-10-05 : 21:27:06
|
quote:
Originally posted by robvolk
that's explain why it does not work on SQL Server 2005.You will need the SQL Server 100 AcreWood Edition
KH |
|
|
|
dduplessis
Starting Member
9 Posts |
Posted - 2006-10-06 : 09:33:11
|
quote:
Originally posted by snSQL
You'll need to post the code for sp_LOG_OnTableUpdate and - are you sure the table definitions are exactly the same on the two servers (especially the primary key and/or unique indexes/constraints)?
It makes no difference. You can remove the call to sp_LOG_OnTableUpdate, and it still generates the same error. Also, "select * into [LOG_UPDATE_BEFORE]", creates the [LOG_UPDATE_BEFORE] table (since it is a select into). "insert * into [LOG_UPDATE_BEFORE]" would require that [LOG_UPDATE_BEFORE] exists. I.e. the newly created table (created by select * into, should have the same schema. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2006-10-06 : 09:44:16
|
quote:
Originally posted by dduplessis
quote:
Originally posted by snSQL
You'll need to post the code for sp_LOG_OnTableUpdate and - are you sure the table definitions are exactly the same on the two servers (especially the primary key and/or unique indexes/constraints)?
It makes no difference. You can remove the call to sp_LOG_OnTableUpdate, and it still generates the same error. Also, "select * into [LOG_UPDATE_BEFORE]", creates the [LOG_UPDATE_BEFORE] table (since it is a select into). "insert * into [LOG_UPDATE_BEFORE]" would require that [LOG_UPDATE_BEFORE] exists. I.e. the newly created table (created by select * into, should have the same schema.
I'm sorry, did you want an answer?Are you saying that this workied before? I don't see how.1. SELECT * is just bad form2. SELECT INTO in a trigger? Once it's fired and created, it will never work againCan we back up...what are you trying to accomplishBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
dduplessis
Starting Member
9 Posts |
Posted - 2006-10-06 : 09:50:38
|
quote:
Originally posted by X002548
quote:
Originally posted by dduplessis
quote:
Originally posted by snSQL
You'll need to post the code for sp_LOG_OnTableUpdate and - are you sure the table definitions are exactly the same on the two servers (especially the primary key and/or unique indexes/constraints)?
It makes no difference. You can remove the call to sp_LOG_OnTableUpdate, and it still generates the same error. Also, "select * into [LOG_UPDATE_BEFORE]", creates the [LOG_UPDATE_BEFORE] table (since it is a select into). "insert * into [LOG_UPDATE_BEFORE]" would require that [LOG_UPDATE_BEFORE] exists. I.e. the newly created table (created by select * into, should have the same schema.
I'm sorry, did you want an answer?Are you saying that this workied before? I don't see how.1. SELECT * is just bad form2. SELECT INTO in a trigger? Once it's fired and created, it will never work againCan we back up...what are you trying to accomplishBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam
It's a generic update trigger that creates a log of changes made to a table. Since it is generic code, I don't want to have to gather the schema and create the table every time. I would much rather simply do a "select * into". The code quoted is paraphrased. The test case would be even more simple. Simply add 1 of the select * into statements to an update trigger, and you will get it to happen. This code worked on 2000. It simply does not work on 2005. I am trying to determine why. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-10-06 : 09:59:10
|
quote:
Rob has gone bye-bye
Someone didn't read the subject line too carefully. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2006-10-06 : 10:37:14
|
The tigger problem is, the voice of Tigger died last year  [Signature]For fast help, follow this link:http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspxLearn SQLhttp://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-06 : 10:46:21
|
| "Since it is generic code, I don't want to have to gather the schema and create the table every time."What about when you add a column to the underlying table?We just handle the orginal creation of the Audit table, and any chagnes to it when the underlying table changes, as part of the "DBA scripting role" to esnure that we can rollout the changed from DEV to QA to Production etc.If you are doing CREATE TABLE MyTable (COL1 ... COLn)its easy enough to cut & paste that to also do:CREATE TABLE MyAuditTable (COL1 ... COLn)See alsohttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Triggers+to+audit+changesKristen |
|
|
|
dduplessis
Starting Member
9 Posts |
Posted - 2006-10-06 : 11:32:19
|
| So here is the issue....SQL Management Studio 2005 requires you to define a primary key on a table, in order to modify rows. This is not a requirement in SQL Enterprise Manager for 2000. I.e. the issue is a non-issue, if you update via a transaction. The problem for us now is that we enter most of our data using Enterprise Manager, i.e. will need to use Management Studio to enter data. Is anyone aware of a configuration setting or something which removes this requirement? Thanks. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-10-06 : 11:45:47
|
| Linked tables in Access are better than using SSMS or EM for data entry. No one who is not an admin (or in rare cases, a developer) should even HAVE SSMS on their computer.And what's wrong with having a primary key on your tables? I have a feeling a unique constraint would work too. Not that using EM or SSMS should be the reason for adding them. |
|
|
|
dduplessis
Starting Member
9 Posts |
Posted - 2006-10-06 : 12:02:12
|
| Guys (and girls)I understand that using access is a better solution. In the future, we will have a data entry application written specifically for that purpose. However, right now I have an immediate need to resolve this issue, that does not involve having to add a primary key to EVERY table in the db (that does not already have one). Any suggestions? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-06 : 12:32:56
|
| Not helpful to the discussion but I thought that E.M. needed a unique index in order to be able to do updates to the data. Perhaps not."right now I have an immediate need to resolve this issue, that does not involve having to add a primary key to EVERY table in the db (that does not already have one)"If Access is happy to allow updates without a PK would that be a solution?I'm kind of nervous that you don't have a PK because it suggests that you are going to get duplicates and all sorts of other rubbish data - but then I don't know anything about your tables, although I suspect others here are having a similar thought.Might help to know why this table doesn't need a PK - or why you cannot add one.Kristen |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2006-10-06 : 13:05:42
|
quote:
Originally posted by dduplessis
Guys (and girls)I understand that using access is a better solution. In the future, we will have a data entry application written specifically for that purpose. However, right now I have an immediate need to resolve this issue, that does not involve having to add a primary key to EVERY table in the db (that does not already have one). Any suggestions?
You don't have a PK on any table?And the goal is to do data entry?Use EXCEL, create a tab delimited file, and bcp the data inBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
|
Tigger Issue in 2005