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