On Insert Trigger For MS SQL Server

Question

I am wanting to insert into data into my table sn when data is inserted into NFI - I have added the below trigger to my database, but the data is not inserted into sn when inserted into NFI.

What do I need to change so this successfully runs?

CREATE TRIGGER UpdateData ON nfi
FOR INSERT
AS

INSERT INTO SN
        (esn,ern)
    SELECT
        esn,ern
        FROM inserted
        where esn not in (select esn from nfi)

go

Answer 1

When the trigger is fired, the data is already present in the nfi table. The clause where esn not in (select esn from nfi) makes sure that nothing is ever inserted into sn.

When you create a trigger and do not explicitely specify AFTER or INSTEAD OF, then AFTER is the default. From the CREATE TRIGGER documentation, you can read:

FOR | AFTER AFTER specifies that the DML trigger fires only when all operations specified in the triggering SQL statement have launched successfully. All referential cascade actions and constraint checks must also succeed before this trigger fires.

AFTER is the default when FOR is the only keyword specified.