0

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;
/

2
Contributors
1
Reply
2
Views
6 Years
Discussion Span
Last Post by debasisdas
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.