Re: Syntax question about returning value from an insert

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.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com