From: M.D.G. Lange <mlange@dltmedia.nl>
To: pgsql-sql@postgresql.org
Subject: rule or trigger?
Date: Tue, 07 Jun 2005 12:28:53 +0200
Message-ID: <42A576E5.1040100@dltmedia.nl> (raw)
In order for a "dictionary" system I have created the following system:
tbllanguages
- ID
- name
Primary key ( ID )
tbldictionary
- wordid
- languageid
- value
Primary key ( wordid, languageid)
The idea is to have a word id in several languages, so you only have to
look up the word id and give the language you would like to get the
message in and you'll be presented the translation.
So far so good... only wordid is not unique, making it not suitable to
use it in foreign keys... however I'd like a similar idea:
tblsystemmessages
- ID
- wordid
- pgsqlerrorcode
Primary key ( ID )
"Foreign key" wordid references tbldictionary.wordid
It is not possible to create a constraint Foreign key for "wordid". No
problem there, but I want to be certain that a given wordid exists in
tbldictionary.
Would I have to create a "RULE" or a "TRIGGER" to be certain that the
wordid is existing in tbldictionary in whatever language.
I have the idea that a trigger will not prevent the insertion, or did I
not read well enough?
Would the following rule do what I want?
CREATE OR REPLACE RULE 'systemmessages_rule' AS ON INSERT TO
"public.tblsystemmessages"
DO INSTEAD (
IF count ( SELECT DISTINCT tbldictionary.wordid ) > 0
INSERT INTO tblsystemmessages ( ID, wordid, pgsqlerrorcode )
VALUES ( NEW.ID, NEW.wordid, NEW.pgsqlerrorcode )
ELSE
NOTHING
);
But would this not recursively call this rule?
Michiel
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mlange@dltmedia.nl
Subject: Re: rule or trigger?
In-Reply-To: <42A576E5.1040100@dltmedia.nl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox