Paul Gardner wrote:
I have a database with roughly 100 tables. I want to add a TIMESTAMP field called LASTUPDATED into each table. Next, I'll have a Trigger Before Update/Insert that says:
LastUpdated = current_timestamp;
Is there any way to do this without 100 different triggers? Some sort of trigger on a RDB$ table that runs each time?
Helen Borrie answers:
Not currently (I think it's among a few global options in the feature request list); but you could write a stored procedure that generates a script out to an external table, that you could run over any database:
create table script_output
external file 'c:\scripts\create_triggers.sql'
(
outputstring char(90),
linebreak char(2) /* or char if the script is for Linux */
);
commit;
create procedure create_triggers
as
declare variable tablename varchar(31);
declare variable outputstring varchar(90);
declare variable crlf char(2) = '
'; /* press Enter key between the two apostrophes */
begin
outputstring = 'SET TERM ^;';
insert into script_output values (:outputstring, :crlf);
for select r.rdb$relation_name from rdb$relations r
where r.rdb$relation_name not starting with 'RDB$'
and r.rdb$view_source is null
and exists (
select 1 from rdb$relation_fields rf
where rf.rdb$relation_name = r.rdb$relation_name
and rf.rdb$field_name = 'LASTUPDATED')
into :tablename do
begin
outputstring = 'create trigger ba_' || tablename || ' for ' || tablename;
insert into script_output values (:outputstring, :crlf);
outputstring = 'active before insert or update as ';
insert into script_output values (:outputstring, :crlf);
outputstring = 'begin';
insert into script_output values (:outputstring, :crlf);
outputstring = 'new.LastUpdated = CURRENT_TIMESTAMP;';
insert into script_output values (:outputstring, :crlf);
outputstring = 'end ^';
insert into script_output values (:outputstring, :crlf);
insert into script_output values (' /* * */', :crlf);
end
outputstring = 'SET TERM ;^';
insert into script_output values (:outputstring, :crlf);
outputstring = '/* That''s all folks! */';
insert into script_output values (:outputstring, :crlf);
end
- Treat this as an example as it hasn't been tested for typos (I don't have 100 tables with LASTUPDATED as a column name!)
- Don't forget to create the directory that you define in the filespec.
- Remember to delete or rename any file in that directory that has the same name as the one you're specifying.
You can make several handy procedures in this vein and keep them in your snippets toolbox.