INSTEAD OF INSERT trigger not firing

  • This is a very strange issue. I have INSTEAD OF INSERT triggers on many of my tables in order to run a stored procedure and apply that value to one field in the data set. These triggers have been working just fine for a long time but all of a sudden, they stopped.

    I did a trace while inserting data into a table, and the trigger does not fire.

    It's nothing fancy, nor is the trigger disabled. Anyone have any ideas what else I can check?

    CREATE TRIGGER [dbo].[INSERT_DDH] ON [dbo].[DDH]

    INSTEAD OF INSERT

    AS

    DECLARE @SYSTEMUSER_ID AS INT

    --Lookup the user's Centric account

    EXECUTE [dbo].[LOOKUP_USER_ID_FROM_WINDOWS]

    @SYSTEMUSER_ID OUTPUT

    INSERT INTO [dbo].[DDH]

    ([DDH]

    ,[BUSINESS_UNIT_ID]

    ,[MAXLENGTH]

    ,[ENABLED]

    ,[CREATED_BY]

    ,[CREATED_DATE]

    ,[USER_ID]

    ,[LAST_MODIFIED])

    SELECT

    [DDH],

    [BUSINESS_UNIT_ID],

    [MAXLENGTH],

    [ENABLED],

    COALESCE([CREATED_BY], @SYSTEMUSER_ID),

    [CREATED_DATE],

    COALESCE([USER_ID], @SYSTEMUSER_ID),

    [LAST_MODIFIED]

    FROM

    INSERTED

  • So - what happens? All your instead of trigger seems to do is to make sure two fields are populated. Do you get rows inserted? No rows?

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • The two fields that get populated are set to NOT NULL, so the INSERT operation fails. If I were to supply values for them, the INSERT would succeed, but that's just because of the table structure.

  • Are you sure the @SystemUser variable gets set?


    N 56°04'39.16"
    E 12°55'05.25"

  • I forgot to mention, if I run the stored procedure it returns a value. However, since the trigger isn't firing, the procedure doesn't run.

  • Are you certain the trigger isn't firing?

    Add a print statement as the first statement of the trigger just to verify this.

  • As unbelievable as it is, it isn't firing. I did try adding a print statement to the beginning of the trigger and nothing. I've also checked to make sure that the database is running in SQL Server 2000 mode (not 7.0 or less) since INSTEAD OF triggers were new to 2000. That isn't the case either. I just triple-checked that it isn't disabled again. The only different thing is that I used SSMS to connect to the database while creating the trigger instead of Query Analyzer. But when I created the trigger in the first place I used SSMS as well and it worked then.

  • Hmm, strange. Just thinking of things to try:-

    If it worked when it was added using QA, but not SSMS, what happens if you drop the trigger in SSMS and re-add it in QA?

    It isn't a problem with owners is it? i.e. you have the same table owned by dbo, another one belonging to a different owner, and the trigger is on the wrong table?

    What does sp_helptrigger tell you?

  • Ian Massi (9/24/2008)


    I forgot to mention, if I run the stored procedure it returns a value. However, since the trigger isn't firing, the procedure doesn't run.

    Don't trust that the context under which you're running it is the same as that for the trigger. I'd make the print statement spit out the contents of the @systemuserID variable.

    If you don't think it's firing, then drop it and readd it in.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • I hadn't thought about confirming ownership but the table is the only one of that name and it is owned by dbo, as is the trigger. I also checked the dependencies of the trigger and it listed the table as being an object that it depends on.

    sp_helptrigger shows (columns pivoted to display better)

    trigger_name: INSERT_DDH

    trigger owner: dbo

    isupdate: 0

    isdelete: 0

    isinsert: 1

    isafter: 0

    isinsteadof: 1

    I dropped it and recreated it in SSMS (didnt' work), then dropped and recreated it in Query Analyzer and it is still not firing. I did try adding the print statement again and nothing came out. This is the most bizarre thing I've ever encountered in my 4 years of working with SQL Server.

    Profiler shows the following (stored procedure statements starting and finishing are on):

    SQL:BatchStarting INSERT INTO [dbo].[DDH]([DDH], [BUSINESS_UNIT_ID]) VALUES('13', 145)

    SQL:BatchCompleted INSERT INTO [dbo].[DDH]([DDH], [BUSINESS_UNIT_ID]) VALUES('13', 145)

    The error that comes out is:

    Msg 233, Level 16, State 2, Line 1

    The column 'CREATED_BY' in table 'dbo.DDH' cannot be null.

    Msg 233, Level 16, State 2, Line 1

    The column 'USER_ID' in table 'dbo.DDH' cannot be null.

  • I should have looked at the original post in detail.

    Although you can use the INSTEAD OF trigger to replace data in columns, the nullability is checked BEFORE the trigger is executed.

    That explains why the trigger isn't firing... the INSERT statement is being bounced before it gets that far. You will need to change your calling code to supply a dummy value, or change the table to make the columns NULLable.

  • Ian Scarlett (9/24/2008)


    I should have looked at the original post in detail.

    Although you can use the INSTEAD OF trigger to replace data in columns, the nullability is checked BEFORE the trigger is executed.

    That explains why the trigger isn't firing... the INSERT statement is being bounced before it gets that far. You will need to change your calling code to supply a dummy value, or change the table to make the columns NULLable.

    Well, I set the two fields to be nullable and it works perfectly! The bizarre thing is that on SQL Server 2005, I have a couple of identical databases where these fields are set to NOT NULL and this works. I wonder if this is a difference between the two versions? Either way, this is a perfectly acceptable solution since the fields will be populated anyway. Thank you for your help!

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply