OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » trigger

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

trigger [message #9297]

Sat, 01 November 2003 23:01

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 package rental
is
type array is table of rental_property.staffID%type index by binary_integer;
v_array array;

procedure process;

end;
/

create or replace package body rental
is
procedure process
is
v_count pls_integer;
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
raise_application_error(-20000, 'A supervisor supervises more than three rental properties!');
end if;
end loop;
end;
end;
/

create or replace trigger supervisor_management
after insert or update of staffID on rental_property
for each row
declare

begin
rental.process;
end;
/

There is no effect after I insert data into the table called rental_property !
Please help me, I am a oracle beginner, thank you for your help !

Thank you,
wayne

Report message to a moderator

Previous Topic:	how can i write a table script having a column containing time
Next Topic:	trgger for date

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 02:52:12 CDT 2024