CODEWITHSUNDEEP
sqloracle-databasetriggers
Azmina Hashim
Azmina Hashim

Reputation: 63

Trigger to prevent Any Deleting from Table

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

Upvotes: 6

Views: 30401

Answers (3)

Himanshu
Himanshu

Reputation: 3970

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;

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39537

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

Upvotes: 13

Gordon Linoff
Gordon Linoff

Reputation: 1271231

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;

Upvotes: 6

Related Questions