Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2009
    Posts
    6

    Unanswered: delete from trigger

    hello!
    i have my trigger that should delete data from sql server table after delete oracle table.
    when i trying to delete from oracle table i have error:
    SQL> delete from cust.SUBSCRIBER;
    delete from cust.SUBSCRIBER
    *
    ERROR at line 1:
    ORA-01403: no data found
    ORA-06512: at "PROC.AFTER_INSERT_SUB", line 12
    ORA-04088: error during execution of trigger 'PROC.AFTER_INSERT_SUB'

    i have a data on 2 tables:
    this is my 2 tables:
    --oracle table
    SQL> select * from cust.SUBSCRIBER;

    SUB_ID CUST_ID SUB_NAME SUB_LAST_NAME SU FEATURE_ID PACKAGE_ID ADRESS_ID
    ---------- ---------- ------------------------- ------------------------- -- ---------- ---------- ----------
    544444445 8 Rivka Malik f 2 3 4

    -sql table:

    SQL> select * from billing.pack_sub@billing;

    sub_id package_cost phone_cost sms_cost multi_cost
    ---------- ------------ ---------- ---------- ----------
    544444445 7 .5 .5 2

    my trigger:
    create or replace trigger after_insert_sub
    AFTER INSERT OR UPDATE OR DELETE ON cust.subscriber
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
    v_package_cost number;
    v_phone_cost number;
    v_sms_cost number;
    v_multi_cost number;
    BEGIN
    select package_cost, phone_cost, sms_cost, internet_cost into v_package_cost, v_phone_cost, v_sms_cost, v_multi_cost from cust.packages
    where package_id = :new.package_id;
    if INSERTING THEN
    insert into billing.pack_sub@billing
    values (:new.sub_id, v_package_cost, v_phone_cost, v_sms_cost, v_multi_cost);
    commit;
    elsif UPDATING ('package_id') THEN
    UPDATE billing.pack_sub@billing set "sub_id" = :new.sub_id,
    "package_cost" = v_package_cost,
    "phone_cost" = v_phone_cost,
    "sms_cost" = v_sms_cost,
    "multi_cost" = v_multi_cost;
    ELSIF DELETING THEN
    DELETE FROM billing.pack_sub@billing where "sub_id" = ld.sub_id);
    end if;
    end;

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    This select will fail for a DELETE:
    Code:
    select package_cost, phone_cost, sms_cost, internet_cost into v_package_cost, v_phone_cost, v_sms_cost, v_multi_cost from cust.packages
    where package_id = :new.package_id;
    The :new value will be null, resulting in a NO_DATA_FOUND exception.
    You could change it to:
    Code:
    select package_cost, phone_cost, sms_cost, internet_cost into v_package_cost, v_phone_cost, v_sms_cost, v_multi_cost from cust.packages
    where package_id = NVL(:new.package_id,:old.package_id);

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Or just re-order the dml

    Or just re-order the dml:
    Code:
    CREATE OR REPLACE TRIGGER after_insert_sub AFTER
      INSERT OR
      UPDATE OR
      DELETE ON cust.subscriber 
      REFERENCING NEW AS NEW OLD AS OLD 
      FOR EACH ROW 
      DECLARE 
      PRAGMA AUTONOMOUS_TRANSACTION;
      
      v_package_cost NUMBER;
      v_phone_cost   NUMBER;
      v_sms_cost     NUMBER;
      v_multi_cost   NUMBER;
      BEGIN
        IF DELETING THEN
          DELETE FROM billing.pack_sub@billing WHERE "sub_id" = :old.sub_id;
        ELSE
          SELECT Package_cost,
            Phone_cost,
            Sms_cost,
            Internet_cost
          INTO V_package_cost,
            V_phone_cost,
            V_sms_cost,
            V_multi_cost
          FROM Cust.Packages
          WHERE Package_id = :New.Package_id;
          IF INSERTING THEN
            INSERT
            INTO Billing.Pack_sub@Billing VALUES
              (
                :New.Sub_id,
                V_package_cost,
                V_phone_cost,
                V_sms_cost,
                V_multi_cost
              );
            COMMIT;
          ELSIF UPDATING
            (
              'package_id'
            )
            THEN
            UPDATE Billing.Pack_sub@Billing
            SET "sub_id"     = :New.Sub_id,
              "package_cost" = V_package_cost,
              "phone_cost"   = V_phone_cost,
              "sms_cost"     = V_sms_cost,
              "multi_cost"   = V_multi_cost;
          END IF;
        END IF;
      END;
    Last edited by LKBrwn_DBA; 12-23-09 at 15:20.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Dec 2009
    Posts
    6
    thank...
    now im trying to run pl sql and i have error:
    DECLARE
    *
    ERROR at line 1:
    ORA-01403: no data found
    ORA-01403: no data found
    ORA-06512: at "PROC.AFTER_INSERT_SUB", line 11
    ORA-04088: error during execution of trigger 'PROC.AFTER_INSERT_SUB'
    ORA-06512: at line 29

    im trying to insert that:
    DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
    a cust.subscriber.cust_id%type;
    b cust.features.feature_id%type;
    c cust.packages.package_id%type;
    d cust.adresses.adress_id%type;
    CURSOR cust_cur
    IS
    select cust_id into a FROM (SELECT cust_id
    FROM cust.customers
    ORDER BY dbms_random.value);
    CURSOR cust_feat
    IS
    select feature_id into b FROM (SELECT feature_id
    FROM cust.features
    ORDER BY dbms_random.value );
    CURSOR cust_pack
    IS
    select package_id into c FROM (SELECT package_id
    FROM cust.packages
    ORDER BY dbms_random.value);
    CURSOR cust_adress
    IS
    select adress_id into d FROM (SELECT adress_id
    FROM cust.adresses
    ORDER BY dbms_random.value);

    begin
    Insert into cust.SUBSCRIBER Values (0544444445, c, a, 'Rivka' , 'Malik', 'ss', b, d);
    commit;
    insert into cust.SUBSCRIBER Values (0547487632, c, a, 'Rachel' , 'Shwartzman', 'dd', b, d);

    commit;
    end;

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    posted code did not throw posted error.
    posted code has syntax errors & won't even compile

    using sqlplus along with CUT & PASTE, show us what you really did & how Oracle responded
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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