| trigger help [message #9594] |
Fri, 21 November 2003 18:35 |
pat
Messages: 27
Registered: September 2000
|
Junior Member |
|
|
Hello. I am trying to update/insert/delete table A based on a value in table C (say empnum) based on an insert in table B. Once when I got something to insert into table A I was missing the "primary key (empnum). Can anyone provide any direction to help me solve the problem with I am getting? Below is a sample of the code I'm using.
create or replace trigger empupdinsdel
after insert or update on B
for each row
declare
v_empno emptab.apno%type;
v_useno use.empkey%type;
v_reckey rec.reckey%type;
v_count number;
begin
if :new.reckey is null then
v_reckey := :old.reckey;
else
v_reckey := :new.reckey;
end if;
select to_number(substr(a.empno,1,6))
into v_empno
from emptab a, use b
where a.reckey = v_useno and b.reckey = v_reckey;
select count(*)
into v_count
from note
where empid = v_empno;
if v_count = 0 and :new.emptype = 'EXECUTIVE' then
insert into note (empid, notedt, empno, notes)
values (v_empno,
:new.strtdttm,
:new.entrdby,
:new.comments);
elsif updating then
update note set notedt = :new.strtdttm,
empno = :new.entrdby,
notes = :new.comments
where empid = v_empno;
end if;
end;
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
