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.klaver@xxxxxxxxxxx
