Dynamic SQL generation using a table.


  19:
  20:/*
  21: * This script demonstrates a technique to alter all tables in a single hit.
  22: * The particular example adds simple auditing to every table.
  23: */
  24:
  25:-- Make sure we are not still trying to write to a file.
  26:output;
  27:
  28:-- Make sure old stuff isn't lying around
  29:-- ISQL has a habit of appending to files, not overwriting them.
  30:shell del autogen_alter_all.ddl;
  31:shell del alter_all.txt;
  32:
  33:-- This is one of the clever bits
  34:-- it transforms the output style into a single line
  35:set list on;
  36:
  37:-- This is the other clever bit. It prevents echoing the
  38:-- executed command to file.
  39:set echo off;
  40:
  41:-- Redirect output to file.
  42:output autogen_alter_all.ddl;
  43:
  44:-- Do the real work here
  45:select trim(RDB$RELATION_NAME) ||
  46: ' add INSERTED_ON TIMESTAMP default ''now'' NOT NULL ' ||
  47: ', add INSERTED_BY VARCHAR(31) default CURRENT_USER NOT NULL ' ||
  48: ', add MODIFIED_ON TIMESTAMP default ''now'' NOT NULL ' ||
  49: ', add MODIFIED_BY VARCHAR(31) default CURRENT_USER NOT NULL ' ||
  50: '; '
  51:as "Alter table "
  52:from RDB$RELATIONS
  53:where RDB$RELATION_NAME not starting with 'RDB$';
  54:
  55:select ' set term ^;' from rdb$database as "";
  56:set term ^;
  57:select 'tub_' || trim(RDB$RELATION_NAME) || ' for ' ||
  58: trim(RDB$RELATION_NAME) ||
  59: ' active before update position 32000 as ' ||
  60: ' begin ' ||
  61: ' New.MODIFIED_BY = CURRENT_USER; ' ||
  62: ' New.MODIFIED_ON = ''now''; ' ||
  63: ' if (new.INSERTED_ON != old.INSERTED_ON) then new.INSERTED_ON = old.INSERTED_ON; ' ||
  64: ' if (new.INSERTED_BY != old.INSERTED_BY) then new.INSERTED_BY = old.INSERTED_BY; ' ||
  65: ' end^ '
  66:
  67:as "create trigger "
  68:from RDB$RELATIONS
  69:where RDB$RELATION_NAME not starting with 'RDB$'^
  70:
  71:set term ;^
  72:select ' set term ;^' from rdb$database as "";
  73:
  74:-- uncomment the next line if running interactively.
  75:--set echo on;
  76:
  77:-- Set list to normal
  78:set list off;
  79:
  80:-- redirect output to our log file
  81:output alter_all.txt;
  82:
  83:-- make command execution visible again.
  84:set echo on;
  85:
  86:-- Execute our freshly minted script.
  87:input autogen_alter_all.ddl;
  88:
  89:--reset output to console
  90:output;
  91:
  92:-- Step back and admire our work
  93:show table revisions;
  94:show triggers;
  95:
  96: