Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Posts
    16

    Unanswered: mulitple updates within trigger

    I am using an instead of update trigger. If I include a single update statement within the instead of update trigger then the update statement gets called. If I include two update statements within the instead of update trigger then the following error is returned: "Data manipulation operation not legal on this view".

    If I use a trigger can I include two subsequent update statements within the same trigger or do I need to use a different approach to update multiple tables?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: mulitple updates within trigger

    Originally posted by aciccarelli
    I am using an instead of update trigger. If I include a single update statement within the instead of update trigger then the update statement gets called. If I include two update statements within the instead of update trigger then the following error is returned: "Data manipulation operation not legal on this view".

    If I use a trigger can I include two subsequent update statements within the same trigger or do I need to use a different approach to update multiple tables?
    There is no restriction that says you can't have 2 update statements in a trigger. The problem is presumably with the 2nd update statement. Post your view definitions and trigger code.

  3. #3
    Join Date
    Jul 2003
    Posts
    16
    TRIGGER
    ------------
    CREATE OR REPLACE TRIGGER person_info_update
    INSTEAD OF UPDATE ON PERSON
    REFERENCING NEW AS n -- new person id num
    BEGIN
    UPDATE tr_prsn
    SET org_id = nvl(:n.org_key,:n.office_key),
    fname = :n.fname,
    mname = :n.mname,
    lname = :n.lname,
    last_updt_date = :n.last_updt_date,
    last_updt_user_id = :n.last_updt_user_id
    WHERE person_id = :n.person_key;

    UPDATE tr_prsn_idnum
    SET identification_nbr = :n.ssn,
    prsn_idnum_code = decode(:n.idtyp_id,1,'USSSN',2,'',3,'',4,'',5,'')
    WHERE person_id = :n.person_key;
    END;

    The update is failing on the second update to tr_prsn_idnum which is a table because when I remove the second update statement the trigger succeeds.

  4. #4
    Join Date
    Jul 2003
    Posts
    16
    Never mind - it is working now.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by aciccarelli
    TRIGGER
    ------------
    CREATE OR REPLACE TRIGGER person_info_update
    INSTEAD OF UPDATE ON PERSON
    REFERENCING NEW AS n -- new person id num
    BEGIN
    UPDATE tr_prsn
    SET org_id = nvl(:n.org_key,:n.office_key),
    fname = :n.fname,
    mname = :n.mname,
    lname = :n.lname,
    last_updt_date = :n.last_updt_date,
    last_updt_user_id = :n.last_updt_user_id
    WHERE person_id = :n.person_key;

    UPDATE tr_prsn_idnum
    SET identification_nbr = :n.ssn,
    prsn_idnum_code = decode(:n.idtyp_id,1,'USSSN',2,'',3,'',4,'',5,'')
    WHERE person_id = :n.person_key;
    END;

    The update is failing on the second update to tr_prsn_idnum which is a table because when I remove the second update statement the trigger succeeds.
    Nothing obviously wrong.

    So what happens if:

    1) Your remove the first update and keep the second?

    2) You run the second update as a standalone statement in SQL Plus?

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by aciccarelli
    Never mind - it is working now.
    What was the problem?

Posting Permissions

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