Oracle 系统触发器不保存一些信息

大家好。

我有一个简单的触发系统的问题。

这是我的代码

CREATE TABLE COLLEGATO(
ID_SESSIONE VARCHAR(30),
NOME_UTENTE VARCHAR(30),
IDENTIFICAZIONE VARCHAR(30),
INDIRIZZO_IP VARCHAR(30),
HOST_MACC VARCHAR(40),
TERRITORIO VARCHAR(30)
);

CREATE OR REPLACE TRIGGER SONO_COLLEGATO
AFTER LOGON ON DATABASE
DECLARE
ID_SES VARCHAR(30);
NOME_UT VARCHAR(30);
IDEN VARCHAR(30);
INDI_IP VARCHAR(30);
HOST_M VARCHAR(40);
TERR VARCHAR(30);
BEGIN

SELECT SYS_CONTEXT('USERENV','SESSION_USER') INTO NOME_UT FROM DUAL;
SELECT SYS_CONTEXT('USERENV','SESSION_ID') INTO ID_SES FROM DUAL;
SELECT SYS_CONTEXT('USERENV','IDENTIFICATION_TYPE') INTO IDEN FROM DUAL;
SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') INTO INDI_IP FROM DUAL;
SELECT SYS_CONTEXT('USERENV','HOST') INTO HOST_M FROM DUAL;
SELECT SYS_CONTEXT('USERENV','NLS_TERRITORY') INTO TERR FROM DUAL;
  
INSERT INTO CATENA_DI_NEGOZI.COLLEGATO(ID_SESSIONE,NOME_UTENTE,IDENTIFICAZIONE,INDIRIZZO_IP,HOST_MACC,TERRITORIO) VALUES(ID_SES,NOME_UT,IDEN,INDI_IP,HOST_M,TERR);

EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20001,'Non sono stati trovati dei dati');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Inserimento dati non avvenuto con successo dopo il collegamento');
END;
/

从代码中可以看到，我的系统触发器只需要保存来自数据库上连接的用户的信息。

我的问题是这个。当我光盘和以后我与同一个用户或其他用户连接时，我没有得到表中的任何行。

有人能帮我吗？

原因 #79您应该始终在 “当其他人” 之后加注 (或完全排除此子句): 此触发器出了点问题，但您看不到什么!

删除此 (或添加raise语句):

CREATE TABLE COLLEGATO(
ID_SESSIONE VARCHAR(30),
NOME_UTENTE VARCHAR(30),
IDENTIFICAZIONE VARCHAR(30),
INDIRIZZO_IP VARCHAR(30),
HOST_MACC VARCHAR(40),
TERRITORIO VARCHAR(30)
);

CREATE OR REPLACE TRIGGER SONO_COLLEGATO
AFTER LOGON ON DATABASE
DECLARE
ID_SES VARCHAR(30);
NOME_UT VARCHAR(30);
IDEN VARCHAR(30);
INDI_IP VARCHAR(30);
HOST_M VARCHAR(40);
TERR VARCHAR(30);
BEGIN

SELECT SYS_CONTEXT('USERENV','SESSION_USER') INTO NOME_UT FROM DUAL;
SELECT SYS_CONTEXT('USERENV','SESSION_ID') INTO ID_SES FROM DUAL;
SELECT SYS_CONTEXT('USERENV','IDENTIFICATION_TYPE') INTO IDEN FROM DUAL;
SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') INTO INDI_IP FROM DUAL;
SELECT SYS_CONTEXT('USERENV','HOST') INTO HOST_M FROM DUAL;
SELECT SYS_CONTEXT('USERENV','NLS_TERRITORY') INTO TERR FROM DUAL;
  
INSERT INTO CHRIS.COLLEGATO(ID_SESSIONE,NOME_UTENTE,IDENTIFICAZIONE,INDIRIZZO_IP,HOST_MACC,TERRITORIO) VALUES(ID_SES,NOME_UT,IDEN,INDI_IP,HOST_M,TERR);

EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20001,'Non sono stati trovati dei dati');
END;
/

然后，当你尝试登录时，你会看到类似的东西:

SQL> grant create session to u identified by u;

Grant succeeded.

SQL> conn u/u
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-02003: invalid USERENV parameter
ORA-06512: at line 11


Warning: You are no longer connected to ORACLE.

SESSION_ID不是有效的USERENV选项。您需要使用SESSIONID。或者使用标准数据库审计来捕获登录!

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_4007.htm#SQLRF01107