Using oracle triggers to update a second table where a condition has been

Question

I'm new to pl/sql and grappling with triggers. I am required to use a trigger for this code. I have 2 tables, job (job_id, job_name, job_price) and job_history (job_id, oldprice, datechanged). I'm trying to create a trigger that adds the old job details to the job_history table when the job_price field in the job table is updated either if no row already exist or if the new job price for that job id is more than any previously stored prices for that job id in the job_history table. The job id field in the job table cannot have duplicates but the job id field in the job_history table can have duplicates. Further, if the condition is not met, that is, the new job price is less than all previously stored prices for that job id, then the error should be trapped.

I've tried this code:

CREATE OR REPLACE TRIGGER conditional_update_job_hist
AFTER UPDATE OF jbsprice ON job
FOR EACH ROW
WHEN (new.jbsprice)<min(old.jbsprice);
BEGIN
INSERT INTO job_history (jbsid, oldprice) VALUES (:old.jbsid,:old.jbsprice);
IF :new.price is<>min(oldprice) THEN
RAISE_APPLICATION_ERROR('Condition not met.');
ENDIF;
END;
/

This resulted in an error at line 4 ORA-00920: invalid relational operator.

I've checked the oracle online documentation. It's confusing. Do I need to use a cursor and loop inside the trigger? The less than operator looks okay and the min(function) looks okay. I cannot see where I'm going wrong. Please help.

Answer 1

at a first glance, I would suggest the following:

CREATE OR REPLACE TRIGGER conditional_update_job_hist
  AFTER UPDATE OF jbsprice ON job
  FOR EACH ROW
DECLARE
  hist_exists number;
BEGIN
  hist_exists := 0;
  begin
    -- select 1 if there is an entry in job_history of that jbsid 
    -- and an oldprice exists which is more than new jbsprice
    select distinct 1
    into hist_exists
    from job_history
    where jbsid = :old.jbsid
    and oldprice > :new.jbsprice;
  exception when no_data_found then hist_exists := 0;
  end;
  IF hist_exists = 0 then
    INSERT INTO job_history (jbsid, oldprice) VALUES (:old.jbsid,:old.jbsprice);
  END IF;
END;
/