asked on

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.

Anthony Perkins

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.

mccarl

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.

chaitu chaitu

ASKER

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

ASKER CERTIFIED SOLUTION

mccarl

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

So if all INSERT statements are single INSERTs like you indicate then the code in your TRIGGER is not the problem.

chaitu chaitu

ASKER

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