SQL error Oracle Trigger on Delete

Question

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;

One error - you are declaring rowcount variable, but using if rowcnt>0. Also use set define offcommand to avoid "bad bind variable" error. — krokodilko
– krokodilko, Commented Mar 24, 2017 at 21:48
The PLS-00049 error suggests your hotel table doesn't have a column called HotelId. Can you add the structures of both tables to your question? The PLS-00103 error may just be because you don't have a trailling /, but the line number reference is a bit odd. Which client and you running this in, and how? Wondering if it is just not handling PL/SQL code properly, and is confused by the semicolons. — Alex Poole
– Alex Poole, Commented Mar 24, 2017 at 23:23
I am using NetBean IDE 8. rowcont >0 was a typo. I am using rowcount > 0. — MohammedS
– MohammedS, Commented Mar 25, 2017 at 2:00
None of the examples in the NetBeans docs show PL/SQL. I still suspect the semicolons are tripping it up then. Are you using the 'execute command' method? I might be worth trying the 'run file' method from a script file, but I doubt it will help. I'd suggest you use a different IDE for this stuff, like the free SQL Developer. Or just SQL*Plus. Or, if you really can't, write your own trivial Java program to run that over JDBC *8-) — Alex Poole
– Alex Poole, Commented Mar 25, 2017 at 8:23
Thanks @AlexPoole. Yes it seems to be the NetBeans problem. I used SQL Developer and worked. It throws error, but at least it compiled and worked. — MohammedS
– MohammedS, Commented Mar 26, 2017 at 3:17

alexgibbs · Accepted Answer · 2017-03-24 22:58:57Z

1

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.

answered Mar 24, 2017 at 22:58

alexgibbs

2,4802 gold badges18 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

MohammedS Over a year ago

I tried exactly what you showed here and tried on same connection, but still having error as follows, [Exception, Error code 6,550, SQLState 65000] ORA-06550: line 5, column 26: PLS-00049: bad bind variable '' ORA-06550: line 5, column 37: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

alexgibbs Over a year ago

Thanks @MohammedS Do you have access to clients like SQLPlus, SQLcl or SQLDeveloper to compare the behavior in another tool against netbeans? Alex Poole has a good question above to consider the editor in addition to the plsql text. If you have access to another client, it would be good to see if the behavior repeats similarly. Also can you confirm for me, when you tried this, did you run the whole example above including the CREATE TABLEs, only replacing the schema and db link? Thanks

MohammedS Over a year ago

Thanks @alexgibbs. yep ran the whole script including create table at once except delete function. Replaced schema and db link. I do not have any other IDE, but I will try to get one and let you know the behaviour.

alexgibbs Over a year ago

Thanks @MohammedS sounds good, I'll wait to hear how this behaves in another editor. I believe Alex Poole had some suggestions, but I'd add SQLcl as well. It is free, supported by oracle and is lightweight. It is a java command-line app. You can try it here: sqlcl

MohammedS Over a year ago

Thanks @alexgibbs. I tried it on SQL Developer and it did work, but it compiled with error. The trigger prevents from deleting, but throwing error as well. The output as as follows. I am trying on SQLcl now. Error starting at line : 1 in command - DELETE FROM HOTEL Error report - SQL Error: ORA-20100: This Hotel has room details in Room table. ORA-06512: at "USER.CHECK_ROOM", line 10 ORA-04088: error during execution of trigger 'USER.CHECK_ROOM'

|

Collectives™ on Stack Overflow

SQL error Oracle Trigger on Delete

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related