View Issue Details

IDProjectCategoryView StatusLast Update
0005157SymmetricDSImprovementpublic2021-12-20 14:25
Reporterpmarzullo Assigned Topmarzullo  
Prioritynormal 
Status assignedResolutionopen 
Product Version3.13.1 
Target Version3.14.0 
Summary0005157: Sybase update trigger should be enhanced to be more efficient generating sym_data records when primary keys are changed
DescriptionFrom issue #EVJ-56133-571

Change 0000001:
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 0000002:
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: sybase, trigger

Activities

There are no notes attached to this issue.

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