Home » SQL & PL/SQL » SQL & PL/SQL » oracle's trigger
| oracle's trigger [message #9176] |
Fri, 24 October 2003 09:58  |
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
);
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', 'STA006', 'available');
insert into rental_property values ('PPT004', 'Jln Baby', 'KL', 'HOUSE', 'STA006', 'available');
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)
);
create or replace trigger supervisor_management
after insert or update of staffID on rental_property
declare
cursor property_cur is
select propertyNo, staffID from rental_property;
pno rental_property.propertyNo%type;
sid rental_property.staffID%type;
property_no number;
begin
open property_cur;
loop
fetch property_cur into pno, sid;
exit when property_cur%notfound;
select count(r.propertyNo) into property_no from rental_property R, staff S
where r.staffID = sid and s.position = 'supervisor';
if property_no > 3 then
raise_application_error(-20000, 'A supervisor supervises more than three rental properties!');
end if;
end loop;
close property_cur;
end;
/
Please help me, I am a oracle beginner, thank you for your help !
Thank you,
wayne
|
|
|
|
| Re: oracle's trigger [message #9184 is a reply to message #9176] |
Fri, 24 October 2003 12:56  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Wayne, please see the below URL for an example of how to defer this type of check to an after-statement trigger using a package to hold the affected values.
In the triggers on rental_property, you would want to add the staff_id value to the array. You could check in the after-row trigger to see if the person is a supervisor, or just do that filter in the SQL in the procedure. Something like:
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 r.staffid = v_array(i)
and s.staffid = r.staffid
and s.position = 'Supervisor';
if v_count > 3 then
raise_application_error(...);
end if;
end loop;
end;
Keep in mind though that a validation like this, handled through triggers, is not multi-user "safe". If a supervisor already had two rental properties, and two sessions each added another property to that supervisor simultaneously, the validation will pass for each and once they commit, that supervisor would end up with four properties.
This may not be a realistic scenario for you given how the data is entered, but is something to keep in mind with this approach.
|
|
|
|
| thank you [message #9198 is a reply to message #9184] |
Sat, 25 October 2003 22:59 |
wayne
Messages: 27
Registered: March 2001
|
Junior Member |
|
|
Dear,
thank you for your help !
Base on your suggestion, I had done for the amendment.
But, it still cannot work.
Please try to have a look..Thank you very much for your help.
thank you,
wayne
|
|
|
|
Goto Forum:
Current Time: Tue Apr 16 18:38:42 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
