Why the trigger was created with this code

Oct 13, 2015 at 8:32 AM
Hi
I have following code to initialization
    ModelToTableMapper<DashboardJobQueueModel> mapper = new ModelToTableMapper<DashboardJobQueueModel>();
            mapper.AddMapping(s => s.Build_Id, "JOB_QUEUE_BUILD_ID");
            mapper.AddMapping(s => s.BuidType_Cd, "JOB_QUEUE_BUILD_TYPE_CD");
            mapper.AddMapping(s => s.DataBase_Cd, "JOB_QUEUE_BUILD_DB");
            mapper.AddMapping(s => s.Step_Cd, "JOB_QUEUE_STEP_CD");
            mapper.AddMapping(s => s.Status, "JOB_QUEUE_STEP_STATUS");
            List<String> updateOfcolumns = new List<String>(1);
            updateOfcolumns.Add("JOB_QUEUE_STEP_STATUS");

            String tableName = "F_JOB_QUEUE"

            String connectionString = "MyConnection"
            _namingConventionForDatabaseObjects = String.Format("{0}_{1}_{2}", tableName, _namingConventionForDatabaseObjects, Environment.MachineName);
       
                _tableDependency = new SqlTableDependency<DashboardJobQueueModel>(
                    connectionString
                    , tableName
                    , mapper
                    , updateOfcolumns
                    , _automaticDatabaseObjectsTeardown
                    , _namingConventionForDatabaseObjects
                    );
The result is created trigger with following code
IF NOT EXISTS(SELECT * FROM INSERTED)
    BEGIN
        SET @dmlType = 'Delete'
        INSERT INTO @modifiedRecordsTable SELECT [JOB_QUEUE_BUILD_ID], [JOB_QUEUE_BUILD_TYPE_CD], [JOB_QUEUE_BUILD_DB], [JOB_QUEUE_STEP_CD], [EXECUTED_DT], [INSERTED_DT], [COMPLETED_DT], [JOB_QUEUE_STEP_STATUS] FROM DELETED AS [F_JOB_QUEUE]
    END
    ELSE
    BEGIN
        IF NOT EXISTS(SELECT * FROM DELETED)
        BEGIN
            SET @dmlType = 'Insert'
            INSERT INTO @modifiedRecordsTable SELECT [JOB_QUEUE_BUILD_ID], [JOB_QUEUE_BUILD_TYPE_CD], [JOB_QUEUE_BUILD_DB], [JOB_QUEUE_STEP_CD], [EXECUTED_DT], [INSERTED_DT], [COMPLETED_DT], [JOB_QUEUE_STEP_STATUS] FROM INSERTED AS [F_JOB_QUEUE]
        END
        ELSE
        BEGIN
            IF (UPDATE([JOB_QUEUE_STEP_STATUS])) BEGIN
    SET @dmlType = 'Update'
    INSERT INTO @modifiedRecordsTable SELECT [JOB_QUEUE_BUILD_ID], [JOB_QUEUE_BUILD_TYPE_CD], [JOB_QUEUE_BUILD_DB], [JOB_QUEUE_STEP_CD], [EXECUTED_DT], [INSERTED_DT], [COMPLETED_DT], [JOB_QUEUE_STEP_STATUS] FROM INSERTED AS [F_JOB_QUEUE]
END
ELSE BEGIN
    RETURN
END
        END
    END
Although I expected to see only the code
IF (UPDATE([JOB_QUEUE_STEP_STATUS])) BEGIN
    SET @dmlType = 'Update'
    INSERT INTO @modifiedRecordsTable SELECT [JOB_QUEUE_BUILD_ID], [JOB_QUEUE_BUILD_TYPE_CD], [JOB_QUEUE_BUILD_DB], [JOB_QUEUE_STEP_CD], [EXECUTED_DT], [INSERTED_DT], [COMPLETED_DT], [JOB_QUEUE_STEP_STATUS] FROM INSERTED AS [F_JOB_QUEUE]
Because I'm interested in the notification only when the changed data in column "JOB_QUEUE_STEP_STATUS"

In my case, the data in the table "F_JOB_QUEUE" change often, but does not change the value in "JOB_QUEUE_STEP_STATUS"..
As a result we have got extra notification

Thank
Oct 13, 2015 at 10:11 AM
Hi tolik51

Keep in mind that this is a generic solution for table changes.

This means that even if you specify updateOfcolumns parameter,
DELETE and INSERT must be notified, because those are change on data.
When a new records comes in the table, the "JOB_QUEUE_STEP_STATUS" can be considered as modified (even if it is NOT an update).
When a records is deleted, the "JOB_QUEUE_STEP_STATUS" can be considered as modified, because it does not exists any more..

Regards.