Problems Updating a table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problems Updating a table

Author

Topic

andrealvarez
Starting Member

3 Posts

Posted - 2006-01-06 : 07:40:46

i got some strange problems updating a table
i have four tables

CREATE TABLE [Table1](
[Table1_Field1] [nVarchar](50) Not Null,
[Table1_Field1] [nVarchar](60) Not Null
)

CREATE TABLE [Table2](
[Table2_Field1] [nVarchar](50) Not Null,
[Table2_Field2] [nVarchar](60) Not Null,
[Table2_Field13] [nVarchar](10) Not Null
)

CREATE TABLE [Table3](
[Table3_Field1] [nVarchar](50) Not Null,
[Table3_Field2] [nVarchar](60) Not Null,
[Table3_Field3] [nVarchar](10) Not Null
)

CREATE TABLE [Table4](
[Table4_Field1] [nVarchar](50) Not Null,
[Table4_Field2] [nVarchar](60) Not Null,
[Table4_Field3] [nVarchar](10) Not Null,
[Table4_Field4] [nVarchar](50) Not Null,
[Table4_Field5] [nVarchar](60) Not Null,
[Table4_Field6] [nVarchar](10) Not Null
)

all fields are primary keys!

table1 references to table 2 and 3
table1_field1 -> table2_field1, table1_field2 -> table2_field2
table1_field1 -> table3_field1, table1_field2 -> table3_field2

both updates over constraint
update works fine!

table2 and table3 references both to table4
table2_field1 -> table4_field1, table2_field2 -> table4_field2, table2_field3 -> table4_field3
table3_field1 -> table4_field4, table3_field2 -> table4_field5, table3_field3 -> table4_field6

table2 updates via constaint
table3 updates via an update trigger

if i now change a value e.g. in table1_field1, the changes should be passd through to table4
but i get an error duplicate values from the trigger, although there is no dupicate error.

please help

nr
SQLTeam MVY

12543 Posts

Posted - 2006-01-06 : 07:46:13

Can you post the trigger.
Are you catering for multiple rows in inserted/deleted?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

andrealvarez
Starting Member

3 Posts

Posted - 2006-01-06 : 08:01:44

this is the trigger for table3:

CREATE TRIGGER [UTrig_table3] ON [table3]
FOR UPDATE
AS
SET NOCOUNT ON
BEGIN

IF UPDATE("Field1") OR UPDATE ("Field2") OR UPDATE ("Field3")
UPDATE
[Table4]
SET
[Table4]."Field4" = inserted."Field1",
[Table4]."Field5" = inserted."Field2",
[Table4]."Field6" = inserted."Field3"
FROM
inserted, deleted
WHERE
[Table4]."Field4" = deleted."Field1" AND
[Table4]."Field5" = deleted."Field2" AND
[Table4]."Field6" = deleted."Field3"

END

nr
SQLTeam MVY

12543 Posts

Posted - 2006-01-06 : 08:08:56

That assumes you are doing single row updates. If more than one row is updated in table3 in a single statement it will probably give the error you are seeing.
I don't think there is any way of doing what you are attempting as you are alowing PKs to be updateable. I would advise redesigning the structure to give something unique and non-updateable on each table.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

andrealvarez
Starting Member

3 Posts

Posted - 2006-01-06 : 09:03:20

there are more rows updated in table3, but i have the same running under oracle 9 and it works fine, so there must be way in sql server

nr
SQLTeam MVY

12543 Posts

Posted - 2006-01-06 : 10:56:49

Oracle has a cludge so the PK isn't really the row identifier.
SQL Server relies a bit more on the database design.

Without joining the insetred and deleted tables you can't do what you aer trying to do.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources