Oracle 9i trigger question

First post!

Using Oracle 9i SQL*Plus I am trying to write a trigger which will fire if more than 20 entities are "posted" into a particular table. Here is the code I have so far:

CREATE OR REPLACE TRIGGER q2
BEFORE UPDATE OR INSERT ON BED

DECLARE
num_of_beds VARCHAR2(2);

begin

SELECT COUNT(*) INTO num_of_beds FROM BED WHERE Ward_name = ????;

IF num_of_beds >= 20 THEN

DBMS_OUTPUT.PUT_LINE('Trigger fired');

END IF;

end;

Does anyone know the SQL needed to replace the question marks so that the trigger would detect which ward has been specified if a user runs an INSERT command?

Is it :new.ward_name?

Any help would be much appreciated!

Cheers :-)

The only way I know to do this is to create another table, with the same access as the table BED, that keeps track of inserts. Your trigger has to maintain the table. Or create a single CHAR field in BED that is set to 'X' when the trigger fires on a given record, and left NULL until the trigger executes.

Trigger variables are not static - they do not persist after the trigger executes. Plus, even if they were they still would not work because triggers execute in the current user's session.
If you had five users hitting BED, the trigger would not fire until one of the five users inserted 20 rows.

Real answer:
What are your trying to do?

Yeah sorry, I didn't really explian myself very well in the first message. I am doing a university assignment, so DB interaction will be slight (no more than one user at a time!). The tables I am using are as follows:

DROP TABLE STAFF;
DROP TABLE BED;
DROP TABLE WARD;

CREATE TABLE STAFF
(
Staff_id varchar2(10) NOT NULL,
Name varchar2(35) NOT NULL,
address VARCHAR2(50) NOT NULL,
teleno varchar2(10) NOT NULL,
NIno varchar2(10) NOT NULL,
Notes VARCHAR2(500) NOT NULL,
Department Varchar2(20),
Staff_responsibility Varchar2(20),
Date_started Date,
Date_left Date,
CONSTRAINT STAFF_PK
PRIMARY KEY (Staff_id)
);

CREATE TABLE BED
(
Bed_no INTEGER NOT NULL,
Ward_name varchar2(10) NOT NULL,
Centre_name varchar2(10) NOT NULL,
CONSTRAINT BED_PK
PRIMARY KEY (Bed_no,Ward_name,Centre_name)
);

CREATE TABLE WARD
(
Ward_name varchar2(10) NOT NULL,
Centre_name varchar2(10) NOT NULL,
CONSTRAINT WARD_PK
PRIMARY KEY (Ward_name,Centre_name)
);

And I have to write a trigger to enforce the following constraint:

There must be no more than 20 beds to a ward.

I am planning to implement 3 wards for test purposes.

Any suggestions?

As i understand, you need to following modification in your trigger:

CREATE OR REPLACE TRIGGER q2
BEFORE UPDATE OR INSERT ON BED
FOR EACH ROW
DECLARE
num_of_beds VARCHAR2(2);

begin

SELECT COUNT(*) INTO num_of_beds FROM BED WHERE Ward_name = :new.ward_name;

IF num_of_beds >= 20 THEN

DBMS_OUTPUT.PUT_LINE('Trigger fired');

END IF;

end;

greetings..

i have the following code:



but even the prompt has got a discount, but why is that the record is not updated?

i'm trying to check the customer status. if it's good, then discount is given.
please advice.

thanks.

You have some problem in your trigger (marked with red color), look at your code and my comments

create or replace trigger eci_discount
before insert -- trigger will fire before inserting any record into table
on eci_purchase
for each row

declare
v_cus_type eci_customer.cus_type%type;

begin
select cus_type into v_cus_type from eci_customer where cus_id = :new.cus_id;
if (v_cus_type = 'GOOD') then
update eci_purchase set
amount_discount = :new.sub_total * 0.1,
total_price = :new.sub_total * 0.9
where pur_id = pur_id; -- how come you update such a record that have not yet been inserted
dbms_output.put_line('Customer ' || :new.cus_id || ' has got a discount');
else
dbms_output.put_line('Customer ' || :new.cus_id || ' has got no discount');
end if;
end;

so you need to read the following code and make certain changes as required.

create or replace trigger eci_discount
before insert
on eci_purchase
for each row
declare
v_cus_type eci_customer.cus_type%type;
begin
select cus_type into v_cus_type from eci_customer where cus_id = :new.cus_id;
if (v_cus_type = 'GOOD') then
:new.amount_discount := :new.sub_total * 0.1;
:new.total_price := :new.sub_total * 0.9;
dbms_output.put_line('Customer ' || :new.cus_id || ' has got a discount');
else
dbms_output.put_line('Customer ' || :new.cus_id || ' has got no discount');
end if;
end;

i found out about that later. and changed it into after insert.
but some problem occured saying that the table was mutating and the trigger might not see it.

how can i solve this problem?
please advice.
thanks.

You cannot use the after insert trigger because you are inserting and updating the same table that cause to generate the mutating error. You did not try my solution, what about that?

let's take a look again. modification marked by red color.

create or replace trigger eci_discount
before insert
on eci_purchase
for each row
declare
v_cus_type eci_customer.cus_type%type;
begin
select cus_type into v_cus_type from eci_customer where cus_id = :new.cus_id;

-- In IF condition i did not use UPDATE clause

if (v_cus_type = 'GOOD') then
:new.amount_discount := :new.sub_total * 0.1;
:new.total_price := :new.sub_total * 0.9;
dbms_output.put_line('Customer ' || :new.cus_id || ' has got a discount');
else
dbms_output.put_line('Customer ' || :new.cus_id || ' has got no discount');
end if;
end;