which tomcat logs will be captured when DB trigger RAISE error

ALTER TRIGGER [dbo].[EMP_Trigger] ON [XXXX].[dbo].[EMPJOBS]
FOR INSERT
AS
BEGIN


SELECT @new_job_id = JOB_ID from INSERTED


 BEGIN  
    SELECT @COUNT2 = COUNT(*) FROM EMPJOBS EJ JOIN INSERTED i
        ON bj.EMP_TYPE= i.EMP_TYPE
        AND EJ.DOC_ID= i.DOC_ID
        AND EJ.EMP_DATE >= DATEADD(mi, -1, GETDATE())
        AND EJ .EMP_ID <> i.EMP_ID       
         END

 IF @COUNT2 > 0
  BEGIN
    PRINT 'JOB ALREADY RUNNING.'
    RAISERROR ('JOB ALREADY RUNNING. JOB_ID=%d', 16, 1, @new_job_id)
    ROLLBACK TRANSACTION
    RETURN
  END
 ELSE
  BEGIN    
    PRINT 'NEW JOB IS CREATED.  JOB_ID=%d'
  END
  
END

Open in new window


above trigger is written if the same emp type and doc id has been inserted within the last
minute then the exception will be raised and  the transaction will be rolled back.

insert into empjobs(emp_type,doc_id,emp_date)
values('SCAN','123',GETDATE())

insert into empjobs(emp_type,doc_id,emp_date)
values('SCAN','123',GETDATE())

Open in new window


for example if i execute both insert statements at the same time in DB studio console then the above trigger will be fired and transaction will be rolled back and this message will be printed('JOB ALREADY RUNNING.')


but the problem is our web application is runnning  in tomcat ,we are not able to see the above error in the logs if the multiple web services execute it in the same time.

in which logs can i see this error if this exception occurs.
LVL 20
chaitu chaituAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
mccarlConnect With a Mentor IT Business Systems Analyst / Software DeveloperCommented:
Ok, so assuming your "LOG" variable (line 10 of the code above) is initialised correctly, then yes you should see the exception raised being output to the "c:/logs/temp.log" file, and it should contain the "JOB ALREADY RUNNING" text from the trigger.

If you have looked here and aren't getting what you expect, then check a few things...

Are you not getting any file at all in C:\Logs? Is the directory already there? Are the permissions set correctly for the user that Tomcat is running as to write in that directory?

Is there a log file already there, but you aren't getting any new messages written to it? Check the log file's permission that it can be updated/appended by Tomcat.

Are you getting any other messages written to this log file?

Try debugging this by putting extra LOG.info("Insert succeeded"); lines in (in between lines 6 & 9) and seeing if you do actually get log messages in that file. This should tell you for sure whether you are getting log messages properly but the code just isn't throwing an exception.
0
 
Anthony PerkinsCommented:
The problem is that your TRIGGER is not coded correctly.  You are making the incorrect assumption that a TRIGGER is fired once per row, when in fact it is executed once per statement.  So if you have a case where a single INSERT adds more than one row, as written it will only see one row.
0
 
mccarlIT Business Systems Analyst / Software DeveloperCommented:
which tomcat logs will be captured when DB trigger RAISE error
That depends totally, entirely, 100% on the web application itself. Possibly, the web app is just ignoring the resulting exception and it ISN'T going to ANY log file.

You would (for a start) need to post the code that attempts the DB operations (inserts, I assume) that may cause this error. Also, you would need to post the web apps logging configuration, etc.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
chaitu chaituAuthor Commented:
acperkins,

Please let me explain what is the difference between executed once per statement or executed once per row.

insert into empjobs(emp_type,doc_id,emp_date)
values('SCAN','123',GETDATE())

insert into empjobs(emp_type,doc_id,emp_date)
values('SCAN','123',GETDATE())

as i explained in my previous statement if i execute above insert statements at one time in DB console only one row will be committed other row will be rolled back that i can see the error in the console.you said TRIGGER is not coded correctly,may i know what is the problem with the above trigger?can you please pinpoint that problem?





mccarl,

but when multiple web services schedulers call below method at the same time then not able to see the TRIGGER errors in the tomcat logs.

private void insertEmpJob() throws  SQLException {

pstmt= conn.prepareStatement("inser empjobs query");
					pstmt.setLong(1, storageId);

					pstmt.executeUpdate();


} catch (Exception e) {
			LOG.error("exception in insertEmpJob:"+e.getMessage());
			throw new XXXException(
					"exception in insertEmpJob:", e);
		}

Open in new window


log4j.xml
********

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd" >
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">

	<!-- Appenders -->
	<appender name="FILE" class="org.apache.log4j.DailyRollingFileAppender">
		<param name="file" value="c:/logs/temp.log" />
		<param name="DatePattern" value="'.'yyyy-MM-dd" />
		<param name="Append" value="true" />
		<layout class="org.apache.log4j.PatternLayout">
			<param name="ConversionPattern" value="[%t] %d{HH:mm:ss,SSS} %-5p %l - %m%n" />
		</layout>
	</appender>

	<!-- Application Loggers -->
	<logger name="com.temp.ws">
		<level value="info" />
	</logger>

	

	<logger name="org.hibernate.tool.hbm2ddl" additivity="false">
		<level value="info" />
		<appender-ref ref="FILE" />
	</logger>

	

	<!-- Root Logger -->
	<root>
		<priority value="info" />
		<appender-ref ref="FILE" />
	</root>

</log4j:configuration>

Open in new window

0
 
Anthony PerkinsCommented:
So if all INSERT statements are single INSERTs like you indicate then the code in your TRIGGER is not the problem.
0
 
chaitu chaituAuthor Commented:
mcarrl,

I wrote below simple trigger to check whether log file is capturing below exception.its indeed capturing the log message in the file.I think may be there is some problem  in the original trigger thats why its not capturing the log message.

can we capture PRINT  log messages in our log file..i want to put some debug or informational statements in trigger so that i can findout the exact problem.



ALTER trigger [dbo].[TEMP_TRIGGER]
    on [dbo].[TEMP_TABLE] for insert
  AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
   BEGIN
    PRINT 'TEMP_TABLE RUNNING..........................'
    RAISERROR (TEMP_TABLERUNNING.............. JOB_ID=', 16, 1)
    ROLLBACK TRANSACTION
    RETURN
  END

END
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.