Trigger on one table field works for all table fields

Question

I have a trigger which is for a few fields in a table. But for some reason, if another field is changed (which is not defined in trigger) then it still fires.

CREATE OR REPLACE TRIGGER INTEGRATION_EMPLOYMENT
    AFTER UPDATE OF start_day_of_employment, end_of_employment ON hr_employment_data
    FOR EACH ROW
    DECLARE
    BEGIN
         IF UPDATING THEN
            MERGE INTO ad_integration intg USING dual ON (intg.user_id = :NEW.user_id AND intg.integrated = 'NO')
            WHEN MATCHED THEN
                UPDATE SET
                         intg.start_day_of_employment = decode(:NEW.start_day_of_employment, NULL, ' ', :NEW.start_day_of_employment),
                         intg.end_of_employment = decode(:NEW.end_of_employment, NULL, ' ', :NEW.end_of_employment),
                         intg.manager_status = :NEW.manager_status,
                         intg.pid = (SELECT pid FROM arc.user_info WHERE user_id = :NEW.user_id),
                         intg.network_access_start_date = (SELECT network_access_start_date FROM hr_extension_data WHERE user_id = :NEW.user_id)
             WHEN NOT MATCHED THEN
                INSERT (intg.user_id, intg.start_day_of_employment, intg.end_of_employment, intg.manager_status, intg.pid, intg.network_access_start_date
                VALUES (:NEW.user_id, :NEW.start_day_of_employment, :NEW.end_of_employment, :NEW.manager_status, (SELECT pid FROM arc.user_info WHERE user_id = :NEW.user_id), (SELECT network_access_start_date FROM hr_extension_data WHERE user_id = :NEW.user_id));
END IF;

END HR_ADINTEGRATION_EMPLOYMENT;

Is it because of using DUAL or something am I missing?

Cheers! :-)

probably you have another trigger that not specifies the columns. — Florin Ghita, Jul 31 '12 at 06:26
How are you updating HR_EMPLOYMENT_DATA? If you're updating it through some sort of user interface, it's possible that the user interface updates all columns, even if the values of only some of the columns have changed. Also, do you have a good reason for not using a view to look up values from HR_EMPLOYMENT_DATA on demand (instead of using triggers to try to keep AD_INTEGRATION up-to-date)? It's hard to do this correctly using triggers. Do you have insert and delete triggers on HR_EMPLOYMENT_DATA? What about update and insert triggers on AD_INTEGRATION? — Brian Camire, Jul 31 '12 at 11:24
yes, there are triggers which are the culprit so I guess that using view is the best option. (Why didn't I think of this before?) — Jaanna, Aug 01 '12 at 05:28
So, why was it triggering? That is still unanswered, was Brian right with his guess that the user interface does an update on all columns even if not changed? I cannot see anything wrong with the trigger. Besides the very smart comment that you could use a view rather than keeping redundant data. — hol, Aug 04 '12 at 09:34

score 2 · Accepted Answer · answered Oct 18 '12 at 10:10

If you want to leave the structure as is and only process the trigger when the specifc fields change, then just do a quick compare (new code lines 7 and 8):

CREATE OR REPLACE TRIGGER INTEGRATION_EMPLOYMENT
AFTER UPDATE OF start_day_of_employment, end_of_employment ON hr_employment_data
FOR EACH ROW
DECLARE
BEGIN
     IF UPDATING 
        AND (:NEW.start_day_of_employment <> :OLD.start_day_of_employment
        OR   :NEW.end_of_employment <> :OLD.end_of_employment)  THEN
        MERGE INTO ad_integration intg USING dual ON (intg.user_id = :NEW.user_id AND intg.integrated = 'NO')
        WHEN MATCHED THEN
            UPDATE SET
                     intg.start_day_of_employment = decode(:NEW.start_day_of_employment, NULL, ' ', :NEW.start_day_of_employment),
                     intg.end_of_employment = decode(:NEW.end_of_employment, NULL, ' ', :NEW.end_of_employment),
                     intg.manager_status = :NEW.manager_status,
                     intg.pid = (SELECT pid FROM arc.user_info WHERE user_id = :NEW.user_id),
                     intg.network_access_start_date = (SELECT network_access_start_date FROM hr_extension_data WHERE user_id = :NEW.user_id)
         WHEN NOT MATCHED THEN
            INSERT (intg.user_id, intg.start_day_of_employment, intg.end_of_employment, intg.manager_status, intg.pid, intg.network_access_start_date
            VALUES (:NEW.user_id, :NEW.start_day_of_employment, :NEW.end_of_employment, :NEW.manager_status, (SELECT pid FROM arc.user_info WHERE user_id = :NEW.user_id), (SELECT network_access_start_date FROM hr_extension_data WHERE user_id = :NEW.user_id));
END IF;

END HR_ADINTEGRATION_EMPLOYMENT;

Trigger on one table field works for all table fields

1 Answers1