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.
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;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments