View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0005157 | SymmetricDS | Improvement | public | 2021-12-14 21:37 | 2023-04-19 18:00 |
| Reporter | pmarzullo | Assigned To | pmarzullo | ||
| Priority | normal | ||||
| Status | closed | Resolution | fixed | ||
| Product Version | 3.13.1 | ||||
| Target Version | 3.14.0 | Fixed in Version | 3.14.0 | ||
| Summary | 0005157: Sybase and SQL Server update trigger should be made more efficient generating sym_data records when primary keys are changed | ||||
| Description | From 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. | ||||
| Tags | dialect: sql-server, dialect: sybase, trigger | ||||
|
|
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. |
|
SymmetricDS: 3.14 4b67163f 2022-06-27 19:56:49 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 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 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 928c2286 2023-04-19 17:34:53 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/mssql/MsSqlTriggerTemplate.java | Diff File | ||
| 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 |
| 2023-04-19 18:00 | pmarzullo | Changeset attached | => SymmetricDS 3.14 928c2286 |