September 22, 2008 at 2:50 pm
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
September 22, 2008 at 3:05 pm
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?
September 23, 2008 at 5:31 am
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.
September 24, 2008 at 2:45 am
Are you sure the @SystemUser variable gets set?
N 56°04'39.16"
E 12°55'05.25"
September 24, 2008 at 5:25 am
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.
September 24, 2008 at 6:32 am
Are you certain the trigger isn't firing?
Add a print statement as the first statement of the trigger just to verify this.
September 24, 2008 at 7:11 am
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.
September 24, 2008 at 8:09 am
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?
September 24, 2008 at 8:24 am
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?
September 24, 2008 at 9:14 am
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.
September 24, 2008 at 9:22 am
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.
September 24, 2008 at 10:52 am
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 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply