logon trigger

carloscps · #1 (**permalink**) 02-05-10, 13:04

I'm getting the following error when creating a trigger

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/1 PL/SQL: Statement ignored
6/9 PLS-00405: subquery not allowed in this context

this is the code of the trigger:

create or replace trigger
tr_logon_xv
AFTER LOGON ON DATABASE
BEGIN
if ora_login_user<> 'USU_XV'
then
null;
else
if 1 in (select 1 from system.cadastro where host = sys_context('USERENV','HOST'))
then
null;
else
insert into SYSTEM.CADASTRO
values( sys_context('USERENV','HOST'), systimestamp);
end if;
end if;
END;
/

Thanks

anacedent · #2 (**permalink**) 02-05-10, 13:52

how is "sys_context('USERENV','HOST')" different from V$SESSION.MACHINE?

What are the requirements in words, that the trigger needs to implement?

Post Operating System (OS) name & version for DB server system.
Post results of
SELECT * from v$version

carloscps · #3 (**permalink**) 02-05-10, 14:09

I need insert in a table all hosts who connect the database with the user USU_XV

anacedent · #4 (**permalink**) 02-05-10, 14:29

modify the trigger similar to below

Code:

SQL> @CADASTRO.sql
SQL> drop table CADASTRO
  2  /

Table dropped.

SQL> create table CADASTRO(machine varchar2(30),logon_time date)
  2  /

Table created.

SQL> insert into CADASTRO values('FOOBAR',sysdate);

1 row created.

SQL> insert into CADASTRO values('BARFOO',sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> DECLARE
  2  CNT NUMBER;
  3  BEGIN
  4  SELECT COUNT(*) INTO CNT FROM CADASTRO WHERE MACHINE = 'bcm-laptop';
  5  if cnt = 0
  6  then
  7  	insert into CADASTRO values('bcm-laptop',sysdate);
  8  	commit;
  9  end if;
 10  end;
 11  /

PL/SQL procedure successfully completed.

The_Duck · #5 (**permalink**) 02-09-10, 11:44

or:

PHP Code:


			
CREATE OR REPLACE TRIGGER audit_logon_trigger
AFTER LOGON ON DATABASE
BEGIN

IF
   SYS_CONTEXT('USERENV','SESSION_USER') = 'USU_XV'
THEN

-- insert data into an audit-table
-- table must be created first
INSERT INTO SYSTEM.CADASTRO VALUES(
user,
sys_context('USERENV','SESSIONID'),
sys_context('USERENV','HOST'),
sysdate);

END IF;
END;
/

Internet Services

Web Development

Digital Marketing

Consumer Tech