abaz
asked on
database trigger not work properly or not created
i logon as a system user and i create this logoff database trigger on my database but it not work properly or no created what is the problem :
create or replace trigger
logoff_audit_trigger
BEFORE LOGOFF ON spcoem
BEGIN
-- ************************** ********** ********** *****
-- Update the last action accessed
-- ************************** ********** ********** *****
update
stats$user_log
set
last_action = (select action from v$session where
sys_context('USERENV','SES SIONID') = audsid)
where
sys_context('USERENV','SES SIONID') = session_id;
--************************ ********** ********** *******
-- Update the last program accessed
-- ************************** ********** ********** *****
update
stats$user_log
set
last_program = (select program from v$session where
sys_context('USERENV','SES SIONID') = audsid)
where
sys_context('USERENV','SES SIONID') = session_id;
-- ************************** ********** ********** *****
-- Update the last module accessed
-- ************************** ********** ********** *****
update
stats$user_log
set
last_module = (select module from v$session where
sys_context('USERENV','SES SIONID') = audsid)
where
sys_context('USERENV','SES SIONID') = session_id;
-- ************************** ********** ********** *****
-- Update the logoff day
-- ************************** ********** ********** *****
update
stats$user_log
set
logoff_day = sysdate
where
sys_context('USERENV','SES SIONID') = session_id;
-- ************************** ********** ********** *****
-- Update the logoff time
-- ************************** ********** ********** *****
update
stats$user_log
set
logoff_time = to_char(sysdate, 'hh24:mi:ss')
where
sys_context('USERENV','SES SIONID') = session_id;
-- ************************** ********** ********** *****
-- Compute the elapsed minutes
-- ************************** ********** ********** *****
update
stats$user_log
set
elapsed_minutes =
round((logoff_day - logon_day)*1440)
where
sys_context('USERENV','SES SIONID') = session_id;
END;
/
create or replace trigger
logoff_audit_trigger
BEFORE LOGOFF ON spcoem
BEGIN
-- **************************
-- Update the last action accessed
-- **************************
update
stats$user_log
set
last_action = (select action from v$session where
sys_context('USERENV','SES
where
sys_context('USERENV','SES
--************************
-- Update the last program accessed
-- **************************
update
stats$user_log
set
last_program = (select program from v$session where
sys_context('USERENV','SES
where
sys_context('USERENV','SES
-- **************************
-- Update the last module accessed
-- **************************
update
stats$user_log
set
last_module = (select module from v$session where
sys_context('USERENV','SES
where
sys_context('USERENV','SES
-- **************************
-- Update the logoff day
-- **************************
update
stats$user_log
set
logoff_day = sysdate
where
sys_context('USERENV','SES
-- **************************
-- Update the logoff time
-- **************************
update
stats$user_log
set
logoff_time = to_char(sysdate, 'hh24:mi:ss')
where
sys_context('USERENV','SES
-- **************************
-- Compute the elapsed minutes
-- **************************
update
stats$user_log
set
elapsed_minutes =
round((logoff_day - logon_day)*1440)
where
sys_context('USERENV','SES
END;
/
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
my msn messenger is
abz01@hotmail.com
to tell you for the trigger ...
abz01@hotmail.com
to tell you for the trigger ...
ASKER
I test "BEFORE LOGOFF ON database"
and the trigger work properly ...