CODEWITHSUNDEEP
sqloracle-databaseplsqltriggers
Justiciar
Justiciar

Reputation: 376

Oracle - Trigger that inserts into one table, while preventing an insert into another

Using a trigger I would like to prevent the insertion into a certain table, while inserting a row into a different, error table. I am having a hard time doing this. I have tried to use raise_application_error but this also prevents the insert into the error table.

the tables are defined as follows--

Table to prevent insert on:

CREATE TABLE Condo_Assign (
MID INT
, RID VARCHAR2(3)
, CONSTRAINT Condo_Assign Primary Key (MID,RID)
, CONSTRAINT MID_Assign_FK Foreign Key (MID) references SkiClub (MID)
, CONSTRAINT RID_Assign_FK Foreign Key (RID) references Condo_Reservation (RID)
);

Error Table (for new insert):

CREATE TABLE ReserveError (
Err INT PRIMARY KEY
, MID INT
, RID VARCHAR2(3)
, errorDate DATE
, errorCode VARCHAR2(6)
, errorMsg VARCHAR2(60)
, CONSTRAINT Error_MID_FK FOREIGN KEY (MID) REFERENCES SkiClub
, CONSTRAINT Error_RID_FK FOREIGN KEY (RID) REFERENCES Condo_Reservation
);

Trigger: 

CREATE OR REPLACE TRIGGER Gender_Assign_Trigger
  BEFORE INSERT ON Condo_Assign
  FOR EACH ROW
DECLARE
  Room_Gender Char(1);
  Guest_Gender Char(1);
BEGIN
  SELECT Gender 
    INTO Room_Gender
    From Condo_Reservation
    WHERE RID = :new.RID;
  SELECT Gender
    INTO Guest_Gender
    FROM SkiClub
    WHERE MID = :new.MID;       
  IF Room_Gender = 'M' AND Guest_Gender = 'F' THEN
    insert into ReserveError (Err,MID,RID, ErrorDate, errorCode, errorMsg) VALUES (Error_Seq.nextVal, :new.MID, :new.RID, SYSDATE, 'g00001', 'Female guest assigned to male room');
  ELSIF Room_Gender = 'F' AND Guest_Gender = 'M' THEN
    insert into ReserveError (Err,MID,RID, ErrorDate, errorCode, errorMsg) VALUES (Error_Seq.nextVal, :new.MID, :new.RID, SYSDATE, 'g00002', 'Male guest assigned to female room');
  END IF;
  END Gender_Assign_Trigger;
/

Upvotes: 0

Views: 103

Answers (1)

access_granted
access_granted

Reputation: 1917

create or replace procedure sp_reserve_error
(
MID int, RID int, errorCode varchar2, errorMsg varchar2
)
as
pragma autonomous_transaction;
begin
  insert into ReserveError (Err,MID,RID, ErrorDate, errorCode, errorMsg) 
  VALUES (Error_Seq.nextVal, MID, RID, SYSDATE, errorCode, errorMsg);  
  commit;
end;
/


CREATE OR REPLACE TRIGGER Gender_Assign_Trigger
  BEFORE INSERT ON Condo_Assign
  FOR EACH ROW
DECLARE
  Room_Gender Char(1);
  Guest_Gender Char(1);
BEGIN
  SELECT Gender 
    INTO Room_Gender
    From Condo_Reservation
    WHERE RID = :new.RID;
  SELECT Gender
    INTO Guest_Gender
    FROM SkiClub
    WHERE MID = :new.MID;       
  IF Room_Gender = 'M' AND Guest_Gender = 'F' THEN
    sp_reserve_error(:new.MID, :new.RID, 'g00001', 'Female guest assigned to male room'); 
  ELSIF Room_Gender = 'F' AND Guest_Gender = 'M' THEN
    sp_reserve_error(:new.MID, :new.RID, 'g00002', 'Male guest assigned to female room'); 
  END IF;
  END Gender_Assign_Trigger;
/

Upvotes: 0

Related Questions