[sql-server] How to emulate a BEFORE INSERT trigger in T-SQL / SQL Server for super/subtype (Inheritance) entities?

This is on Azure.

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 BEFORE INSERT trigger to accomplish this. How would one accomplish this in SQL Server / T-SQL?

DDL

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)
);

I wish for an insert into sub1 to fire a trigger that actually inserts a value into super and uses the super_id generated to put into sub1.

In Oracle, this would be accomplished by the following:

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;

Please advise on how I would simulate this in T-SQL, given that T-SQL lacks the BEFORE INSERT capability?

While Andriy's proposal will work well for INSERTs of a small number of records, full table scans will be done on the final join as both 'enumerated' and '@new_super' are not indexed, resulting in poor performance for large inserts.

This can be resolved by specifying a primary key on the @new_super table, as follows:

DECLARE @new_super TABLE (
  row_num INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
  super_id   int
);

This will result in the SQL optimizer scanning through the 'enumerated' table but doing an indexed join on @new_super to get the new key.