Readying database triggers for Apex (or Will Smith's stamp collection)

If you've written database triggers that record the username of the person who's updated a record [:new.modified_user := USER;] you probably discovered soon after you switched to Apex that the column was full of APEX_PUBLIC_USERs, the middleman through whom all Apex transactions with the database must pass. The solution, you probably realised, was easy: to get the name of the user of your apex application you need to make a call to APEX_APPLICATION [:new.modified_user := nvl(apex_application.g_user,USER);].

But it's not always that simple and uncomplicated; if it was I could end this blog entry right here and get back to watching my latest shameful TV addiction, Dating in the Dark.

Here's how Dating in the Dark works: 3 guys and 3 girls live in separate sections of a house and only get to meet in complete darkness so that the impressions they develop of each other are based totally on personality and not looks. Soon they pair off and have a number of dates (still in absolute darkness). Then, once they're sure they're totally in love, the lights are switched on - and they get their first looks at each other. And they're asked if they wish to continue the relationship.

Of course, because this is reality TV we're served up the weirdest combinations. And so the nerd with a mole the size of Switzerland and the pink Homer Simpson-print trousers is paired with the blond bimbo with shop-bought boobs so new they've still got the pricetag on.

But darkness is a great equaliser, and when those lights go off boring old John Smith the librarian with the stamp collection and poster of Marie Curie on his bedroom wall can become cool, can transform into Will Smith.

Which (coming back to my complication) is kinda what I wanted my database to do too. Because my database pre-dated my Apex application it already contained hundreds of triggers that would need editing. But with gold like Dating in the Dark on the telly, who has the time? What I needed was a script that would metaphorically turn the lights down on my boring database and allow it transform into a superstar, a script that'll run through my database, find my triggers, and 'apexify' them.

Well, here's that script:

/*
** This script will 'Apexify' mod triggers if Apex is installed.
** Most tables in the database have a column called MODIFIED_USER
** and accompanying triggers that set it to the current user
** after a row has been updated or insterted.
*/
set serveroutput on;

DECLARE
 vSql   VARCHAR2(32767);
 vInstalled BOOLEAN := FALSE;
 vText   VARCHAR2(4000);

BEGIN

 -- First thing we've got to do is check if Apex is installed.
 for i in (select 1
  from all_users
  where username like 'FLOWS%') loop

  -- If we get here it means it's installed.
  vInstalled := TRUE;
 end loop;

 if not vInstalled then -- No need to continue.
  RETURN;
 end if;

 -- Find all affected triggers.
 for i in (select name
  from user_source
  where upper(text) like '%:NEW.MODIFIED_USER%:=% USER;%'
  and type = 'TRIGGER') loop
  
  -- Now get the code for the trigger.
  vSql := 'CREATE OR REPLACE ';
  for j in (select text
   from user_source
   where type = 'TRIGGER'
   and name = i.name
   order by line) loop

   if upper(j.text) like '%:NEW.MODIFIED_USER%:=% USER;%' then
    vText := replace(upper(j.text),' USER;',' NVL(APEX_APPLICATION.G_USER,USER);');
   else
    vText := j.text;
   end if;

   vSql := vSql||vText;
  end loop;

  -- Now that we've built the script for this trigger, run it.
  begin
   EXECUTE IMMEDIATE vSql;
  exception
  when others then
   dbms_output.put_line(i.name||' '||sqlerrm);
  end;
 end loop;
END;
/