FOR EACH ROW Missing

[carenswijaya01]

Hi Darold, i have a bug from trigger convert (idk if it will affect others)

Here's my Oracle Trigger - 1

create or replace trigger "TDB_IBON_DTL" 
  before delete on tbl_ibon_dtl
  for each row
declare
  -- local variables here

begin
...
--  Errors handling
exception
    when integrity_error then
       raise_application_error(errno, errmsg);

end TDB_IBON_DTL;

and the result is

DROP TRIGGER IF EXISTS tdb_ibon_dtl ON tbl_ibon_dtl CASCADE;
CREATE OR REPLACE FUNCTION trigger_fct_tdb_ibon_dtl() RETURNS trigger AS $BODY$
declare
  -- local variables here

BEGIN
...
end
$BODY$
 LANGUAGE 'plpgsql';

CREATE TRIGGER tdb_ibon_dtl
    BEFORE DELETE ON tbl_ibon_dtl FOR EACH ROW
    EXECUTE PROCEDURE trigger_fct_tdb_ibon_dtl();

Which seems perfect for me

But if there any other trigger below it (i mean i have 2 or more triggers), it doesnt include 'FOR EACH ROW' which i use for almost every trigger that i made.

Here's my Oracle Trigger - 2:

create or replace trigger "TDB_IPB_BJ_DETAIL" 
before insert or delete on tbl_ipb_bj_detail 
for each row
declare
  -- local variables here
dummy integer(6);

begin
 ...

end tdb_ipb_bj_detail;

And the result:

DROP TRIGGER IF EXISTS tdb_ipb_bj_detail ON tbl_ipb_bj_detail CASCADE;
CREATE OR REPLACE FUNCTION trigger_fct_tdb_ipb_bj_detail() RETURNS trigger AS $BODY$
declare
  -- local variables here
dummy integer(6);

BEGIN
...

end
$BODY$
 LANGUAGE 'plpgsql';

CREATE TRIGGER tdb_ipb_bj_detail
    BEFORE INSERT OR DELETE ON tbl_ipb_bj_detail    EXECUTE PROCEDURE trigger_fct_tdb_ipb_bj_detail();

As you can see, there is no 'FOR EACH ROW' clause in the last result, but there is some whitespace in it (i think it was replaced with whitespace (?))

But as far as i know, 'FOR EACH ROW' clause only appear in 1st trigger exported

So when i export "TDB_IPB_BJ_DETAIL" only, without "TDB_IBON_DTL" before it (or exclude it), the "FOR EACH ROW" clause appear

DROP TRIGGER IF EXISTS tdb_ipb_bj_detail ON tbl_ipb_bj_detail CASCADE;
CREATE OR REPLACE FUNCTION trigger_fct_tdb_ipb_bj_detail() RETURNS trigger AS $BODY$
declare
  -- local variables here
dummy integer(6);

BEGIN
...

end
$BODY$
 LANGUAGE 'plpgsql';

CREATE TRIGGER tdb_ipb_bj_detail
    BEFORE INSERT OR DELETE ON tbl_ipb_bj_detail FOR EACH ROW
    EXECUTE PROCEDURE trigger_fct_tdb_ipb_bj_detail();

The hint that i know is just like i tell above, it only happen when it export 2 or more triggers, and only affect start from 2nd trigger.

[carenswijaya01]

Update:

I tried to change

# $sql_output .= "FOR EACH ROW\n" if ($sql_output !~ /FOR EACH ROW/);
$sql_output .= "FOR EACH ROW\n";

and it work, and just in case i try to change FOR EACH STATEMENT too, maybe you can try it and test

For now, i think i'll comment it first to continue my job

[darold]

Commit 887969f fixes this issue, thanks for the report.

[carenswijaya01]

Thanks darold :+1: