October 29, 2002 at 8:33 am
Me again...
I was in need of having an "Account" point to another "DebitThisAccount" for billing purposes.
So, I created a column in my Accounts table which is a Foreign Key to itself, it's own unique identifier called "DebitThisAccount".
It's NON NULL, with no Default value.
Fine.
Now the problem...
I'd like to:
1) Keep the column as NOT NULL
2) Keep a RI Foreign Key to the same table
(From the one account column to the Unique identifier - a self-join)
3) When a new account is INSERTED - take the new Accounts Identity and insert it into itelf as "DebitThisAccount".
AS it stands - I have to:
1) Make the column NULL
2) Create an After Insert Trigger to get the new identity.
3) Create an Update Trigger to make sure the change to the "DebitThisAccount" column is not NULL.
Am I running in circles for no Reason ????
Thanks in advance - B
October 29, 2002 at 2:41 pm
Alternative solution (haven't tried it, but should work).
1. Keep the NOT NULL RI Foreign Key DebitThisAccount column
2. Put a 'INSTEAD OF' INSERT Trigger that does exactly what you want. In a single insert statement.
Something like
create trigger tr_test ON your_table INSTEAD OF INSERT
AS
-- needed if Account is an identity field
SET IDENTITY_INSERT your_table ON
Insert into your_table (Account, DebitThisAccount) (select Account, Account FROM inserted)
-- Reset identity_insert to the normal value
SET IDENTITY_INSERT your_table OFF
This should work, since an instead of trigger is executed before any constraints are checked (and NOT NULL is a constraint, I believe).
If it does not work because of a violation of the NOT NULL constraint, you can assign a dummy value as default to DebitThisAccount.
Edited by - NPeeters on 10/29/2002 2:41:33 PM
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply