Well if it's just a case of supplying a 'default' value for a column by using a trigger the code in the before insert trigger would be something like:
:NEW.name_column := NVL(:NEW.name_column, 'Lubna');
If that's not what you want, please explain more clearly what your issue is.
Ok.. I will try to ask my question in dofferant way .. "Wisper: ReallyI don't have time to read that topic , maybe later "..
I have 2 tables :
I want to create a trigger in receipt table such that it will insert into jobs table the new inserted job id in receipt , into jobs table only if its not exists in Jobs_ID column :
so what i want this statment that will check in JOB_ID column in jobs table the existance of the new inserted job_ID value ..
How is it now ?
You need to simply create a trigger on the Receipt table . But on which action you want to trigger the action ?
There are different types of trigger , BEFORE INSERT , AFTER INSERT , BEFORE UPDATE , AFTER UPDATE , BEFORE DELETE , AFTER DELETE.
First choose the action then go as given below ,
CREATE OR REPLACE TRIGGER orders_before_insert
FOR EACH ROW
SELECT Job_Id INTO v_job_id FROM Jobs WHERE job_id in ( SELECT Job_Id FROM dual ) ;
if v_job_id is null then
-- your insert statement
end if ;
hhhhhhhhhhhhhh .. i killed Lubna
As what i understand from your code .. insert new job that came from dummy table only if not exists in .. umm .. wait wait ,.. can i write it like that ..?
insert into jobs(job_id) select :new.job_id from dual where not exists (select Job_ID from jobs where .job_id = :new.job_id); --
Asif i added new thing .. hhhhhhhh..
Any way thanks alot ,.
No problem in that. It's just that if the value is already there (this is up to OP to know the likelihood), then the select statement will be successful so no need to proceed further. Another reason (about which I doubt here), is that if there are job_id duplicates in jobs then I think the select with rownum will be less expensive.