Trigger compiles, valid but won't trip
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Trigger compiles, valid but won't trip

  1. #1
    Join Date
    Oct 2000
    Posts
    2

    Angry

    HI, I'm using sqlloader to insert data into a table. I have created a compiled and valid trigger that won't trip. I would like the trigger to see if the row already exists on the table (I have a 4 column primary key) if it already exists update it. But if it doesn't exist insert it. I am loading in 10 dummy records, 5 are updates and 5 are inserts. So after the load there should only be 5 new records but there are 10. It seems that it is inserting all 10 no matter what the trigger is set up to do. Here is my sql, any help would be greatly appreciated. This is the first trigger I have ever written and I know no plsql.

    CREATE OR REPLACE TRIGGER COMP_DATA_TRIG
    INSTEAD OF INSERT ON COMP_DATA_VW
    REFERENCING NEW AS n
    FOR EACH ROW
    DECLARE
    rowcnt number;
    BEGIN
    SELECT COUNT(*) INTO rowcnt FROM comparison_data
    WHERE rfp_id = :n.rfp_id
    and option_id=:n.option_id
    and coverage_id=:n.coverage_id
    and network_type_id=:n.network_type_id;
    IF rowcnt >0 THEN
    UPDATE comparison_data
    SET
    value = :n.value,
    unit_of_measure_id = :n.unit_of_measure_id,
    comparison_descr = :n.comparison_descr,
    created_on = :n.created_on,
    created_by = :n.created_by,
    cost_type_id = :n.cost_type_id,
    copay_coins = :n.copay_coins,
    last_updated_on = :n.last_updated_on,
    last_updated_by = :n.last_updated_by,
    category = :n.category
    WHERE rfp_id = :n.rfp_id
    and coverage_id = :n.coverage_id
    and option_id = :n.option_id
    and network_type_id = :n.network_type_id;
    ELSE
    INSERT INTO comparison_data
    (rfp_id,option_id, coverage_id, network_type_id, value, unit_of_measure_id, comparison_descr, created_on, c
    reated_by, cost_type_id, copay_coins, last_updated_on, last_updated_by, category)
    VALUES (:n.rfp_id, :n.option_id, :n.coverage_id, :n.network_type_id, :n.value, :n.unit_of_measure_id, :n.comp
    arison_descr, :n.created_on, :n.created_by, :n.cost_type_id, :n.copay_coins, :n.last_updated_on, :n.last_upda
    ted_by, :n.category);
    END IF;
    END;
    /

  2. #2
    Join Date
    Jul 2000
    Posts
    296
    Your trigger is an instead of trigger created on a view. A view on a table comp_data i suppose. SQL*Loader loads data in a table, not in a view. The trigger wil not fire on inserts on the table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width