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 t…

Accepted 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_id`s 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_id`s 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_id`s 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_id`s 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

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

Solution

Problem

Related problems