Re: Triggers and :new :old

On Sat, 20 Sep 2003 20:57:09 +1000, "David" <auto90059_at_hushmail.com> wrote:

>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?
>

You currently have a trigger which fires once for each statement, how many rows you insert or update doesn't matter, you are firing only once.
The first lines of your post however indicate you need to have a trigger that for fires 'for each row'
Just add those words to after 'after insert' and you'll be ok. In a statement level trigger you can't access individual rows, in a row level trigger you can.

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Sat Sep 20 2003 - 08:12:15 CDT