Triggers and :new :old

From: David <auto90059_at_hushmail.com>
Date: Sat, 20 Sep 2003 20:57:09 +1000
Message-ID: <3f6c3286$0$10357$afc38c87@news.optusnet.com.au>

I want to write a trigger that updates a row in the Experts table when the equivalent row in the AssignedTo table is updated (AssignedTo.Expert is a foreign key referencing Experts.ExpertID)

SELECT Status, PersonID

        INTO v_status, v_person_id
        FROM AssignedTo, People
        WHERE AssignedTo.Expert = People.PersonID
        AND ProcessOpinion.expert = People.Name
        AND ExpAddress = People.Address;
        IF v_status = 'Defunct' THEN
            RAISE expert_defunct;
        ELSE
            UPDATE AssignedTo
                SET Status = result
            WHERE Expert = v_person_id;
        END IF;

          -- make this a trigger
        UPDATE Experts
            SET Pending = Pending - 1
        WHERE ExpertID = v_person_id;

This doesn't compile. What am I doing wrong? How do I get at the value of v_person_id within the scope of the trigger?

CREATE OR REPLACE TRIGGER assigned_to_ai_trigger AFTER INSERT ON AssignedTo
BEGIN
    UPDATE Experts

            SET Pending = Pending - 1
    WHERE ExpertID = :old.Expert;
END assigned_to_ai_trigger;
/

ERROR at line 1:
ORA-04082: NEW or OLD references not allowed in table level triggers

I want to do something similar with this:

IF v_found AND v_pending = 0 AND v_thisyear < 3

                AND v_expert_status != 'Delinquent' THEN
                INSERT INTO AssignedTo VALUES
                    (p_manuscript, v_expert_id, TRUNC (SYSDATE) + 100,

'Waiting');

• make this a trigger UPDATE Experts SET ThisYear = ThisYear + 1, Pending = Pending + 1 WHERE CURRENT OF expert_cur;

How do I address the row referenced by the expert_cur cursor within my trigger? Received on Sat Sep 20 2003 - 05:57:09 CDT