Home » SQL & PL/SQL » SQL & PL/SQL » trigger errors out
trigger errors out [message #340842] Thu, 14 August 2008 09:59 Go to next message
patdev
Messages: 56
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 #340843 is a reply to message #340842] Thu, 14 August 2008 10:04 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: trigger errors out [message #340851 is a reply to message #340842] Thu, 14 August 2008 11:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://asktom.oracle.com/tkyte/Misc/RolesAndProcedures.html

Regards
Michel
Re: trigger errors out [message #340854 is a reply to message #340842] Thu, 14 August 2008 11:13 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Please realize that only a single UPDATE can accomplish the same desired results with much less overhead.

Some/many/most DB professionals consider it to be "bad form" to store a computed value like elapsed_minutes within the DB.
Re: trigger errors out [message #340858 is a reply to message #340842] Thu, 14 August 2008 12:02 Go to previous messageGo to next message
zoltanp
Messages: 58
Registered: March 2005
Location: Hungary
Member
Your trigger is ssociated with the database, so In my opininion you have to write the name of schema.
(eg. hr.stats$user_log)

Regards,
Zoltán Patalenszki
Re: trigger errors out [message #340949 is a reply to message #340842] Fri, 15 August 2008 02:55 Go to previous messageGo to next message
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.
Re: trigger errors out [message #340951 is a reply to message #340949] Fri, 15 August 2008 03:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Hmm... Is this not what I pointed? Smug

Regards
Michel
Re: trigger errors out [message #340957 is a reply to message #340951] Fri, 15 August 2008 03:38 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That is entirely true.
Teach me to skim read the thread.
Previous Topic: ORA-29283: invalid file operation
Next Topic: Trigger
Goto Forum:
  


Current Time: Sat Dec 03 09:47:52 CST 2016

Total time taken to generate the page: 0.05507 seconds