View Issue Details

IDProjectCategoryView StatusLast Update
0005157SymmetricDSImprovementpublic2022-07-27 18:59
Reporterpmarzullo Assigned Topmarzullo  
Prioritynormal 
Status closedResolutionfixed 
Product Version3.13.1 
Target Version3.14.0Fixed in Version3.14.0 
Summary0005157: Sybase and SQL Server update trigger should be made more efficient generating sym_data records when primary keys are changed
DescriptionFrom issue #EVJ-56133-571

Change # 1:
Here is a strategy I used in the past. Let assume table t1 has a composite PK: col1, col2.

As you suggested, you could also enhance the code that if the @@rowcount = 1, you always generate an update where the pk columns are set to the value from the inserted table in the SET clause and set to the value from the deleted table in the where clause, no matter what. This enhancement would avoid paying a run time penalty in this case. Let me know what you think!

(I did not compile the code so I could have a typo or two in there!)

CREATE OR REPLACE TRIGGER t1_u_t
ON t1
FOR UPDATE
AS
DECLARE @rc1 int,
                 @rc2 int

SELECT @rc1 = @@rowCount -- Save the number of rows affected by this update statement

IF UPDATE(col1) OR UPDATE(col2)
BEGIN -- Primary key column(s) are part of the SET clause but not necessarily updated to a different value
    SELECT @rc2 = COUNT(*)
        FROM inserted i,
                       deleted d
        WHERE d.col1 = i.col1
            AND d.col2 = i.col2

    IF @rc1 = @rc2
        PRINT 'No primary key columns were updated to a different value. Generate update'
    ELSE
        PRINT 'At least one primary key column was updated to a different value. Generate delete/insert'
END

Change # 2:
Create the trigger template text in the UPDATE trigger that would create the trigger like
it used to when a parameter is set. Otherwise, use the delete/insert mechanism.
Tagsdialect: sql-server, dialect: sybase, trigger

Activities

pmarzullo

2022-06-27 20:01

developer   ~0002089

There is a new parameter that was added in order to allow the specification of the update trigger to be as it was in previous versions.
That parameter is:
trigger.use.insert.delete.for.primary.key.changes

The default is true, which means that when more than one record is updated by a statement that changes primary keys, then deletes followed by inserts will be inserted into sym_data to specify the primary key change.

If the parameter is set to false, then updates will be inserted into sym_data for each row updated, using the original logic for ordering the deleted and inserted result sets inside the trigger for matching the deletes to the inserts.

Related Changesets

SymmetricDS: 3.14 4b67163f

2022-06-27 19:56:49

pmarzullo

Details Diff
0005157: Sybase and SQL Server update trigger should be made more
efficient generating sym_data records when primary keys are changed
Affected Issues
0005157
mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/ase/AseTriggerTemplate.java Diff File
mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/mssql/MsSqlTriggerTemplate.java Diff File
mod - symmetric-core/src/main/java/org/jumpmind/symmetric/common/ParameterConstants.java Diff File
mod - symmetric-core/src/main/resources/symmetric-default.properties Diff File

SymmetricDS: 3.14 84b844d7

2022-06-27 20:02:58

pmarzullo

Details Diff
0005157: Sybase and SQL Server update trigger should be made more
efficient generating sym_data records when primary keys are changed
Affected Issues
0005157
mod - symmetric-core/src/main/resources/symmetric-default.properties Diff File

SymmetricDS: 3.14 8e958e8d

2022-06-27 20:05:37

pmarzullo

Details Diff
0005157: Sybase and SQL Server update trigger should be made more
efficient generating sym_data records when primary keys are changed
Affected Issues
0005157
mod - symmetric-core/src/main/resources/symmetric-default.properties Diff File

Issue History

Date Modified Username Field Change
2021-12-14 21:37 pmarzullo New Issue
2021-12-14 21:37 pmarzullo Status new => assigned
2021-12-14 21:37 pmarzullo Assigned To => pmarzullo
2021-12-14 21:37 pmarzullo Tag Attached: dialect: sybase
2021-12-14 21:37 pmarzullo Tag Attached: trigger
2021-12-20 14:25 admin Target Version 3.13.2 => 3.14.0
2022-06-27 19:51 pmarzullo Tag Attached: dialect: sql-server
2022-06-27 19:53 pmarzullo Summary Sybase update trigger should be enhanced to be more efficient generating sym_data records when primary keys are changed => Sybase and SQL Server update trigger should be made more efficient generating sym_data records when primary keys are changed
2022-06-27 19:53 pmarzullo Description Updated View Revisions
2022-06-27 19:58 pmarzullo Status assigned => resolved
2022-06-27 19:58 pmarzullo Resolution open => fixed
2022-06-27 19:58 pmarzullo Fixed in Version => 3.14.0
2022-06-27 20:00 pmarzullo Changeset attached => SymmetricDS 3.14 4b67163f
2022-06-27 20:01 pmarzullo Note Added: 0002089
2022-06-27 21:00 pmarzullo Changeset attached => SymmetricDS 3.14 8e958e8d
2022-06-27 21:00 pmarzullo Changeset attached => SymmetricDS 3.14 84b844d7
2022-07-27 18:59 admin Status resolved => closed