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:

| 
|