Thread: how to update tables and archive the changed data?

how to update tables and archive the changed data?

From
"Wilkinson, Graeme"
Date:
Using Postgres to store server information. Currently running perl scripts
to update it and enter new data, but I would like to get the database to do
more work.

From my limited knowledge of databases I have worked out that functions and
triggers are what I need to use.

Here is a cut down version of what we are doing. A table to store some
server details. Actually have 40 or so tables storing disk, software,
services, hotfixes, timezone, etc.
CREATE TABLE server (
    serverid SERIAL PRIMARY KEY,
    customer_account_name VARCHAR(30) NOT NULL,
    asset_tag VARCHAR(30),
    serial_number VARCHAR(30),
    registered_organization VARCHAR(50),
    nodename VARCHAR(30) NOT NULL,
    domain VARCHAR(30) NOT NULL,
    server_role VARCHAR(30),
    installed_memory VARCHAR(5),
    number_of_cpus VARCHAR(5),
    bios_string VARCHAR(200),
    gold_disk_ver VARCHAR(40),
    run_time TIMESTAMP NOT NULL
);
a table to store the changed details.
CREATE TABLE audit (
    id SERIAL PRIMARY KEY,
    table_name VARCHAR(30),
    column_name VARCHAR(30),
    row_number VARCHAR(30),
    old_value VARCHAR(600),
    run_time TIMESTAMP NOT NULL
);
each day scripts run gathering server info and send it back to our reporting
server that imports the data into  the database. If we change the memory in
a server I want to be able to write the data into the database and have a
function realise that the value for installed_memory has changed so it will
write the old value into the audit table.

from the reading I have done I can't work out how to do this, must be a bit
slow or I haven't' read the right stuff.
So my questions are:
What should the syntax/structure of the function look like and What should
the syntax/structure of the trigger look like?
Where is some good reading on functions and triggers with lots of examples?

Thanks.
Graeme Wilkinson
NSM & Messaging Operations Management SA
EDS Australia


Re: how to update tables and archive the changed data?

From
Josh Berkus
Date:
Graeme,

> Using Postgres to store server information. Currently running perl scripts
> to update it and enter new data, but I would like to get the database to do
> more work.

I believe that Joe Conway posted a sample solution to your problem on the SQL
list a couple-three months ago, using his DBLink module in /contrib.  Search
the archives of the SQL list.

--
Josh Berkus
Aglio Database Solutions
San Francisco