2 edited tags
| link
1
source | link

Trigger After Insert is preventing insertion

I have two tables that must have similar information, and one of them can be considered the base table for to other, so in order to have the info in the two tables, I create this trigger:

ALTER TRIGGER [dbo].[TR_INSERTED_READER_LOG] 
ON [dbo].[TBL_READER_LOG]
AFTER INSERT
AS
IF EXISTS(SELECT * FROM INSERTED WHERE REL_ENROLLNUMBER < 9999999)
BEGIN
    SET LANGUAGE us_english
    DECLARE @DEV_NID INT
    DECLARE @DATE DATETIME
    DECLARE @USE_NID INT
    DECLARE @REL_MODE INT 
    SELECT @DEV_NID = DEV_NID, 
           @DATE = LOG_DATE, 
           @USE_NID = REL_ENROLLNUMBER, 
           @REL_MODE = REL_MODE 
    FROM TBL_DEVICE 
    INNER JOIN TBL_READER_LOG ON DEV_ORIGINAL_NID = RDR_NID WHERE DEV_TYPE = 1
    INSERT INTO TBL_USER_LOGS (DEV_NID, LOG_DATE, USE_NID, LOG_MODE) VALUES (@DEV_NID, @DATE, @USE_NID, @REL_MODE)
END

It works fine, however when the insert statement in the trigger has an error (like a FK violation) is preventing that the data in the first table can be inserted.

I have searched in the documentation of Microsoft and says that After Insert execute the Trigger when all the instructions has already done so, Why is preventing the insertion?