Triggers/Rules?

From: "Geoff" <geoff(at)metalogicplc(dot)com>
To: "Pgsql-Admin (E-mail)" <pgsql-admin(at)postgresql(dot)org>
Subject: Triggers/Rules?
Date: 2002-12-05 16:25:39
Message-ID: FDCF842C8B1DD311833C0090277C04B518C257@augusta.planmatics.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Guys, I've have a legacy summary table ( monitor_summary ) which represents
a count of the number of records in another table ( document_status ).
Here are the table definitions.

CREATE TABLE monitor_summary (

id SERIAL PRIMARY KEY, -- Internal ID.
version INTEGER, -- Record Version ( used for locking ).
cdate TIMESTAMP WITH TIME ZONE, -- Record Creation Date.
mdate TIMESTAMP WITH TIME ZONE, -- Record Modification Date.
direction CHAR(1), -- Document Direction
unit VARCHAR(16), -- Document Business Unit
msgtype VARCHAR(4), -- Document Message Type
status VARCHAR(4), -- Document Status
total INTEGER DEFAULT 0 -- Document Total Count

);

create table document_status
(
id SERIAL PRIMARY KEY,
version integer,
cdate timestamp WITH TIME ZONE,
mdate timestamp WITH TIME ZONE,
filename varchar (64),
direction char(1),
partner varchar(10),
msgtype char(4),
docref varchar(16),
status char(4),
statusdesc text,
unit text,
pathtofile text,
ourcomp varchar(30),
partnercomp varchar(30),
ourinvref varchar(30),
ourordref varchar(30),
ourdlvref varchar(30),
partnerinvref varchar(30),
partnerordref varchar(30),
partnerdlvref varchar(30),
CONSTRAINT "document_status_key" UNIQUE ("filename", "direction")
);

The senario goes like this....

I want a rule/trigger or several, so when a document_status record get's
updated, it updates the monitor summary.
There are some conditions though.

It's all based on unit & status & direction & msgtype.

Some sample data.

monitor summary
===============

direction | unit | msgtype | status | total
-----------+------+---------+--------+-------
O | AB | 0001 | CONF | 606
O | ACP | 0001 | CONF | 554
O | ACP | 0020 | CONF | 468
O | BAT | 0001 | CONF | 70
O | BOD | 0001 | CONF | 404
O | BOD | 0001 | TOUT | 1
O | BRW | 0001 | CONF | 169
O | BW | 0001 | CONF | 533
O | BW | 0020 | CONF | 2

document_status
===============

filename | partner | status | unit | msgtype | direction
------------------+---------+--------+------+---------+-----------
PABTUBE01000265 | TUBE01 | CONF | AB | 0001 | O
PABASDC01000881 | ASDC01 | CONF | AB | 0001 | O
PABASDC01000882 | ASDC01 | CONF | AB | 0001 | O
PACPARMS00000773 | ARMS00 | CONF | ACP | 0001 | O
PACPARMS00000774 | ARMS00 | CONF | ACP | 0001 | O
PACPARMS00000775 | ARMS00 | CONF | ACP | 0001 | O
PACPARMS00000772 | ARMS00 | CONF | ACP | 0001 | O
PBODISTI01000241 | ISTI01 | CONF | BOD | 0001 | O
PBODCSTK00000207 | CSTK00 | TOUT | BOD | 0001 | O

now, we have a daemon which updates the document_status table and basically
changes it's status from for example TOUT to CONF. What I'd like to do is to
have a trigger/rule that would represent this change in the monitor summary
table, so it would then deduct 1 from TOUT and add 1 to CONF where the
document_status.unit = monitor_summary.unit and document_status.msgtype =
monitor_summary.msgtype........ basically where the unit, direction, msgtype
and status are the same in both tables....
However, there are several status's... TOUT, UNCO, INVA, DELE, UNRE, CONF,
INTE and also a direction of O and I. So I need to take into account all
possible actions...

I know how to do simple rules, but I'm not sure how to add in case
statements etc etc, I would really really appreciate some pointers on how to
achieve my goal and relieve me of this thorn in my side!

thanks very much

geoff ellis

Browse pgsql-admin by date

  From Date Subject
Next Message Jie Liang 2002-12-05 18:51:12 list schema
Previous Message Eric Hallander 2002-12-05 15:51:41 Re: how to cancel a query ?