Trigger to prevent Any Deleting from Table

Question

a trigger for this new rental history table that prevents deletions from the table.

CREATE OR REPLACE  TRIGGER RENTALHIS_DEL
BEFORE DELETE ON RENTALHISTORY
BEGIN
dbms_output.put_line( 'Records can not be deleted');
END;

DELETE FROM RENTALHISTORY WHERE RENTALID = 1;



-- It is deleting before it says it can not delete

1 rows deleted.

Records can not be deleted

Answer 1

dbms_output.put_line( 'Records can not be deleted');

The above just prints the text and trigger completes successfully and then delete happens anyway. What you wanna do instead is to raise an error to prevent the program from completing.

Use standard procedure raise_application_error to stop the program and raise error:

CREATE OR REPLACE  TRIGGER RENTALHIS_DEL
BEFORE DELETE ON RENTALHISTORY
BEGIN
    raise_application_error(-20001,'Records can not be deleted');
END;
/

Answer 2

You want to raise an error, not print a message. The delete happens anyway:

CREATE OR REPLACE  TRIGGER RENTALHIS_DEL
BEFORE DELETE ON RENTALHISTORY
BEGIN
  RAISE_APPLICATION_ERROR (-20000, 'Deletion not supported on this table');
END;

Alternatively, you could use an instead of trigger to prevent the delete from taking place:

CREATE OR REPLACE TRIGGER RENTALHIS_DEL
INSTEAD OF DELETE ON RENTALHISTORY
BEGIN
   dbms_output.put_line( 'Records cannot be deleted');
END;