Preventing Deletion of Specific Rows in Oracle

Question

I want any row with VERSIONID=1not to be deleted in a specific table. I also want to write this to the audit table so that we can see when this happens for logging. I am trying to do this with a trigger:

CREATE TRIGGER TPMDBO.PreventVersionDelete
  BEFORE DELETE ON TPM_PROJECTVERSION
  FOR EACH ROW
DECLARE
BEGIN
  IF( :old.VERSIONID = 1 )
  THEN
    INSERT INTO TPM_AUDIT VALUES ('Query has attempted to delete root project version!', sysdate);
    RAISE_APPLICATION_ERROR( -20001, 'Query has attempted to delete root project version!' );
  END IF;
END;

I get the following results:

SQL> delete from TPM_PROJECTVERSION where PROJECTID=70 and VERSIONID=1;
delete from TPM_PROJECTVERSION where PROJECTID=70 and VERSIONID=1
            *
ERROR at line 1:
ORA-20001: Query has attempted to delete root project version!
ORA-06512: at "TPMDBO.PREVENTVERSIONDELETE", line 6
ORA-04088: error during execution of trigger 'TPMDBO.PREVENTVERSIONDELETE'

However, the table is TPM_AUDITempty. Am I doing something wrong?

Answer 1

If your trigger causes an error, the statement DELETEfails, and the transaction rolls back to the implicit savepoint created before the statement was run. This means that any changes made by the trigger are also rolled back.

, . -

CREATE PROCEDURE write_audit
AS
  PRAGMA AUTOMOMOUS_TRANSACTION;
BEGIN
  INSERT INTO tpm_audit
    VALUES( 'Query has attempted to delete root project version!',
            sysdate );
  commit;
END;

CREATE TRIGGER TPMDBO.PreventVersionDelete
  BEFORE DELETE ON TPM_PROJECTVERSION
  FOR EACH ROW
DECLARE
BEGIN
  IF( :old.VERSIONID = 1 )
  THEN
    write_audit;
    RAISE_APPLICATION_ERROR( -20001, 'Query has attempted to delete root project version!' );
  END IF;
END;

INSERT TPM_AUDIT , DELETE. ,

• - -, , - .
• PL/SQL, , , , .
• - , Oracle DELETE, , DELETE. , . , , DELETE , , TPM_AUDIT.

Answer 2

UNIQUE pk TPM_PROJECTVERSION + , , .

TPM_PROJECTVERSION , . , , .

TPM_PROJECTVERSION.

DELETE , .

Answer 3

I believe you need to REVERSE the INSERT operation before calling the RAISE_APPLICATION_ERROR command, which rolls back the transaction.