trigger errors out [message #340842] |
Thu, 14 August 2008 09:59  |
patdev
Messages: 74 Registered: August 2008
|
Member |
|
|
Hi all,
Would any one plase tell me what is wrong with this script:
ALTER trigger logoff_audit_trigger
BEFORE LOGOFF ON DATABASE
BEGIN
-- ***************************************************
-- Update the last action accessed
-- ***************************************************
update stats$user_log
set last_action = (select action from v$session where sys_context('USERENV','SESSIONID') = audsid)
where sys_context('USERENV','SESSIONID') = session_id;
--***************************************************
-- Update the last program accessed
-- ***************************************************
update stats$user_log
set last_program = (select program from v$session where sys_context('USERENV','SESSIONID') = audsid)
where sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update the last module accessed
-- ***************************************************
update stats$user_log
set last_module = (select module from v$session where sys_context('USERENV','SESSIONID') = audsid)
where sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update the logoff day
-- ***************************************************
update stats$user_log
set logoff_day = sysdate
where sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update the logoff time
-- ***************************************************
update stats$user_log
set logoff_time = to_char(sysdate, 'hh24:mi:ss')
where sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Compute the elapsed minutes
-- ***************************************************
update stats$user_log
set elapsed_minutes = round((logoff_day - logon_day)*1440)
where sys_context('USERENV','SESSIONID') = session_id;
COMMIT;
END;
/
I still get error as followed:
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/1 PL/SQL: SQL Statement ignored
7/39 PL/SQL: ORA-00942: table or view does not exist
12/1 PL/SQL: SQL Statement ignored
13/41 PL/SQL: ORA-00942: table or view does not exist
18/1 PL/SQL: SQL Statement ignored
19/39 PL/SQL: ORA-00942: table or view does not exist
the table is exist because other log on trigger works fine!!
Thanks
Pat
|
|
|
|
|
|
|
Re: trigger errors out [message #340949 is a reply to message #340842] |
Fri, 15 August 2008 02:55   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I suspect that the user that the trigger is created as only has permission to see the V$SESSION view via a Role.
Try granting SELECT on v$SESSION explicitly to the user and then create the trigger again.
|
|
|
|
|