Continue processing after Error Handling in IF statement

[cdutta]

Hi everybody,

I have a logon trigger where I have an IF statement that
contains a condition where I have an error that's being raised, and some other statement that I need to process. By raising the error the control is passed outside the inner block and the statement following the raise application error stmt is not executed... Can anyone tell me how can I execute the statement after the error handler?

Here is the code for the trigger:

create or replace trigger logon_terminal_try2
after logon
on database
DECLARE
v_username varchar2(30);
v_terminal varchar2(16);
v_terminal1 varchar2(16);
v_sid number;
login_notallowed EXCEPTION;
CURSOR USER_CUR is select terminal
from sys.v$session
where username = v_username
and
user# = v_sid ;
BEGIN
v_username := user;
v_sid := uid;
OPEN USER_CUR;
v_username := user;
v_sid := uid;
select terminalname into v_terminal1
from cris.terminal_compare
where
USERNAME = v_username;

FETCH USER_CUR into v_terminal;
BEGIN
BEGIN
IF
v_terminal= v_terminal1
THEN
insert into cris.LOG_INFO_TBL values (sysdate, v_username, 'LOGONOK');
ELSE
raise login_notallowed;
insert into cris.log_info_tbl values(sysdate, v_username, 'LOGONOIMP');

END IF;
END;
v_username := user;
v_sid := uid;

EXCEPTION
when login_notallowed then raise_application_error(-20001, 'YOU ARE NOT ALLOWED TO LOG IN!!!');
END;
CLOSE USER_CUR;
END;


Thanks,
CD

[Dave_A]

It looks like there will be 2 statements that will not get executed - the insert of the logonnoimp message, and the close of the user_cursor.

Seems to me that the best bet in this case would be to simply log the message and close the cursor before raising the application error. (note, in the code fragment below, I have 'borrowed' Chrisrlong's indent method - please replace '---' with an appropriate tab)

As in..
EXCEPTION
---when login_notallowed then
---BEGIN
------insert into cris.log_info_tbl values(sysdate, v_username, 'LOGONOIMP');
------CLOSE USER_CUR;
------raise_application_error(-20001, 'YOU ARE NOT ALLOWED TO LOG IN!!!');
---END;
END;


[Edited by Dave_A on 06-04-2001 at 10:56 AM]

[cdutta]

I tried to handle the exception as suggested by it still does not work. The 2 statements prior to raise application error
still do not get to be executed...

This is the modified code:

create or replace trigger logon_terminal_try3
after logon
on database
DECLARE
v_username varchar2(30);
v_terminal varchar2(16);
v_terminal1 varchar2(16);
v_sid number;
login_notallowed EXCEPTION;
--PRAGMA EXCEPTION_INIT(login_notallowed, -20001);
CURSOR USER_CUR is select terminal
from sys.v$session
where username = v_username
and
user# = v_sid ;
BEGIN
v_username := user;
v_sid := uid;
OPEN USER_CUR;
v_username := user;
v_sid := uid;
select terminalname into v_terminal1
from cris.terminal_compare
where
USERNAME = v_username;

FETCH USER_CUR into v_terminal;
BEGIN

IF
v_terminal= v_terminal1
THEN
insert into cris.LOG_INFO_TBL values (sysdate, v_username, 'LOGONOK');
ELSE
raise login_notallowed;
END IF;
EXCEPTION
when login_notallowed then
BEGIN
insert into cris.log_info_tbl values(sysdate, v_username, 'LOGONOIMP');
CLOSE USER_CUR;
raise_application_error(-20001, 'YOU ARE NOT ALLOWED TO LOG IN!!!');
END;
END;
--CLOSE USER_CUR;
v_username := user;
v_sid := uid;
END;

Any ideas?

Thanks,
CD

[Heath]

Shooting in the dark here.....

But how exactly are you checking to see if the

    insert into cris.log_info_tbl values(sysdate, v_username, 'LOGONOIMP');
    CLOSE USER_CUR;

statements get executed or not? If it's from a different database session, try issuing a commit.

[cdutta]

every time an user log in a row is inserted in one
table cris.log_info_tbl. I do not see the point of commiting.
There should be a way.. to continue execution of the statements after error handling... Please help.
Thanks CD.

[Heath]

Generally, you use commits so that other database sessions can see the work that you have performed. It's how you make changes to the database permanent. I'm sure you already knew that.

I guess I was wondering if your application made sure that these rows that you are inserting actually stay in the cris.log_info_tbl. You can't commit from within a trigger so, if you don't issue a commit from the code that caused the trigger to fire, another database session is not going to see the rows in cris.log_info_tbl. The code in your trigger will execute, but you may not be able to tell since without a commit, nobody else is ever going to see the row inserted into cris.log_info_tbl by your logon trigger. If your basis for stating that the code isn't executing is that you don't see the row in the cris.log_info_tbl, then I am just asking you to verify that a commit gets executed by your application. It may be detecting the exception raised by the trigger and not performing a commit. I can't really say because I don't know your application.

I can't see any reason in your code why those lines are not getting executed. That's why I am suggesting you investigate how you are checking to see if they execute.

I hope this is more helpful,

Heath

[Dave_A]

I have to agree with Heath's posting. I was originally going to comment on the lack of a commit, but assumed you were aware of it. Oops. Assumptions are dangerous things...

[cdutta]

First I have to give an overview of what I need this trigger do
do.

I have the following 2 tables:
cris.log_info_tbl
Table where trigger logs on successful and unsuccessful attempts to log on the database.

Name Null? Type
----------------------------------------- -------- ------------------------
LOGIN_DATE DATE
USERNAME VARCHAR2(25)
EVENT VARCHAR2(10)

cris.terminal_compare
holds the username and the terminal where the user usually logs on from:
eg. CRIS should only log in from PIC-DELL1 terminal.

USERNAME VARCHAR2(20)
TERMINALNAME VARCHA


When I test the trigger by logging in from a legitimate terminal eg: User CRIS only logs in from PIC-DELL1 terminal trigger should
insert a row in cris.LOG_INFO_TBL that the user logged in ok. If Other user KEVINS tries to log in from same terminal PIC-DELL1 ( which is not legitimate for that user)
then trigger should insert a row in cris.log_info_tbl to signal user impersonation. My problem was that the
insert into cris.log_info_tbl values(sysdate, v_username, 'LOGONOIMP');
CLOSE USER_CUR; did not get executed... but error login_notallowed error gets executed and kevins is not allowed to log in.

I added a commit after the insert in the second part of
the if statement. It seems that trigger is working now
and a row gets inserted to signal who tried impersonation...



create or replace trigger logon_terminal_try4
after logon
on database
DECLARE
v_username varchar2(30);
v_terminal varchar2(16);
v_terminal1 varchar2(16);
v_sid number;
login_notallowed EXCEPTION;
PRAGMA EXCEPTION_INIT(login_notallowed, -20001);
CURSOR USER_CUR is select terminal
from sys.v$session
where username = v_username
and
user# = v_sid ;
BEGIN
v_username := user;
v_sid := uid;
OPEN USER_CUR;
v_username := user;
v_sid := uid;
select terminalname into v_terminal1
from cris.terminal_compare
where
USERNAME = v_username;

FETCH USER_CUR into v_terminal;
BEGIN

IF
v_terminal= v_terminal1
THEN
insert into cris.LOG_INFO_TBL values (sysdate, v_username, 'LOGONOK');
ELSE
insert into cris.log_info_tbl values(sysdate, v_username, 'LOGONOIMP');
commit;
raise login_notallowed;
END IF;
EXCEPTION
when login_notallowed then
raise_application_error(-20001, 'YOU ARE NOT ALLOWED TO LOG IN!!!');

END;
-- END;
v_username := user;
v_sid := uid;
CLOSE USER_CUR;
END;

Still I do not yet see the logical explanation for this working... Especially when I added commit inside the trigger when I thought that I should not have been allowed to do so.
Maybe I am still missig something...
Thanks for your help.

CD