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?