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;
vInstalled BOOLEAN := FALSE;
-- First thing we've got to do is check if Apex is installed.
for i in (select 1
where username like 'FLOWS%') loop
-- If we get here it means it's installed.
vInstalled := TRUE;
if not vInstalled then -- No need to continue.
-- Find all affected triggers.
for i in (select name
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
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);');
vText := j.text;
vSql := vSql||vText;
-- Now that we've built the script for this trigger, run it.
EXECUTE IMMEDIATE vSql;
when others then