Oracle Trigger (10g)

David Paras

• August 06, 2012 16:45

Situation:

I have a database trigger event and I would like to add a condition to
trigger the the PF. Following is the trigger.

 

create or replace trigger BRC_TRIGGERORD

AFTER insert or update of MEMO_3

on BRC_ORD

for each row

when(new.MEMO_3 is not null)

begin

INSERT INTO dbeventtriggertable VALUES ('Query = where
MEMO_3='||:new.MEMO_3);

END;

 

I want to add a where clause so that it triggers the PF for insert, update
of MEMO_3 where BRC_ORD.project_num in (select project_num from PROJECT
where client_num = 'BI'). How can this be achieved?

 

0

• 

  David Paras

  • August 06, 2012 16:50

  A trigger running fine on Oracle SQL editor does not work on Adeptia “SQL Trigger” text area present at “Database Trigger Activity” creation GUI of Adeptia Suite.

  This is found to be Oracle version specific issue and happens in case of Oracle 10g and works fine on Oracle 11g. On further investigation, we now conclude that Oracle 10g fails to execute Trigger Query string that contains carriage return (\r) along with newline character (\n) which makes this trigger invalid.

  Oracle 11g works fine with Trigger Query strings containing carriage return (\r) along with newline character (\n).

  If you are using Oracle 10g or a lower version, avoid this issue by  removing the carriage return (\r) from the query string before providing it to Adeptia “SQL Trigger” text area present at “Database Trigger Activity” creation GUI of Adeptia Suite. You can do this by following below steps:-

  1. Copy and paste your trigger on a text editor that supports searching of new line characters.
  2. Search for '\n' and replace them with blank string.
  3. Copy this trigger query string from textpad editor and paste it at “SQL Trigger” text area of “Database Trigger Activity”. This will result the creation of a valid trigger in case of Oracle 10g.

  Here is the example trigger that we tested on Adeptia and can be used as reference:-

  create or replace trigger TestTrigger
  AFTER insert or update of id
  on test
  for each row
  declare
  cursor cursor_project is
  select project_num from project where client_num = 'abc';
  temp_project varchar2(30);
  begin
  open cursor_project;
  loop
  fetch cursor_project into temp_project;
  exit when cursor_project%NOTFOUND;
  if :new.id = temp_project then
  INSERT INTO dbeventtriggertable VALUES ('Query = where id='||:new.id);
  end if;
  end loop;
  END;

  For executing this trigger on Oracle 10g through Adeptia Database Trigger Activity we replaced the \n&\r charecters using a textpad editor as below:-

   

  create or replace trigger TestTrigger AFTER insert or update of id on test for each row declare cursor cursor_project is select project_num from project where client_num = 'abc'; temp_project varchar2(30); begin open cursor_project;loop fetch cursor_project into temp_project;exit when cursor_project%NOTFOUND;if :new.id = temp_project then INSERT INTO dbeventtriggertable VALUES ( '010000002015133890788500200007','Query = where id='||:new.id, sysdate );end if; end loop; END;

  0

Please sign in to leave a comment.