CODEWITHSUNDEEP
sqloracle-databaseplsqldatabase-trigger
Justiciar
Justiciar

Reputation: 376

Oracle - Using a trigger to delete a row

When an insert is made to Condo_assign I am using multiple triggers to add entries to a reserveError table to indicate what the error was. After they are logged in reserveError I am now attempting to use another trigger to delete the record from condo_assign that caused the problem. Basically, the erroneous insert should be logged in ReserveError and Deleted from Condo_assign. The problem is that while my delete trigger compiles and causes no problems, it doesn't appear to do anything. when I select * from condo_assign the erroneous entries are still there.

Condo_Assign table: 

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)
);

reserveError Table:

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
);

Procedure that causes trigger to fire:

CREATE OR REPLACE Procedure addCondo_Assign
(
inMID in Condo_Assign.MID%type
, inRID in Condo_Assign.RID%type
, inPaymentDate in Payment.PaymentDate%type
, inPayment in Payment.Payment%type
)
is
begin
insert into Condo_Assign (MID,RID) values (inMid,inRid);
IF inPayment >= 50 then 
    insert into Payment (MID,RID,PaymentDate,Payment) values (inMID,inRID,inPaymentDate,inPayment);
ELSE
    raise_application_error(-20088,'Deposit less than 50');
end if;

exception
    when others then
    raise_application_error(-20005,'Cannot add to entry to Condo_Assign Table.');
end addCondo_Assign;
/

Trigger that writes to ReserveError table

-- Trigger to prevent gender mismatchs in room assignment
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
    addReserveError(:new.MID,:new.RID,SYSDATE,'g00001','Female guest assigned to male room');
ELSIF Room_Gender = 'F' AND Guest_Gender = 'M' THEN
    addReserveError(:new.MID,:new.RID,SYSDATE,'g00002','Male guest assigned to female room');
END IF;
END Gender_Assign_Trigger;
/

Trigger that should delete entry from condo_assign:

CREATE OR REPLACE TRIGGER Remove_errors_trigger
after Insert on ReserveError
FOR EACH ROW
BEGIN
  DELETE FROM Condo_Assign
  WHERE MID = :new.MID and RID = :new.RID;
END remove_errors_trigger;
/

Upvotes: 0

Views: 376

Answers (1)

Rene
Rene

Reputation: 10541

The entire process is started in a before insert trigger.

The code that tries to delete the record won't delete anything because the record has not been inserted yet. There is nothing to delete at the time the delete code is being fired.

Usually checks in triggers prevent the insertion of incorrect data throwing an exception. The code inserting the data will have to handle the exception.

But you already have a procedure that handles the insert. It even performs a check on the deposit. Why not handle the gender check there?

Upvotes: 1

Related Questions