CODEWITHSUNDEEP
oracle-databasetriggersdelete-row
MohammedS
MohammedS

Reputation: 221

SQL error Oracle Trigger on Delete

I am a first timer in Oracle and using Netbean IDE 8 and I am trying to prevent deletion of a Hotel from Hotel Table if Room Table has Room details for the Hotel. These 2 tables are on 2 different sites so have to use trigger. I tried the following code but it throws error like following with sql error on line 6, 10, 13, 14,

[Exception, Error code 6,550, SQLState 65000] ORA-06550: line 4, column 19: PLS-00049: bad bind variable '' ORA-06550: line 4, column 30: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

the table structure is 

CREATE TABLE Hotel
(
  HotelID number not null,
  HotelName varchar2(100) not null,
  HotelType varchar2(10) not null,
  ConstrYear varchar2(10) null,
  Country varchar2 (100) not null,
  City varchar2 (50) not null,
  Address varchar2 (100) not null, 
  ContactNo varchar2(50) not null,
  Email varchar2(100) null,
  CONSTRAINT Hotel_pk PRIMARY KEY (HotelID)
);

and for Room 

CREATE TABLE Room 
(
 RoomID number not null, 
 HotelID raw(16) not null, 
 RoomNo number not null,
 RoomType varchar2(20) not null, 
 Price numeric(10,2) not null, 
 RoomDesc varchar2(255) not null, 
 CONSTRAINT Room_pk PRIMARY KEY (RoomID),
);

What am I doing wrong? Please help.

CREATE OR REPLACE TRIGGER CHECK_Room
BEFORE DELETE on Hotel
FOR each ROW
declare
  rowcount number;
begin
  SELECT COUNT(HotelID) INTO rowcount 
  from ROOM@site1
  where HotelID = :OLD.HotelID;
  if rowcount>0 
  THEN
     Raise_Application_Error (-20100, 'This Hotel has room details in Room table.');
  end if;
end;

Upvotes: 0

Views: 1064

Answers (1)

alexgibbs
alexgibbs

Reputation: 2480

The below should work for you, provided your database link is in good shape.
I'd recommend staying away from using a reserved word as a variable name. One common convention is to prefix variable names with "v" as in this example.
I'd also recommend qualifying schema names over the database link. THE_USER is a placeholder here as is THE_OTHER_DATABASE. Please replace with site1, etc. as needed. First create the tables, in your case in two databases: 

--This database
CREATE TABLE HOTEL(HOTELID NUMBER);
--(On the other database)
CREATE TABLE ROOM(HOTELID NUMBER);
-- ... Set up database link

CREATE OR REPLACE TRIGGER CHECK_ROOM
BEFORE DELETE ON HOTEL
FOR EACH ROW
  DECLARE
    V_ROWCOUNT NUMBER;
  BEGIN
    SELECT COUNT(HOTELID)
    INTO V_ROWCOUNT
    FROM THE_USER.ROOM@THE_OTHER_DATABASE
    WHERE ROOM.HOTELID = :OLD.HOTELID;
    IF V_ROWCOUNT > 0
    THEN
      Raise_Application_Error(-20100, 'This Hotel has room details in Room table.');
    END IF;
  END;
  /

Then test it:

--Here
INSERT INTO HOTEL VALUES(19);
COMMIT;
--There
INSERT INTO ROOM VALUES(19);
COMMIT;

Then:

DELETE FROM HOTEL;
DELETE FROM HOTEL
            *
ERROR at line 1:
ORA-20100: This Hotel has room details in Room table.

Upvotes: 1

Related Questions