rule or trigger?
От | M.D.G. Lange |
---|---|
Тема | rule or trigger? |
Дата | |
Msg-id | 42A576E5.1040100@dltmedia.nl обсуждение исходный текст |
Ответы |
Re: rule or trigger?
Re: rule or trigger? |
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: