title: How to create Oracle trigger that deletes/updates only some rows from the original query?
body: <p>I have a table:</p>
  
  <pre><code>create table projects(pid int,name char(10),active int);
  insert into projects values(1,'aa',1);
  insert into projects values(2,'bb',0);
  </code></pre>
  
  <p>and I want to create a trigger that doesn't allow to delete a project that is active (active=1).
  So, for the command:
  <code>delete from projects where pid in (1,2)</code>
  only row with pid=2 will be deleted and the row with pid=1 <strong>will not be deleted</strong>.
  And no error will be reported (only a warning maybe?).</p>
  
  <p>In SQL Server I am doing it like that:</p>
  
  <pre><code>create trigger nondel on projects instead of DELETE
  as
  delete from projects where pid in (select pid from deleted where active=0)
  end
  </code></pre>
  
  <p>So "instead of" trigger and only rows that fulfill both delete condition and active=0 condition are deleted.</p>
  
  <p>In Oracle I tried a "for each" trigger:</p>
  
  <pre><code>create trigger nondel before delete on projects
  for each row
  when :old.active=1
  begin
  RAISE_APPLICATION_ERROR (-20502, 'Active rows cannot be deleted!');
  end
  </code></pre>
  
  <p>but RAISE_APPLICATION_ERROR aborts the whole transaction and nothing is deleted.
  My question is: Is it possible to declare a 'for each row' trigger with a code that when a condition is fulfilled does not not delete the row but doesn't raise any error either, just continues with subsequent rows? Or maybe a statement trigger would be better?</p>