title: How to emulate a BEFORE INSERT trigger in T-SQL / SQL Server for super/subtype (Inheritance) entities?
body: <p>This is on Azure.</p>
  
  <p>I have a supertype entity and several subtype entities, the latter of which needs to obtain their foreign keys from the primary key of the super type entity on each insert. In Oracle, I use a <code>BEFORE INSERT</code> trigger to accomplish this. How would one accomplish this in SQL Server / T-SQL?</p>
  
  <p>DDL</p>
  
  <pre><code>CREATE TABLE super (
   super_id int IDENTITY(1,1)
   ,subtype_discriminator char(4) CHECK (subtype_discriminator IN ('SUB1', 'SUB2')
   ,CONSTRAINT super_id_pk PRIMARY KEY (super_id)
  );
  CREATE TABLE sub1 (
   sub_id int IDENTITY(1,1)
  ,super_id int NOT NULL
  ,CONSTRAINT sub_id_pk PRIMARY KEY (sub_id)
  ,CONSTRAINT sub_super_id_fk FOREIGN KEY (super_id) REFERENCES super (super_id)
  );
  </code></pre>
  
  <p>I wish for an insert into <code>sub1</code> to fire a trigger that actually inserts a value into <code>super</code> and uses the <code>super_id</code> generated to put into <code>sub1</code>.</p>
  
  <p>In Oracle, this would be accomplished by the following:</p>
  
  <pre><code>CREATE TRIGGER sub_trg
      BEFORE INSERT ON sub1
      FOR EACH ROW
  DECLARE
      v_super_id int; //Ignore the fact that I could have used super_id_seq.CURRVAL
  BEGIN
      INSERT INTO super (super_id, subtype_discriminator) 
          VALUES (super_id_seq.NEXTVAL, 'SUB1') 
          RETURNING super_id INTO v_super_id;
      :NEW.super_id := v_super_id;
  END;
  </code></pre>
  
  <p>Please advise on how I would simulate this in T-SQL, given that T-SQL lacks the <code>BEFORE INSERT</code> capability?</p>