View Issue Details

IDProjectCategoryView StatusLast Update
0004611SymmetricDSBugpublic2020-11-09 15:15
Reporterpmarzullo Assigned Topmarzullo  
Prioritynormal 
Status closedResolutionfixed 
Product Version3.11.12 
Target Version3.11.13Fixed in Version3.11.13 
Summary0004611: SQL Server primary key update should create update event when only one record is updated
DescriptionWhen SQL Server primary key update occurs, we capture a delete event and an insert event.

This breaks when there are foreign key constraints that refer to this table, referring to non-primary key columns.
This case is rare.
It breaks because we execute a delete on the target, which will cause the foreign key constraint to fail.

The functionality of a primary key update on SQL Server should change such that if only one record is updated when a primary key update occurs, then create an update event, joining the "insert" result set and the "delete" result set with a "ON 1=1". This will link the delete record with the insert record inside the trigger together, successfully linking the two records together.
Steps To Reproduce1. Create table with a primary key and another column.
2. Create a unique index on the other column (required in order to refer to this column with a foreign key constraint).
3. Create another table that has a column that refers to the other column on table 1 with a foreign key constraint.
4. Insert a record into table 1.
5. Insert a record into table 2 with a value in the column referring to the record in table 1.
6. Update the primary key value in table 1.

The delete on the target should fail.
Tagsdialect: sql-server

Relationships

related to 0004612 closedpmarzullo SQL Server primary key update should create update event when only one record is updated 

Activities

There are no notes attached to this issue.

Related Changesets

SymmetricDS: 3.11 4275d170

2020-10-30 17:13:10

pmarzullo

Details Diff
0004611: SQL Server primary key update should create update event when
only one record is updated
Affected Issues
0004611
mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/mssql/MsSqlTriggerTemplate.java Diff File

SymmetricDS: 3.12 d6d674c5

2020-10-30 17:13:10

pmarzullo

Details Diff
0004611: SQL Server primary key update should create update event when
only one record is updated
Affected Issues
0004611
mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/mssql/MsSqlTriggerTemplate.java Diff File

Issue History

Date Modified Username Field Change
2020-10-29 17:29 pmarzullo New Issue
2020-10-29 17:29 pmarzullo Status new => assigned
2020-10-29 17:29 pmarzullo Assigned To => pmarzullo
2020-10-29 17:29 pmarzullo Tag Attached: dialect: sql-server
2020-10-29 17:31 pmarzullo Relationship added related to 0004612
2020-10-30 17:22 pmarzullo Status assigned => resolved
2020-10-30 17:22 pmarzullo Resolution open => fixed
2020-10-30 17:22 pmarzullo Fixed in Version => 3.11.13
2020-10-30 18:00 pmarzullo Changeset attached => SymmetricDS 3.11 4275d170
2020-10-30 18:00 pmarzullo Changeset attached => SymmetricDS 3.12 d6d674c5
2020-11-09 15:15 admin Status resolved => closed