Re: Triggers and :new :old

David,
You have defined a statement trigger, not a row trigger.

See the section "Firing Triggers One or Many Times (FOR EACH ROW Option)" in the "Application Developer's Guide - Fundamentals" manual where it states
"Old and new values are available in both BEFORE and AFTER row triggers"

HTH,
Neil

"David" <auto90059_at_hushmail.com> wrote in message news:3f6c3286$0$10357$afc38c87_at_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 - 12:16:49 CDT