Hello, I am fairly new to SQL and PLSQL and am having a problem debugging some code for triggers, the compiler keeps saying that the trigger is not initialized properly. Here is a posting of the code. This is for a homework assignment, but I am lost so I would appreciate the help. These are with the standard emp, dept, bonus, s, sp and p tables from oracle
- Problem 2
create or replace trigger salary_cap_trigger
before update on emp
for each row
type emp_array is table of emp%rowtype index by varchar2(10);
emp_arr emp_array;
v_diff number;
begin
for sal_rec in (select * from emp) loop
if upper(new.job) != upper(emp_arr(sal_rec.job)) and new.sal > emp_arr(sal_rec.sal) then
raise_application_error(-20100, 'Job modifcation not permitted');
elsif upper(emp_arr(sal_rec.job)) = 'ANALYST' and emp_arr(sal_rec.sal) > 4000 then
v_diff := sal - 4000;
update into emp (ename, job, sal, comm) values (emp_arr(sal_rec.ename), emp_arr(sal_rec.job), 4000, v_diff);
elsif upper(emp_arr(sal_rec.job)) = 'CLERK' and emp_arr(sal_rec.sal) > 1500 then
v_diff := sal-1500;
update into emp (ename, job, sal, comm) values (emp_arr(sal_rec.ename), emp_arr(sal_rec.job), 1500, v_diff);
elsif upper(emp_arr(sal_rec.job)) = 'MANAGER' and emp_arr(sal_rec.sal) > 3500 then
v_diff := sal - 3500;
update into emp (ename, job, sal, comm) values (emp_arr(sal_rec.ename), emp_arr(sal_rec.job), 3500, v_diff);
elsif upper(emp_arr(sal_rec.job)) = 'SALESMAN' and emp_arr(sal_rec.sal) > 2000 then
v_diff := sal - 2000;
update into emp (ename, job, sal, comm) values (emp_arr(sal_rec.ename), emp_arr(sal_rec.job), 2000, v_diff);
end if;
end loop;
end salary_cap_trigger;
/
-- ////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- Problem 3a
create or replace trigger cascade_change_s#_trigger
before update on s
for each row
begin
update s set s# = :new.s# where s# =: old.s#;
end cascade_change_s#_trigger;
/
-- ////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- Problem 3b
create or replace trigger cascade_change_p#_on_sp_trigger
before update on sp
for each row
begin
update sp set p# = :new.p# where p# =: old.p#;
end cascade_change_p#_on_sp_trigger;
/
-- ////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- Problem 4
create or replace trigger heavy_weight_trigger
before insert or update on p
for each row
when (new.weight > 10)
begin
update into p (color) values ('red');
end heavy_weight_trigger;
/
-- ////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- Problem 5
create or replace trigger light_weight_trigger
after insert or update on p
for each row
when(old.weight < 10)
begin
update into p (color) values ('blue');
end light_weight_trigger;
/
