6

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
Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
Azmina Hashim
  • 63
  • 1
  • 1
  • 3

3 Answers3

12
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;
/
Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
6

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

You can make use of commit after your delete statement and rollback after that to reach to the previous stage using Pragma.

     CREATE OR REPLACE  TRIGGER 
     RENTALHIS_DEL
    AFTER DELETE ON RENTALHISTORY
       DECLARE
       PRAGMA 
          AUTONOMOUS_TRANSACTION;
      BEGIN
      RAISE_APPLICATION_ERROR 
     (-20000, 'Deletion getting rolled 
     back');
           ROLLBACK;
             END;
Himanshu
  • 3,830
  • 2
  • 10
  • 29