Scott Leslie Scott Leslie - 1 month ago 16
SQL Question

sql oracle, error "PL/SQL: SQL Statement ignored" and "PL/SQL: ORA-00917: missing comma"

Code:

create or replace TRIGGER populate_title
AFTER INSERT
ON SITES


BEGIN

INSERT INTO sites
( job_title )
VALUES
( (SELECT title FROM jobs WHERE job_id IN (SELECT job_id FROM SITES) );

END;


So basically, we are trying to populate a field in the column "job_title" on table "sites," but the information is in another table, "jobs." We have errors and are now stuck. Please help.

Answer

First, if you managed to get this to compile, you would probably have a mutating table problem.

The trigger should be written as a before insert trigger:

create or replace TRIGGER populate_title
BEFORE INSERT
ON SITES
BEGIN 
    SELECT j.title INTO :NEW.job_title
    FROM jobs j
    WHERE j.job_id = :NEW.job_id;
END;

However, a trigger for this is ludicrous. The right way to get this information is to query and use JOIN:

select s.*, j.title
from sites s join
     jobs j
     on s.job_id = j.job_id;
Comments