[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-sql
Subject: [SQL] Postgresql Rules
From: Staten Oliver <ssoliver () me-equip ! com>
Date: 2009-03-20 19:54:18
Message-ID: 1237578858.6665.34.camel () lapdance ! me-equip ! com
[Download RAW message or body]
I have three tables (CUST_CNTCTS, CUST_CO_BILL_ADDR, and
CUST_CNTCT_MAILING_LIST_ADDR) using postgresql 8.1.10
CUST_CNTCTS contains contacts for companies we do business with
CUST_CO_BILL_ADDR contains billing address for these companies
CUST_CNTCT_MAILING_LIST_ADDR associates contacts with addresses so we
know where to send marketing information for each contact
I have a rule (that works correctly) in CUST_CNTCTS that removes a
contact from CUST_CNTCT_MAILING_LIST_ADDR when their "mailing_list" flag
is turned from true to false
CREATE OR REPLACE RULE "mailing_list_to_malng-lst"
AS ON UPDATE TO meedb."CUST_CNTCTS"
WHERE old.mailing_list <> new.mailing_list AND new.mailing_list = false
DO DELETE FROM meedb."CUST_CNTCT_MAILING_LIST_ADDR"
WHERE new.cust_cntct_id = "CUST_CNTCT_MAILING_LIST_ADDR".cust_cntct_id;
COMMENT ON RULE "mailing_list_to_malng-lst" ON meedb."CUST_CNTCTS" IS 'If \
mailing_list is changed from true to false, remove the contact from the mailing \
list';
There is also a rule in CUST_CO_BILL_ADDR that sets
CUST_CNTCTS."mailing_list" to false when the address for a contact (that
is in CUST_CNTCT_MAILING_LIST_ADDR) is deactivated
CREATE OR REPLACE RULE "status_to_malng-lst" AS
ON UPDATE TO meedb."CUST_CO_BILL_ADDR"
WHERE old.status::text <> new.status::text AND new.status::text = 'i'::text
DO UPDATE meedb."CUST_CNTCTS" SET mailing_list = false
WHERE ("CUST_CNTCTS".cust_cntct_id IN
(SELECT "CUST_CNTCT_MAILING_LIST_ADDR".cust_cntct_id
FROM meedb."CUST_CNTCT_MAILING_LIST_ADDR"
WHERE "CUST_CNTCT_MAILING_LIST_ADDR".cust_bill_addr_id = \
new.cust_bill_addr_id)); COMMENT ON RULE "status_to_malng-lst" ON \
meedb."CUST_CO_BILL_ADDR" IS 'If the status of a company''s billing address is \
changed from active to inactive, remove the contacts associated with this address \
from the mailing list by setting the mailing_list flag in CUST_CNTCTS to FALSE';
The problem is that when I change the status of the address from a
(active) to i (inactive) the contacts associated with the address are
removed from CUST_CNTCT_MAILING_LIST_ADDR, but
CUST_CNTCTS."mailing_list" is not set to FALSE
Does anyone see any problems with what I am doing, or is this something
that can't be done with postgresql's rule system?
[Attachment #3 (text/html)]
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
<META NAME="GENERATOR" CONTENT="GtkHTML/3.18.3">
</HEAD>
<BODY>
I have three tables (CUST_CNTCTS, CUST_CO_BILL_ADDR, and \
CUST_CNTCT_MAILING_LIST_ADDR) using postgresql 8.1.10<BR> <BR>
CUST_CNTCTS contains contacts for companies we do business with<BR>
<BR>
CUST_CO_BILL_ADDR contains billing address for these companies<BR>
<BR>
CUST_CNTCT_MAILING_LIST_ADDR associates contacts with addresses so we know where to \
send marketing information for each contact <BR> <BR>
I have a rule (that works correctly) in CUST_CNTCTS that removes a contact from \
CUST_CNTCT_MAILING_LIST_ADDR when their "mailing_list" flag is turned from \
true to false <PRE>
CREATE OR REPLACE RULE "mailing_list_to_malng-lst"
AS ON UPDATE TO meedb."CUST_CNTCTS"
WHERE old.mailing_list <> new.mailing_list AND new.mailing_list = false
DO DELETE FROM meedb."CUST_CNTCT_MAILING_LIST_ADDR"
WHERE new.cust_cntct_id = "CUST_CNTCT_MAILING_LIST_ADDR".cust_cntct_id;
COMMENT ON RULE "mailing_list_to_malng-lst" ON \
meedb."CUST_CNTCTS" IS 'If mailing_list is changed from true to false, \
remove the contact from the mailing list'; </PRE>
There is also a rule in CUST_CO_BILL_ADDR that sets \
CUST_CNTCTS."mailing_list" to false when the address for a contact (that is \
in CUST_CNTCT_MAILING_LIST_ADDR) is deactivated <PRE>
CREATE OR REPLACE RULE "status_to_malng-lst" AS
ON UPDATE TO meedb."CUST_CO_BILL_ADDR"
WHERE old.status::text <> new.status::text AND new.status::text = 'i'::text
DO UPDATE meedb."CUST_CNTCTS" SET mailing_list = false
WHERE ("CUST_CNTCTS".cust_cntct_id IN
(SELECT "CUST_CNTCT_MAILING_LIST_ADDR".cust_cntct_id
FROM meedb."CUST_CNTCT_MAILING_LIST_ADDR"
WHERE \
"CUST_CNTCT_MAILING_LIST_ADDR".cust_bill_addr_id = new.cust_bill_addr_id)); \
COMMENT ON RULE "status_to_malng-lst" ON \
meedb."CUST_CO_BILL_ADDR" IS 'If the status of a company''s billing address \
is changed from active to inactive, remove the contacts associated with this address \
from the mailing list by setting the mailing_list flag in CUST_CNTCTS to FALSE'; \
</PRE> The problem is that when I change the status of the address from a (active) to \
i (inactive) the contacts associated with the address are removed from \
CUST_CNTCT_MAILING_LIST_ADDR, but CUST_CNTCTS."mailing_list" is not set to \
FALSE<BR> <BR>
Does anyone see any problems with what I am doing, or is this something that can't be \
done with postgresql's rule system? <PRE>
</PRE>
</BODY>
</HTML>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic