Home » SQL & PL/SQL » SQL & PL/SQL » Oracle's trigger on rental_property
| Oracle's trigger on rental_property [message #9197] |
Sat, 25 October 2003 22:31  |
wayne
Messages: 27
Registered: March 2001
|
Junior Member |
|
|
Dear friend,
I want to do a trigger : A staff with position of supervisor, who only can handle maximum three rental_property, if who handle more than three, then error message will prompt out!
I send my table with trigger, please have a look on it, thank you !
create table staff (
staffID varchar2(6) constraint pk_staff primary key,
staffName varchar2(10) unique not null,
position varchar2(10) not null,
gentle varchar2(6) not null,
age number(2) not null,
salary number(4) not null
);
create table rental_property (
propertyNo varchar2(6) constraint pk_rental_property primary key,
street varchar2(15) not null,
city varchar2(10) not null,
type varchar2(10),
staffID varchar2(6) constraint fk_staff_staffID references staff(staffID),
status varchar2(15)
);
insert into staff values ('STA001', 'TitiTo', 'Supervisor', 'Male', 20, 1200);
insert into staff values ('STA002', 'BearBear', 'manager', 'Male', 25, 1200);
insert into rental_property values ('PPT001', 'Jln FLOWER', 'KL', 'HOUSE', 'STA001', 'available');
insert into rental_property values ('PPT002', 'Jln BEAUTY', 'KL', 'HOUSE', 'STA002', 'available');
insert into rental_property values ('PPT003', 'Jln LOVE', 'KL', 'HOUSE', 'STA001', 'available');
insert into rental_property values ('PPT004', 'Jln Baby', 'KL', 'HOUSE', 'STA001', 'available');
create or replace trigger supervisor_management
after insert or update of staffID on rental_property
for each row
declare
type array is table of rental_property.propertyNo%type index by binary_integer;
v_array array;
v_count number;
begin
for i in 1..v_array.count Loop
select count(*) into v_count from rental_property rp, staff s
where rp.staffID = v_array(i)
and s.staffID = rp.staffID
and s.position = 'supervisor';
if v_count > 3 then
DBMS_OUTPUT.ENABLE;
DBMS_OUTPUT.PUT_LINE('INCORRECT ENTRY');
ELSE
DBMS_OUTPUT.ENABLE;
DBMS_OUTPUT.PUT_LINE('CORRECT');
end if;
end loop;
end;
/
Please help me, I am a oracle beginner, thank you for your help !
Thank you,
wayne
|
|
|
|
| Re: Oracle's trigger on rental_property [message #9199 is a reply to message #9197] |
Sun, 26 October 2003 13:26  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
|
Wayne, you need to look at the example more closely. There is a package and 3 different triggers that need to be set up. You just threw everything into a single trigger. I will be glad to look at it once you have set up the framework properly.
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 15:30:42 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
