Solved

which tomcat logs will be captured when DB trigger RAISE error

Posted on 2013-12-21
6
297 Views
Last Modified: 2014-01-10
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.
0
Comment
Question by:chaitu chaitu
  • 2
  • 2
  • 2
6 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39733752
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
 
LVL 35

Expert Comment

by:mccarl
ID: 39734184
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
 
LVL 20

Author Comment

by:chaitu chaitu
ID: 39735339
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 35

Accepted Solution

by:
mccarl earned 500 total points
ID: 39735374
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39737242
So if all INSERT statements are single INSERTs like you indicate then the code in your TRIGGER is not the problem.
0
 
LVL 20

Author Comment

by:chaitu chaitu
ID: 39737827
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

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In this article I will describe the Detach & Attach 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.
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.

757 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

22 Experts available now in Live!

Get 1:1 Help Now