Solved

sql server tempdb trigger not firing based on user/process

Posted on 2012-03-30
8
426 Views
Last Modified: 2012-06-27
Hi -
I have a AFTER INSERT/UPDATE trigger on a table in TempDB (to be explained further down)
which does not fire/execute when a record is inserted or updated from "applications".

However, when I go into SSMS and update a record I can watch it execute in a profiler trace without error.

Is there any obvious reason for this I am overlooking ?
Google has not been my friend so far on this search....


Details:
----------------------
This is a local application running SQLEXPRESS and using tempdb for speed due to many, many singluar and batch INSERTS.

I tried/and thought maybe something to do with creating the trigger "with execute as owner" but that does not seem to be the issue.

Additionally - the trigger is doing an INSERT/UPDATE into a table in another database (same instance) - again, this works fine if I execute it in SSMS.

In this scenario - I happen to be logged into SSMS and the database as domain\administrator - and the application is running as that user as well.
My assumption being, for testing - that indeed both processes are running as same user, eliminating permissions as the reason....
0
Comment
Question by:bbaley
  • 4
  • 3
8 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 37788892
Have you verified that the trigger is really not firing?

Such as put a non-conditioned INSERT to a dummy/trace as the *first* thing in the trigger to see if the trigger was entered.

Maybe there is just an error in the INSERT/UPDATE statement when the app fires the trigger (?).
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 37788900
The trigger code itself would be extremely helpful.

Also, certain types of statements may not fire triggers, such as BULK INSERTs.  Verify that your app isn't using that type of statement.  Not likely but I mention it just to be thorough.
0
 

Author Comment

by:bbaley
ID: 37794127
Scott,
I am running SQL Profiler trace (with SP:StatementCompleted, SP:StatementStarted selected) and I see the insert or update happen, but the trigger does not show up in the trace.

Primarily I wanted to make sure there was nothing specific to TempDB I should be aware of.

I can execute all code in a test script outside the trigger to verify no errors in the executing code by replacing the INSERTED table reference with a #temp table reference, etc. (in other words I have code that puts a record into a table called #INSERTED and I replace that in the test code etc.)


here is the trigger code:
(along with some debugging stuff in there  I was trying...)
======================
USE [tempdb]
GO
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trg_Insert_iFixLog]'))
DROP TRIGGER [dbo].[trg_Insert_iFixLog]
GO
USE [tempdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[trg_Insert_iFixLog]
   ON  [tempdb].[dbo].[iFix]
   --with execute as owner  
   AFTER UPDATE,INSERT
AS
BEGIN

--PRINT 'trigger fired!'

IF (UPDATE(A_ALMEXT1) or
      UPDATE(A_ALMEXT2) or
      UPDATE(A_AUTO) or
      UPDATE(A_CV) or
      UPDATE(A_DESC) or
      UPDATE(A_ENAB) or
      UPDATE(F_CV)  or
      UPDATE(A_NAME)
      )
      begin try
            --print 'begin try'
            
            SET NOCOUNT ON;

            declare @date datetime
            SET @date = GETDATE()
            
            --EXEC iFixLogging.dbo.sp_UPSERT_LogData(
            declare @tagname varchar(60)
            SET @tagname = (SELECT TagName FROM INSERTED)
            
            --print 'got tag name ' + @tagname
            
            declare @recs int
            SELECT @recs = (SELECT COUNT(*) FROM  iFixLogging.dbo.iFix_LogData WHERE TagName = @tagname)
                              
            --print 'got rec count' + STR(@recs)
                              
            IF (@recs <= 0)
            BEGIN                  
            
                  --print 'inserting!'
                  
                  INSERT INTO
                        iFixLogging.dbo.iFix_LogData (
                                                      TagIndex
                                                        ,TagName
                                                        ,A_ALMEXT1
                                                        ,A_ALMEXT2
                                                        ,A_AUTO
                                                        ,A_CV
                                                        ,A_DESC
                                                        ,A_ENAB
                                                        ,A_NAME
                                                        ,F_CV
                                                        ,LastModified)
                        (SELECT
                              TagIndex
                              ,TagName
                              ,A_ALMEXT1
                              ,A_ALMEXT2
                              ,A_AUTO
                              ,A_CV
                              ,A_DESC
                              ,A_ENAB
                              ,A_NAME
                              ,F_CV,
                              @date
                         from inserted)       
            END
            ELSE
            BEGIN
                  UPDATE i --iFix_Logging.dbo.iFix_LogData
                  SET
                  --      i.TagIndex            = u.TagIndex
                  --   ,i.TagName            = u.TagName
                     i.A_ALMEXT1            = u. A_ALMEXT1
                     ,i.A_ALMEXT2            = u.A_ALMEXT2
                     ,i.A_AUTO            = u.A_AUTO
                     ,i.A_CV                  = u.A_CV
                     ,i.A_DESC            = u.A_DESC
                     ,i.A_ENAB            = u.A_ENAB
                     ,i.A_NAME            = u.A_NAME
                     ,i.F_CV                  = u.F_CV
                     ,i.LastModified  = @date
                  FROM
                        iFixLogging.dbo.iFix_LogData i
                  INNER JOIN
                        INSERTED u
                  ON
                        --u.TagName = i.tagName and u.TagIndex = i.TagIndex
                        u.TagIndex = i.TagIndex
                        
                  --print 'inserted record'      
            END            
      end try
      begin catch
            SELECT
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState,
            ERROR_PROCEDURE() AS ErrorProcedure,
            ERROR_LINE() AS ErrorLine,
            ERROR_MESSAGE() AS ErrorMessage;
            
            RAISERROR('Error in iFixLogging Trigger' ,16,1) WITH LOG
      END CATCH
END
GO
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 37798177
I know you cannot create a trigger on a temp table, but I'm pretty sure that you can add triggers to non-temp tables in the tempdb database.

The trigger should definitely do something for INSERTs.  UPDATEs would only do something if they SET one of the tested columns (IF UPDATE(...)).

Verify that table:
 iFixLogging.dbo.iFix_LogData

exists and that the user executing the trigger has authority to INSERT and UPDATE that table at least.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:bbaley
ID: 37799178
the answer is;

The application that was writing to the table, was doing so in batch mode (adodb.UpdateBatch) - which does not fire triggers!

Thank you all for your input !
0
 

Author Comment

by:bbaley
ID: 37802741
I've requested that this question be closed as follows:

Accepted answer: 0 points for bbaley's comment #37799178

for the following reason:

The solution provided solves the problem
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 37801543
What about my earlier comment??:

"Also, certain types of statements may not fire triggers, such as BULK INSERTs.  Verify that your app isn't using that type of statement."

That was EXACTLY what it was, right?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37802743
I have re-opened this question to allow you to do the right thing.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
sql query 7 35
combine an MS SQL string in Idera DM 9 31
Log Backup 2 13
add 1 to a field for 100 rows 11 12
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now