On Wed, Dec 25, 2019 at 11:55:51AM -0800, Adrian Klaver wrote: > On 12/25/19 11:08 AM, stan wrote: > > > > On Wed, Dec 25, 2019 at 08:28:45AM -0800, Adrian Klaver wrote: > > > On 12/25/19 7:26 AM, stan wrote: > > > > I am writing a trigger/function to make certain a default item, and its key > > > > exist when an insert is called. EG > > > > > > > > The trigger gets called on insert to T1 If column c1 is NULL in the NEW > > > > structure, I need to check table t2 to get the key associated with the > > > > default for this column. However, if the default is not yet inserted into > > > > t2, I an to go ahead and insert it. > > > > > > I'm with Pavel in not understanding what you want to do. This prevents any > > > clear discussion on what to do below. To help: > > > > > > 1) Schema of t1 and t2. > > > > > > 2) Default for what column? > > > > > > 3) What is the key? > > > > > > > First of all, thanks to both of you for your fast response . > > > > Let me clarify. > > > > I have a table that records will be inserted into. Several of the columns > > in this table must be non NULL, and they are actually keys from other > > tables. Like > > > > Table t1 > > has a column like cost_category_key > > > > So if an INSERT to this table gets called with this column as a NULL, I am > > creating a function that will query for the default category, like > > > > SELECT cost_category_key from t2 where type = 'Misc' > > > > Now suppose that the default category has not yet been inserted in T2. I > > can easily detect this as the SELECT will return a NULL. So what I want to > > do is go ahead and insert this row. Once this is done, the correct default > > row will exist in T2, but I still need the (automatically assigned) key for > > this row to place in the NEW. structure for the function that is called On > > insert to t1, and checks to see if the value supplied for this key is in > > t2. > > > > Make more sense? > > No. It looks like you are trying to do a backwards FK. I would say your life > would be a lot easier if you used FK's as intended e.g. have t2 be the > parent table and INSERT the correct type/key combination there first before > you INSERT into t1, as separate operations. As you script it out above you > have to know what the the type/key is before you INSERT into t1 anyway. No, the key is auto generated using a sequence on the INSERT into t2 Which is where this gets interesting. If I try to select for it and a NULL is returned, then I KNOW I need to insert it. As a consequence of this, the new key is generated. I need to populate (replace the NULL) that comes in the NEW. record with the newly generated key. The original article says that I can get the results of an insert, to use. That's what I have in mind. Other approaches welcome. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
