CREATE TRIGGER TRG_Animaux
BEFORE INSERT OR UPDATE ON Animaux
FOR EACH ROW
BEGIN 
    IF INSERTING THEN 
        IF :new.Ani_util_creer IS NULL THEN 
            :new.Ani_util_creer := User;
        END IF;

        IF :new.ani_date_creer IS NULL THEN 
            :new.ani_date_creer := TO_DATE(SYSDATE);
        END IF;
    END IF; 

    IF UPDATING THEN 
       :new.Ani_util_modif := User;
       :new.ani_date_modif := TO_DATE(SYSDATE);
      END IF; 
END; 
/

create table audit_connection (
connection_DATEON         date,
connection_DATEOFF        date,
connection_TIME           Number,
connection_user           varchar2(30),
connection_osuser         varchar2(30),
connection_machine        varchar2(64),
connection_terminal       varchar2(16),
connection_PROTOCOL       varchar2(30),
connection_ADDRESS        varchar2(30),
connection_program        varchar2(64),
connection_MODULE         varchar2(48),
connection_NB             Number,
connection_SID_SERIAL#    varchar2(30))
TABLESPACE XXXXXXXX;

CREATE OR REPLACE PROCEDURE SYS.P_CONNECTIONON
IS
  N_NUMBER  NUMBER(10);
  V_MACHINE1 VARCHAR2(50);
  V_MACHINE2 VARCHAR2(50);
  v_program1 Varchar2(50);
  v_program2 Varchar2(50);

  FOUND BOOLEAN;
  V_LBPARAM   VARCHAR2(1024);


  V_CONNECTION_SID_SERIAL#     AUDIT_CONNECTION.CONNECTION_SID_SERIAL#%TYPE;
  V_CONNECTION_USER      AUDIT_CONNECTION.CONNECTION_USER%TYPE;
  V_CONNECTION_OSUSER    AUDIT_CONNECTION.CONNECTION_OSUSER%TYPE;
  V_CONNECTION_MACHINE   AUDIT_CONNECTION.CONNECTION_MACHINE%TYPE;
  V_CONNECTION_TERMINAL  AUDIT_CONNECTION.CONNECTION_TERMINAL%TYPE;
  V_CONNECTION_PROTOCOL  AUDIT_CONNECTION.CONNECTION_PROTOCOL%TYPE;
  V_CONNECTION_ADDRESS   AUDIT_CONNECTION.CONNECTION_ADDRESS%TYPE;
  V_CONNECTION_PROGRAM   AUDIT_CONNECTION.CONNECTION_PROGRAM%TYPE;
  V_CONNECTION_PROGRAM2   AUDIT_CONNECTION.CONNECTION_PROGRAM%TYPE;
  V_CONNECTION_MODULE    AUDIT_CONNECTION.CONNECTION_MODULE%TYPE;
  V_CONNECTION_SID_SERIAL#2    AUDIT_CONNECTION.CONNECTION_SID_SERIAL#%TYPE;

  CURSOR C1(VAR_MACHINE VARCHAR2) IS
    SELECT DISTINCT (MACHINE) 
    FROM V$SESSION 
    WHERE  UPPER(USERNAME) = UPPER(USER) AND 
           MACHINE  <> VAR_MACHINE;

  CURSOR C12(VAR_MACHINE VARCHAR2) IS
    SELECT DISTINCT MACHINE, UPPER(PROGRAM) 
    FROM V$SESSION 
    WHERE  UPPER(USERNAME) = UPPER(USER) AND 
           ( MACHINE <> VAR_MACHINE and UPPER(PROGRAM) = V_CONNECTION_PROGRAM );

  CURSOR C2 IS
    SELECT TO_CHAR(SID)||':'||TO_CHAR(SERIAL#),
           UPPER(SYS_CONTEXT('USERENV','SESSION_USER')),
           UPPER(SYS_CONTEXT('USERENV','OS_USER')), 
           UPPER(SYS_CONTEXT('USERENV','HOST')), 
           UPPER(SYS_CONTEXT('USERENV','TERMINAL')),
           UPPER(SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')),
           UPPER(SYS_CONTEXT('USERENV','IP_ADDRESS')), 
           UPPER(PROGRAM), LTRIM(MODULE)
    FROM   V$SESSION 
    WHERE  UPPER(USERNAME) = UPPER(USER) AND 
           AUDSID = SYS_CONTEXT('USERENV','SESSIONID') AND 
           AUDSID > 0 ;

  CURSOR C3 IS
    SELECT CONNECTION_SID_SERIAL#
    FROM   PL.AUDIT_CONNECTION
    WHERE  CONNECTION_SID_SERIAL# <> '£' AND 
           CONNECTION_SID_SERIAL# NOT IN 
            ( SELECT TO_CHAR(SID)||':'||TO_CHAR(SERIAL#)
              FROM V$SESSION);

  CURSOR C4 IS
    SELECT NVL(CONNECTION_SID_SERIAL#,'£')
    FROM   AUDIT_CONNECTION 
    WHERE  CONNECTION_DATEON  <= SYSDATE AND 
           TO_CHAR(CONNECTION_DATEON,'DD/MM/YYYY') = TO_CHAR(SYSDATE,'DD/MM/YYYY') AND
           CONNECTION_USER          = V_CONNECTION_USER     AND 
           CONNECTION_OSUSER        = V_CONNECTION_OSUSER   AND 
           CONNECTION_MACHINE       = V_CONNECTION_MACHINE  AND 
           CONNECTION_TERMINAL      = V_CONNECTION_TERMINAL AND 
           CONNECTION_PROTOCOL      = V_CONNECTION_PROTOCOL AND 
           CONNECTION_ADDRESS       = V_CONNECTION_ADDRESS  AND 
           CONNECTION_PROGRAM       = V_CONNECTION_PROGRAM  ;

--  Cursor C5(var_PROGRAM Varchar2) is
--  Select Distinct PROGRAM
--  from v$session 
--  where upper(username) = upper(user) and 
--        upper(PROGRAM)  <> upper(var_PROGRAM) and 
--        PROGRAM is not null;
        
BEGIN

  OPEN C3;
  FETCH C3 INTO V_CONNECTION_SID_SERIAL#;
  FOUND := C3%FOUND;
  WHILE FOUND LOOP
        UPDATE PL.AUDIT_CONNECTION
        SET   CONNECTION_SID_SERIAL#= NULL,
              CONNECTION_DATEOFF = SYSDATE,
              CONNECTION_NB = NVL(CONNECTION_NB,0) + 1,
              CONNECTION_TIME = NVL(CONNECTION_TIME,0) + (SYSDATE - CONNECTION_DATEON)*24*60*60
        WHERE CONNECTION_SID_SERIAL#= V_CONNECTION_SID_SERIAL#;
    FETCH C3 INTO V_CONNECTION_SID_SERIAL#;
    FOUND := C3%FOUND;
  END LOOP;
  CLOSE C3;

  OPEN C2;
  FETCH C2 INTO V_CONNECTION_SID_SERIAL#, V_CONNECTION_USER, V_CONNECTION_OSUSER, 
                V_CONNECTION_MACHINE, V_CONNECTION_TERMINAL, V_CONNECTION_PROTOCOL,
                V_CONNECTION_ADDRESS, V_CONNECTION_PROGRAM,V_CONNECTION_MODULE;
  FOUND := C2%FOUND;
  CLOSE C2;
  IF FOUND THEN 
    OPEN C4;
    FETCH C4 INTO V_CONNECTION_SID_SERIAL#2;
    FOUND := C4%FOUND;
    CLOSE C4;
    IF NOT FOUND THEN 
      INSERT INTO PL.AUDIT_CONNECTION
      ( CONNECTION_SID_SERIAL#,CONNECTION_DATEON,CONNECTION_USER, CONNECTION_OSUSER,CONNECTION_MACHINE, CONNECTION_TERMINAL,
        CONNECTION_PROTOCOL, CONNECTION_ADDRESS, CONNECTION_PROGRAM, CONNECTION_MODULE)
      VALUES
      ( V_CONNECTION_SID_SERIAL#,SYSDATE, V_CONNECTION_USER,V_CONNECTION_OSUSER,V_CONNECTION_MACHINE, V_CONNECTION_TERMINAL,
        V_CONNECTION_PROTOCOL, V_CONNECTION_ADDRESS, V_CONNECTION_PROGRAM, V_CONNECTION_MODULE);
    ELSE
      IF V_CONNECTION_SID_SERIAL#2 ='£' THEN 
        UPDATE AUDIT_CONNECTION
        SET    CONNECTION_SID_SERIAL# = V_CONNECTION_SID_SERIAL#
        WHERE  TO_CHAR(CONNECTION_DATEON,'DD/MM/YYYY') = TO_CHAR(SYSDATE,'DD/MM/YYYY') AND
               CONNECTION_USER          = V_CONNECTION_USER     AND 
               CONNECTION_OSUSER        = V_CONNECTION_OSUSER   AND 
               CONNECTION_MACHINE       = V_CONNECTION_MACHINE  AND 
               CONNECTION_TERMINAL      = V_CONNECTION_TERMINAL AND 
               CONNECTION_PROTOCOL      = V_CONNECTION_PROTOCOL AND 
               CONNECTION_ADDRESS       = V_CONNECTION_ADDRESS  AND 
               CONNECTION_PROGRAM       = V_CONNECTION_PROGRAM  ;
      END IF;
    END IF; 
  END IF;

  SELECT  COUNT (1)
  INTO  N_NUMBER
  FROM  G_USER
  WHERE UPPER(USERNAME) = UPPER(USER) ;
  IF N_NUMBER > 0 Then 
    IF USER <> 'ADMINISTRATEUR' THEN
      -- autorisation connection sur meme application 
  
[b]recuperation de si l'utilisateur a le droit d'ouvrir plusieur session : n_number[/b]            
  
      IF n_number = 0 Then 
        Select count(distinct USERNAME||UPPER(PROGRAM)||PROCESS)
        into  n_number 
        from  v$session 
        where upper(username) = upper(user) and 
              Upper(PROGRAM)  = V_CONNECTION_PROGRAM;
  
        If n_number > 1 Then
  --        V_LBPARAM := 'Vous êtes déjà connecté sur l''aplication ''';
  --        IF NVL(V_CONNECTION_PROGRAM,'£') = '£' Then 
  --          Open C5(V_CONNECTION_PROGRAM);
  --          Fetch C5 into V_CONNECTION_PROGRAM2;
  --          Found := C5%FOUND;
  --          Close C5;
  --          IF FOUND THEN
              V_LBPARAM := V_LBPARAM||V_CONNECTION_PROGRAM||''' !';
              RAISE_APPLICATION_ERROR (-20200, V_LBPARAM);
  --          END IF;
  --        End If;
        End If;
      End if;
  
      -- autorisation connection sur meme poste 

[b]Recuperer V_LBPARAM[/b] 


      IF V_LBPARAM = 'N' THEN 
  
        SELECT COUNT(DISTINCT USERNAME||MACHINE) 
        INTO N_NUMBER 
        FROM V$SESSION 
        WHERE  UPPER(USERNAME) = UPPER(USER) AND 
               UPPER(USERNAME) <> 'PL' and 
               UPPER(USERNAME) <> 'ADMINISTRATEUR';
  
        IF N_NUMBER > 1 THEN 
  
          SELECT  SYS_CONTEXT('USERENV','HOST') 
          INTO V_MACHINE1 
          FROM DUAL;
  
          V_LBPARAM := 'Vous êtes déjà connecté sur le poste ''';
          IF V_MACHINE1 IS NOT NULL THEN
  --          IF NVL(V_CONNECTION_PROGRAM,'£') = '£' Then 
  --            OPEN C1(V_MACHINE1);
  --            FETCH C1 INTO V_MACHINE2;
  --            FOUND := C1%FOUND;
  --            CLOSE C1;
  --          ELSE
            OPEN C12(V_MACHINE1);
            FETCH C12 INTO V_MACHINE2, V_CONNECTION_PROGRAM2;
            FOUND := C12%FOUND;
            CLOSE C12;
  
  --          END IF;
            IF FOUND THEN
  
              V_LBPARAM := V_LBPARAM||V_MACHINE2||''' !';
              RAISE_APPLICATION_ERROR (-20200, V_LBPARAM);
  
            END IF;
          END IF;
        END IF;
      END IF;
    END IF;
  END IF;
END;
/

Create or replace Procedure SYS.P_CONNECTIONOFF
Is
  found Boolean;
  v_connection_SID_SERIAL#     audit_connection.connection_SID_SERIAL#%TYPE;
  v_connection_module          audit_connection.connection_module%TYPE;
  v_LOGON_TIME                 date;
  
  Cursor C1 is
    Select connection_SID_SERIAL#
    from   audit_connection
    where  connection_SID_SERIAL# = v_connection_SID_SERIAL#;

  Cursor C3 is
    Select connection_SID_SERIAL#
    from   audit_connection
    where  connection_SID_SERIAL# <> '£' and 
           connection_SID_SERIAL# not in ( Select to_char(SID)||':'||to_char(SERIAL#)
                                           from v$session);



  Cursor C2 is
    Select to_char(SID)||':'||to_char(SERIAL#), nvl(module,'£'), LOGON_TIME
    from v$session
    where  upper(username) = upper(user) and 
           AUDSID = SYS_CONTEXT('USERENV','SESSIONID')  and 
           AUDSID > 0 ;
  
BEGIN

  Open C3;
  Fetch C3 into v_connection_SID_SERIAL#;
  Found := C3%FOUND;
  While Found Loop
        Update audit_connection
        set   connection_SID_SERIAL#= NULL,
              connection_dateOFF = SYSDATE,
              connection_NB = nvl(connection_NB,0) + 1,
              connection_TIME = nvl(connection_TIME,0) + (SYSDATE - connection_dateON)*24*60*60
        WHERE connection_SID_SERIAL#= v_connection_SID_SERIAL#;
    Fetch C3 into v_connection_SID_SERIAL#;
    Found := C3%FOUND;
  end loop;
  close C3;

  Open C2;
  Fetch C2 into v_connection_SID_SERIAL#, v_connection_module, v_LOGON_TIME;
  Found := C2%FOUND;
  Close C2;
  If Found Then 
    Open C1;
    Fetch C1 into v_connection_SID_SERIAL#;
    Found := C1%FOUND;
    Close C1;
    If Found Then 
      If v_connection_module <> '£' Then 
        Update audit_connection
        set   connection_SID_SERIAL#= '£',
              connection_dateOFF = SYSDATE,
              connection_NB = nvl(connection_NB,0) + 1,
              connection_TIME = trunc(nvl(connection_TIME,0) + (SYSDATE - nvl(v_LOGON_TIME,SYSDATE))*24*60*60,0),
              connection_module = v_connection_module
        WHERE connection_SID_SERIAL#= v_connection_SID_SERIAL#;
      Else
        Update audit_connection
        set   connection_SID_SERIAL#= '£',
              connection_dateOFF = SYSDATE,
              connection_NB = nvl(connection_NB,0) + 1,
              connection_TIME = trunc(nvl(connection_TIME,0) + (SYSDATE - nvl(v_LOGON_TIME,SYSDATE))*24*60*60,0)
        WHERE connection_SID_SERIAL#= v_connection_SID_SERIAL#;
      End if;
    End if;
  End if; 
END;

CREATE OR REPLACE TRIGGER SYS.CONNECTIONOFF
BEFORE LOGOFF ON DATABASE
BEGIN
  SYS.P_CONNECTIONOFF;
END;

CREATE OR REPLACE TRIGGER SYS.CONNECTIONON
AFTER LOGON
ON DATABASE
DECLARE

BEGIN
  SYS.P_CONNECTIONON;
END;