1

I have a compound trigger using a when clause. My when clause is working as expected for the AFTER EACH ROW statement but not for the following AFTER STATEMENT. If the WHEN clause is not met the AFTER EACH ROW is not running but the AFTER Statement still is. Is there a way to ensure the WHEN clause works for the whole trigger?

I tried a work around by just adding the WHEN clause as an IF statement in the AFTER STATEMENT but have had issues with the Bind variables and an ORA 03113 error. Sample Code is below:

  FOR UPDATE OF COL_X ON MY_TABLE
WHEN (nvl(new.var,'*') != '*')

COMPOUND TRIGGER

type t_table is table of TABLE.VAR%TYPE;
l_table t_table := t_table();
AFTER EACH ROW IS
BEGIN       
      l_table.extend();
      l_table(l_table.last) := :new.var;
END AFTER EACH ROW;
--The following OUTPUT is displayed even if WHEN is not me--
after statement is
begin
dbms_output.put_line('OUTPUT'); 
for i in l_table.first .. l_table.last
loop
    dbms_output.put_line('Run if Data in l_table');
end loop;
l_table.delete
end after statement
end;
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
cdbean
  • 21
  • 3

1 Answers1

0

No way around it; before and after statements triggers will always fire. The WHEN condition refers to columns (and then only on update). Specific columns are not available at the statement level.

Belayer
  • 13,578
  • 2
  • 11
  • 22