Path: news.easynews.com!easynews!newshosting.com!news-xfer2.atl.newshosting.com!news.stealth.net!news.stealth.net!207.8.186.117.MISMATCH!grr!newsprint.netaxs.com!POSTED.newshog.newsread.com!not-for-mail From: "Neal Helman" Newsgroups: comp.databases.oracle.server Subject: triggers, db jobs, & raising errors Lines: 25 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Original-NNTP-Posting-Host: 204.144.244.24 Message-ID: <3d52e59c$1@news.peakpeak.com> X-Original-Trace: 8 Aug 2002 15:41:48 -0600, 204.144.244.24 Date: Thu, 08 Aug 2002 21:22:23 GMT NNTP-Posting-Host: 207.174.178.27 X-Complaints-To: Abuse Role , We Care X-Trace: newshog.newsread.com 1028841743 207.174.178.27 (Thu, 08 Aug 2002 17:22:23 EDT) NNTP-Posting-Date: Thu, 08 Aug 2002 17:22:23 EDT Organization: Peak to Peak Internet (peakpeak.com) Xref: easynews comp.databases.oracle.server:157304 X-Received-Date: Thu, 08 Aug 2002 14:19:28 MST (news.easynews.com) I'm using Oracle 7.3.4. I have a BEFORE UPDATE trigger that runs a stored procedure, then submits a database job to run another stored procedure as the process that submitted the UPDATE continues. The job is successfully submitted, but breaks. No notification or error was raised indicating that the stored procedure failed. When I manually execute the stored procedure executed by the job, it finishes without errors. When I manually submit an appropriate UPDATE statement, the trigger fires, the job is submitted, and the stored procedure finishes without errors. I've used this trigger/db job/stored procedure scheme successfully with the UPDATE being issued by yet another stored procedure. Typically, when the procedure executed by the job fails, the error is raised up to the procedure that issued the UPDATE, and the top-level procedure then reports an error. I'm trying to figure out why the top-level procedure may or MAY NOT receive the error procedure called by the job. When I look at the ALL_JOBS view, it appears that the context in which the job is running is the owner of the triggered table. What's confusing is that this is the same schema as the top-level stored procedure. I realize that the job is probably running under a background process, but isn't there some sort of connection between the calling connection and that background process? How is it that broken jobs sometimes appear to raise errors, other times not? Thanks, Neal