| Author |
Topic |
|
inibhreaslain
Starting Member
19 Posts |
 Posted - 2009-01-29 : 17:38:18
|
Hi all,I have a trigger on a table for insert or update. Its been working fine for 8 months. It stopped triggering two days ago. I can successfully make it trigger by entering data using SQL Server Management Studio. Data is generally entered using an application. The application has not been changed, and the data is still being entered into the table.CREATE TRIGGER [dbo].[FUPCall] ON [dbo].[QT_Status] FOR INSERT, UPDATEASBEGIN EXEC msdb.dbo.sp_start_job @job_name=My_Job_From_Trigger';END |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-30 : 01:45:32
|
| also are you sure that application is not doing bulk insert in which case trigger wont be called unless you have enable_triggers as true |
 |
|
|
inibhreaslain
Starting Member
19 Posts |
Posted - 2009-01-30 : 09:14:44
|
| The job itself runs is fine - it runs when I enter a record through Management studio. (The quote is a cut/paste problem)The application sometimes enters two records, but mostly one and it is not working for either. Any other ideas where to look? Ciould it be a permissions issue? Anything entering data should cause the trigger - right? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-01-30 : 09:16:53
|
How often are you inserting records in the table?Same job can't be started again unless previous run is finished.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-30 : 09:20:41
|
| what does job My_Job_From_Trigger consists of? |
|
|
|
inibhreaslain
Starting Member
19 Posts |
Posted - 2009-01-30 : 09:46:50
|
| Peso - They CAN be inserted very close together, but generally every few minutes. The job only takes 5 seconds. For my purposes, it does not matter if it does not trigger EVERY time, but it is not triggering at all on application-entered records.visakh16 - the job is a 3-step process and takes ~5 seconds. 1) It reads from the table the record was inserted into and another table.2) A vbscript takes this data and writes to a file.3) A Third table gets updated with a timestamp. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-30 : 10:03:51
|
| how is application doing inserts? by means of sp or just inline code? |
|
|
|
inibhreaslain
Starting Member
19 Posts |
Posted - 2009-01-30 : 10:07:58
|
| Just inline code. Its an internal app. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-30 : 10:11:19
|
| and is it simple insert statement? |
|
|
|
inibhreaslain
Starting Member
19 Posts |
Posted - 2009-01-30 : 10:16:56
|
Here's the insert:strInsert = "INSERT INTO QT_Status (QtID, Status, StatusSubmitDateTime, StatusUpdatedBy, StatusNotes) VALUES (";strInsert += QtIDs[i] + ", 'Submitted', '" + DateTime.Now + "', '" + UserID + "', 'na')";The reason for the array is that there SOMETIMES are 2 records to enter. It's in a loop that usually only gets executed once. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-30 : 10:20:21
|
| can you capture with profiler trace the insert statement it tries to execute from app while inserting? |
|
|
|
inibhreaslain
Starting Member
19 Posts |
Posted - 2009-01-30 : 10:53:59
|
| I can't really do that. I'm a software developer who's been asked to figure this out. Not really the way things should work. Kacj of all trades etc... |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-01-30 : 11:08:10
|
| Have you confirmed that the "application entered records" are actually making it into [QT_Status]?It could be a permission issue. Try connecting to sql as the same account that the application runs under and see if you see the same behavior. You may just need to make the owner of the job the same account that the app runs as.Be One with the OptimizerTG |
|
|
|
inibhreaslain
Starting Member
19 Posts |
Posted - 2009-01-30 : 11:13:27
|
TG - I did what you said and you're right. When I try to insert a new record I get the error:The specified @job_name ('My_Job_From_Trigger') does not exist.So - the OWNER has to be the same? Or can I give permissions to the account that the app uses for that job? |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-01-30 : 11:29:30
|
| You could run your trigger with the EXECUTE AS clause. This would mean that it would run under any account. |
|
|
|
|
Trigger stopped working