Preventing certain rows from being deleted in Oracle

Question

I want to prevent any row with VERSIONID=1 from being deleted in a certain table. I also want to log this in an audit table so we can see when this happens for logging purposes. I'm 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 TPM_AUDIT is empty. Am I doing something wrong?

Answer 1

If your trigger raises an error, the DELETE statement fails and the transaction is rolled back to the implicit savepoint that is created before the statement is run. That means that any changes made by the trigger are rolled back as well.

You can work around this by using autonomous transactions. Something like

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;

This will put the INSERT into TPM_AUDIT into a separate transaction that can be committed outside the context of the DELETE statement. Be very careful about using autonomous transactions, however

1. If you ever find yourself using autonomous transactions for anything other than writing to a log table, you're almost certainly doing something wrong.
2. Code in a PL/SQL block declared using autonomous transactions is truly autonomous so it cannot see uncommitted changes made by the current session.
3. Because of write consistency, it is entirely possible that Oracle will partially execute a DELETE statement, firing the row-level trigger a number of times, roll back that work, and then re-execute the DELETE. That silent rollback, however, will not roll back the changes made by the autonomous transaction. So it is entirely possible that a single DELETE of a single row would actually cause the trigger to be fired more than once and, therefore, create multiple rows in TPM_AUDIT.

Answer 2

If you can create a UNIQUE constraint on the TPM_PROJECTVERSION pk columns + the version column, then you can create a second table that would reference those rows.

Trying to delete a row in TPM_PROJECTVERSION would then fail because child rows are present. This would at least throw an error in your application and prevent the deletion.

The other table could be automatically populated through an insert trigger on TPM_PROJECTVERSION.

If you revoke the DELETE privilege on that helper table, it would never be possible to remove those rows.