Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2009
    Posts
    9

    Unanswered: Trigger Copy and delete

    Hi,

    i have created a trigger that will copy a row from one table to another table when the value in current_status is 4.

    The sql code i have created below works in sql developer:

    Code:
    INSERT INTO closed_calls (call_id, contract_number, fid, pid, description, call_date, problem_severity, call_method, caller_name, telephone_number, email_address, sid)
     SELECT call.call_id, contract_number, fid, pid, description, call_date, problem_severity, call_method, caller_name, telephone_number, email_address, sid
       FROM call inner join action on call.call_id=action.call_id
       WHERE current_status = 4
    The code for the trigger below, when run comes up with an error message saying:

    UPDATE "APEX10"."CALL" SET CURRENT_STATUS = '4' WHERE ROWID = 'AAAosGAAEAAAWEEAAA' AND ORA_ROWSCN = '562563771'

    One error saving changes to table "APEX10"."CALL":
    Row 1: ORA-04098: trigger 'APEX10.COPY_CALL' is invalid and failed re-validation


    Code:
    create or replace trigger "COPY_CALL"
    AFTER
    insert on "CALL"
    for each row
    begin
    INSERT INTO closed_calls (call_id, contract_number, fid, pid, description, call_date, problem_severity, call_method, caller_name, telephone_number, email_address, sid)
     SELECT call.call_id, contract_number, fid, pid, description, call_date, problem_severity, call_method, caller_name, telephone_number, email_address, sid
       FROM call inner join action on call.call_id=action.call_id
       WHERE current_status = 4
    end;
    I just want to know if anyone can tell me why the code works but the trigger doesnt?

    Also, after copying the row across from the call table to the closed_call table I want to delete the row from the call table. I have written the sql code for this but want to know if its possible to include it in this trigger after it copys the data across:

    Code:
    delete from call
    where current_status = 4
    Thanks for any help.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I just want to know if anyone can tell me why the code works but the trigger doesnt?
    Privileges acquired via ROLE do not apply within PL/SQL procedures; including triggers.

    explicit GRANT on object to trigger owner is required
    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.

  3. #3
    Join Date
    Dec 2009
    Posts
    9
    Do i put the grant in my trigger? or does that go in my table

    thanks

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by gisler View Post
    Do i put the grant in my trigger? or does that go in my table

    thanks
    >or does that go in my table
    I have no idea what this is supposed to mean.

    After http://tahiti.oracle.com (Unbreakable Oracle) comes back online
    Read The Fine Manual, SQL Reference Manual on GRANT
    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.

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

    Thumbs down ORA-04091: table name is mutating

    Your trigger has a "Mutating Table" error (in red):
    Code:
    CREATE OR REPLACE TRIGGER "COPY_CALL"
        AFTER INSERT
        ON "CALL"
        FOR EACH ROW
    BEGIN
        INSERT INTO Closed_calls (
                                      Call_id, Contract_number, Fid, Pid, Description, Call_date, Problem_severity, Call_method, Caller_name, Telephone_number, Email_address, Sid
                   )
            SELECT   Call.Call_id, Contract_number, Fid, Pid, Description,
                     Call_date, Problem_severity, Call_method, Caller_name,
                     Telephone_number, Email_address, Sid
              FROM       Call
                     INNER JOIN
                         Action
                     ON Call.Call_id = Action.Call_id
             WHERE   Current_status = 4;
    END;
    /
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Dec 2009
    Posts
    9
    how do i fix this? surely if i wish to do an inner join then i need the 'from call'?

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Within Oracle trigger, you are precluded from doing any SQL against the table upon which the trigger is based.

    Here is the reason behind this restriction.
    The table is already undergoing some sort of DML operation; which is how/why the trigger gets initiated.

    Now if SQL were allowed against same table, what values get returned; the new or old.
    Should Oracle return pre-DML results or post-DML results?
    To avoid this ambiguity, Oracle just does not allow SQL against underlying base table.
    Deal with this restriction or find a different solution.
    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.

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

    Cool No join

    Quote Originally Posted by gisler View Post
    how do i fix this? surely if i wish to do an inner join then i need the 'from call'?
    You do not need a "join", you use the old/new values from the trigger to do your select on the other table.

    Similar to this:
    Code:
    CREATE OR REPLACE TRIGGER "COPY_CALL"
        AFTER INSERT
        ON "CALL"
        FOR EACH ROW
    BEGIN
        INSERT INTO Closed_calls (
                                      :new.Call_id, Contract_number, Fid, Pid, 
                                      Description, Call_date, Problem_severity, 
                                      Call_method, Caller_name, Telephone_number, 
                                      Email_address, Sid
                   )
            SELECT   :new.Call_id, Contract_number, Fid, Pid, Description,
                     Call_date, Problem_severity, Call_method, Caller_name,
                     Telephone_number, Email_address, Sid
              FROM   Action
             WHERE   :new.Call_id = Action.Call_id AND Current_status = 4;
    END;
    /
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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