How to Create/Use a Trigger

[mrasad]

Hi Guys,

I need some help with creating a simple trigger. I will try to explain the problem i'm having.

I created a table called MEMBER;

SQL> CREATE TABLE MEMBER
2 (MemberID number(7) not null primary key,
3 Surname varchar2(15) not null,
4 Forename varchar2(15) not null,
5 DOB date not null,
6 Street_Name varchar2(20),
7 Town varchar2(20),
8 City varchar2(20),
9 Postcode varchar2(8) not null,
10 Tel_No varchar2(14),
11 Email varchar2(30),
12 Date_Joined date not null,
13 Proof_Of_Residency varchar2(30) not null);

Table Created

--------------------------------------------------------
I then created a test trigger;
TRIGGER

SQL> CREATE OR REPLACE TRIGGER NoSmiths
2 BEFORE INSERT OR UPDATE OF ForeName
3 ON Member
4 FOR EACH ROW
5 BEGIN
6 IF :new.Forename not like '%SMITH%' THEN
7 RAISE_APPLICATION_ERROR(-20000, 'No People Named Smith Allowed!');
8 END IF;
9 END NoSmiths;
10
11 /

------------------------------------------------------------
I then tried to enter some values into the table.

insert into MEMBER
values(0001234, 'Boyd', 'Brandon', '06-AUG-77', '554 Thistlewood Lane', 'Middletown',
'Southampton', 'SO67 6FR', '07986443112', null, '05-DEC-02', 'Received - Gas Bill');
----------------------------------------------------------

But with any values I insert, regardless of the values i get the following error message;

Enter Values
ERROR at line 1:
ORA-20000: No People Named Smith Allowed!
ORA-06512: at "P032452975.NOSMITHS", line 3
ORA-04088: error during execution of trigger 'P032452975.NOSMITHS'

---------------------------------------------------------
What am i doing wrong?

I thought the point of triggers was that if Smith was entered in the forename field then an error message would appear? Otherwise the datbase would accept the values.

If you can help the great, if not, then don't worry.

 

[SantaMufasa]

MrAsad,

According to your code, you get the error when ":new.Forename not like '%SMITH%'", meaning, "If the new forename does NOT contain "SMITH", then give me an error message." I believe that is the OPPOSITE of what you want, right? Solution: change "not like" to "like".

Let us know if this solves your problem,

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:52 (16Dec03) GMT, 11:52 (16Dec03) Mountain Time)

 

[avjoshi]

Change,
"IF :new.Forename not like '%SMITH%' THEN"

to
"IF uppernew.Forename) like '%SMITH%' THEN"

in your trigger code.

Anand.

 

[mrasad]

Thanks Mufasa that worked. I will need to try something more difficult now

 

[mrasad]

If i wanted book a room, (date and time) the DB should not allow me to book a room if it has the same start time and same date. I've tried the following, and it does not work;

CREATE OR REPLACE TRIGGER DoubleBookRoom
AFTER INSERT OR UPDATE OF ReservedFor,Start_Time
ON
roomReservation

DECLARE
v_count NUMBER;
ROOM_BOOKED EXCEPTION;
BEGIN
SELECT max(Count(*))
INTO v_count
FROM roomReservation
GROUP BY ReservedFor, Start_Time

HAVING Count(*)>1;
IF v_count IS NOT NULL THEN
RAISE ROOM_BOOKED;
END IF;

EXCEPTION

WHEN ROOM_BOOKED THEN
raise_application_error
(-20000,'Sorry – Room is Reserved is already booked - Try again');

END;
/
----------------------------------------------------------
An error comes up on all data in put in.
----------------------------------------------------------

any advice?

 

[SantaMufasa]

MrAsad,

I have some observations/questions regarding your code:

1) "START_TIME": is it an Oracle DATE column? If so, do you TRUNC(START_TIME)? If you do not, then Oracle stores the Date/Time with granularity to the second, which makes it rather difficult to have matching START_TIMEs.

2) Your SELECT statement reads:
"SELECT max(Count(*))INTO v_count FROM roomReservation
GROUP BY ReservedFor, Start_Time HAVING Count(*)>1;"

Without a WHERE statement, the SELECT considers ALL ReservedFor names for ALL room reservations, not just the current ReservedFor name for the current room. I cannot tell if you are looking for concurrent reservations for the same person or for the same room, but your code checks for neither as far as I can tell. Your code is grouping by ReservedFor (name, I presume) and START_TIME. This means that your code considers only those rows where there are multiple reservations for the the same person at the same reservation time. Frankly, that should be legal: If I make a reservation for multiple rooms under my name (since my family and friends are travelling with me and I'm paying for their rooms).

So, if that situation exists at all in your table, then EVERY Insert and/or Update on RoomReservation will yield your error message, 'Sorry – Room is Reserved is already booked - Try again'.

Is this all true?

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:26 (17Dec03) GMT, 10:26 (17Dec03) Mountain Time)

 

[mrasad]

1) "START_TIME": is it an Oracle DATE column? If so, do you TRUNC(START_TIME)? If you do not, then Oracle stores the Date/Time with granularity to the second, which makes it rather difficult to have matching START_TIMEs.

I'm not sure what TRUNC is, and i was told that a time function with :SS may cause problems. I could not get a time function to work so I used a number field, and then enter data like 9,10,11,12,13,14.

Is this all true?

Yes.

This is my table below


create table RoomReservation
(RoomReservationID varchar2(6) not null primary key,
purpose varchar2(20) not null,
reservedOn date not null, << (current date)
reservedFor date not null,
Start_Time number(2) not null,
End_Time varchar2(8) not null,
Booking_Fee number(5,2) not null,
RoomNO number(4) not null, <<< A primary key in room table
MemberID varchar2(7) not null); (<<A primary key in member table)

A member is allowed to reserve a room. If however the RESERVEDFOR(date the room is booked for) and START_TIME (time room is booked for) has already been booked an error message should pop up. No one room can be booked at the same time on the same time.

Is that possible to do?