Re: Triggers and :new :old

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

:OLD and :NEW are only available in row level triggers. The header syntax is:

CREATE OR REPLACE TRIGGER <trigger_name> BEFORE UPDATE
ON <table_name>
FOR EACH ROW DECLARE
   ...
BEGIN
   ...
EXCEPTION
   ...
END <trigger_name>;
/

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)