Friday, 25 September 2009

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;

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


-- 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.
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.
for j in (select text
from user_source
where type = 'TRIGGER'
and 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);');
vText := j.text;
end if;

vSql := vSql||vText;
end loop;

-- Now that we've built the script for this trigger, run it.
when others then
dbms_output.put_line(||' '||sqlerrm);
end loop;