View Revisions: Issue #5157

All RevisionsBack to Issue
Summary 0005157: Sybase and SQL Server update trigger should be made more efficient generating sym_data records when primary keys are changed
Revision 2022-06-27 19:53 by pmarzullo
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.
Revision 2021-12-14 21:37 by pmarzullo
Description From 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.