Identities, Defaults, Nulls, Triggers - Oh My!

• BillyWilly

  SSCrazy

  Points: 2304

  More actions

  October 29, 2002 at 8:33 am

  #101288

  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

• NPeeters

  SSChampion

  Points: 12358

  More actions

  October 29, 2002 at 2:41 pm

  #439744

  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