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?
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin