The trigger is working but when you raise the application error the
insert gets rolled back.
The only way we were able to accomplish the logging of a failed login
was to use the
declare
pragma autonomous_transaction;
begin
This way the insert gets committed when you raise the application error
exception
Hope this helps
Mike
Thanks for all
the replies. My
initial attempts used an AFTER LOGON ON SCHEMA, not DATABASE, trigger.
However, I'm now stymied in
a
simple test case: the following SYS trigger is firing, but no error
appears on login.
create or replace
trigger
verify_client
after logon on database when (user='PB')
begin
insert into pb.foo values('Test');
commit;
raise_application_error(-20999,'Not authorized');
end;
/
Here's what happens:
SQL> @conn pb/pb
Connected.
PB@CSAR.REGRESS.COM> select * from foo;
BAR
--------------------
Test
PB@CSAR.REGRESS.COM>
@conn pb/pb
Connected.
PB@CSAR.REGRESS.COM> select * from foo;
BAR
--------------------
Test
Test
As you can probably guess, I
added
the "insert into pb.foo" to the trigger to verify that it is
firing.
What's wrong here?
Thanks
Paul Baumgartel
CREDIT SUISSE
Information Technology
DBA & Admin - NY, KIGA 1
11 Madison Avenue
New York, NY 10010
USA
Phone 212.538.1143
paul.baumgartel@credit-suisse.com
www.credit-suisse.com
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Sep 13 2006 - 20:24:51 CDT
