Hi, Group
I have a inserting trigger on my table. The insert
operation failed if the trigger failed. But in my
Java client I have never catched the SQL exception (caused
by the insert statement). The error is just
disregarded silently. To make sure I did it right, I
also did a
test on a store procedure which does
exactly same thing as the trigger, I can capture the
exception caused by the stored procedure. If I execute
the insert statement and the stored procedure in
SQL/PLUS, both were failed and shown me the exception.
My question is : 1. is the insert statement be
considered as fail if the trigger is failed? If, yes,
how can I capture this exception?
2. Understand that you always need to add exception
handling in your stored procedure or trigger, but even
if I did not add exception section in my stored
procedure, the exception also be catched by my java
program. This is confused me.
Following is my trigger and procedure
------------------------------------------
Create or replace procedure TErr as
v_test number;
BEGIN
select a.a from a where 1=2; --cause NO_DATA_FOUND
exception
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO_RECORD_FOUND_EXCEPTION:'||sqlerrm);
RAISE;
END;
------------------------------------------
------------------------------------------
CREATE or REPLACE TRIGGER TErr2
AFTER INSERT ON b
FOR EACH ROW
DECLARE v_test number;
BEGIN
select a.a from a where 1=2; --cause NO_DATA_FOUND
exception
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO_RECORD_FOUND_EXCEPTION:'||sqlerrm);
RAISE;
END;
------------------------------------------
my Java code:
------------------------------------------
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.CallableStatement;
Connection conn;
//Create Conn
try {
PreparedStatement preparedStmt =
conn.prepareStatement("insert into b(b) values(1);");
preparedStmt.execute();
preparedStmt.close();
CallableStatement pstmt = conn.prepareCall("{call
\"TErr\"()}");
pstmt.execute();
pstmt.close();
}
catch (SQLException sqle) {
//process exception
}
------------------------------------------
Anybody can help? Thanks a lot
-Reico
