How to emulate a BEFORE INSERT trigger in T-SQL SQL Server for super subtype Inheritance entities

Question

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

User · Answer

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

User · Answer

Sometimes a BEFORE trigger can be replaced with an AFTER one  but this doesn t appear to be the case in your situation  for you clearly need to provide a value before the insert takes place  So  for that purpose  the closest functionality would seem to be the INSTEAD OF trigger one  as  marc s has suggested in his comment   Note  however  that  as the names of these two trigger types suggest  there s a fundamental difference between a BEFORE trigger and an INSTEAD OF one  While in both cases the trigger is executed at the time when the action determined by the statement that s invoked the trigger hasn t taken place  in case of the INSTEAD OF trigger the action is never supposed to take place at all  The real action that you need to be done must be done by the trigger itself  This is very unlike the BEFORE trigger functionality  where the statement is always due to execute  unless  of course  you explicitly roll it back   But there s one other issue to address actually  As your Oracle script reveals  the trigger you need to convert uses another feature unsupported by SQL Server  which is that of FOR EACH ROW  There are no per-row triggers in SQL Server either  only per-statement ones  That means that you need to always keep in mind that the inserted data are a row set  not just a single row  That adds more complexity  although that ll probably conclude the list of things you need to account for   So  it s really two things to solve then    replace the BEFORE functionality  replace the FOR EACH ROW functionality    My attempt at solving these is below   CREATE TRIGGER sub trg ON sub1 INSTEAD OF INSERT AS BEGIN   DECLARE  new super TABLE       super id int        INSERT INTO super  subtype discriminator    OUTPUT INSERTED super id INTO  new super  super id    SELECT  SUB1  FROM INSERTED     INSERT INTO sub  super id    SELECT super id FROM  new super  END    This is how the above works    The same number of rows as being inserted into sub1 is first added to super  The generated super id values are stored in a temporary storage  a table variable called  new super   The newly inserted super ids are now inserted into sub1    Nothing too difficult really  but the above will only work if you have no other columns in sub1 than those you ve specified in your question  If there are other columns  the above trigger will need to be a bit more complex   The problem is to assign the new super ids to every inserted row individually  One way to implement the mapping could be like below   CREATE TRIGGER sub trg ON sub1 INSTEAD OF INSERT AS BEGIN   DECLARE  new super TABLE       rownum   int IDENTITY  1  1       super id int        INSERT INTO super  subtype discriminator    OUTPUT INSERTED super id INTO  new super  super id    SELECT  SUB1  FROM INSERTED     WITH enumerated AS       SELECT    ROW NUMBER   OVER  ORDER BY  SELECT 1   AS rownum     FROM inserted       INSERT INTO sub1  super id  other columns    SELECT n super id  i other columns   FROM enumerated AS i   INNER JOIN  new super AS n   ON i rownum   n rownum  END    As you can see  an IDENTIY 1 1  column is added to  new user  so the temporarily inserted super id values will additionally be enumerated starting from 1  To provide the mapping between the new super ids and the new data rows  the ROW NUMBER function is used to enumerate the INSERTED rows as well  As a result  every row in the INSERTED set can now be linked to a single super id and thus complemented to a full data row to be inserted into sub1   Note that the order in which the new super ids are inserted may not match the order in which they are assigned  I considered that a no-issue  All the new super rows generated are identical save for the IDs  So  all you need here is just to take one new super id per new sub1 row   If  however  the logic of inserting into super is more complex and for some reason you need to remember precisely which new super id has been generated for which new sub row  you ll probably want to consider the mapping method discussed in this Stack Overflow question    Using merge  output to get mapping between source id and target id

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

Examples related to sql-server

Examples related to tsql

Examples related to triggers

Examples related to azure-sql-database