Hello,
I'm aktually create a database with postgres, but I'm a long time out-of-date working with Postgres (I think I have
workedlast time with PG 5.4),
so I need some help to create a working trigger / rule solution:
I have got a master table and a slave table (1:N). The master table create a key with a sequence, on an insert on the
mastertable
I create with a after-insert trigger a dataset of the slave table with the value "pk master table, 'HEAD'". PK on the
slavetable is
the master-table-pk & the text. The name must be always uppercase, so I would like to create a rule that shifts the
stringinto
uppercase letters, but if I create the rule and add INSEAD ("UPPER"(new.positionname)) I get a syntax error.
At next the dataset with the HEAD value need not be delete or changed of a corresponding dataset on the master table
exists.I have create two
rules for update & delete with a do-nothing if the value is HEAD. But I don't know how can I delete the value if the
dataseton the master table
is deleted.
Which case is the best choice to do this? Should I use trigger or rules? I need this:
if a dataset is insert on the master table, a new dataset must be create on the slave table with the correct key value
if a dataset on the slave table is updated or delete which has the name "HEAD" the action need not be performed
if a dataset on the master table is deleted all datasets on the slave table must be also deleted.
the key field on the slave table must be always upper case (index & content value)
Hope anybody can send me some tips
Thanks
Phil