Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
It runs in the same transaction as the original statement that fired the trigger. –  Jon Seigel Nov 19 '13 at 16:50
    
How can I do to prevent this? –  Ruben Gomez Nov 19 '13 at 16:53

1 Answer 1

up vote 2 down vote accepted
  1. The trigger is part of the original transaction. Just because it happens after the original insert does not mean that everything doesn't get rolled back in the event of an error. A rollback does not just roll back the last thing that ran. Take this example:

    BEGIN TRANSACTION;
    INSERT dbo.table1 ...;
    INSERT dbo.table2 ...;
    ROLLBACK TRANSACTION;
    

    Surely you don't expect the rollback to only affect the 2nd insert?

  2. You might consider using TRY/CATCH or an INSTEAD OF trigger, which will allow you to handle other stuff without suffering from the errors. Or simply checking first

  3. Your use of declaring a bunch of variables, assigning them from a join, and then inserting seems to be both inefficient and problematic. What happens if more than one row matches the join? You know that if you have a multi-row operation, the trigger only fires once, so you'll only end up with one arbitrary row being inserted, right? Why not:

    INSERT dbo.TBL_USER_LOGS(DEV_NID, LOG_DATE, USE_NID, LOG_MODE)
      SELECT DEV_NID, LOG_DATE, REL_ENROLLNUMBER, REL_MODE -- table prefixes?
    FROM dbo.TBL_DEVICE -- always use dbo prefix! 
    INNER JOIN dbo.TBL_READER_LOG 
    ON DEV_ORIGINAL_NID = RDR_NID -- which table do these columns come from?
    WHERE DEV_TYPE = 1; -- which table does this column come from?
    

    As an aside, I notice that your query doesn't seem to take the inserted pseudo-table into account. Shouldn't the insert have something to do with the row(s) actually affected by the trigger?

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.