Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Unable to create database trigger

Posted on 2004-11-08
15
Medium Priority
?
761 Views
Last Modified: 2008-01-09
i am working with Oracle 8 on PC running on Windows 98. For learning purpose i login as scott/tiger.
I was trying to create following trigger (to keep track of each logon in my databse)

create or replace trigger track_logon
after logon on database
begin
    insert into log_trail values (user,sysdate,'LOGON');
   commit;
end ;

It gives the following error (the table log_trail has been created) :

SQL> start trigger1.sql
  7  /
after logon on database
      *
ERROR at line 2:
ORA-04072: invalid trigger type

What went wrong and where. Please help. Thanks.
--TheTechGuy.
0
Comment
Question by:Sandeep Sood
  • 7
  • 6
  • 2
15 Comments
 

Author Comment

by:Sandeep Sood
ID: 12522901
further..
 i have tried it by logging in as system/manager also. but same problem occured there also.

thanks.
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 12522924
do use commit in trigger.....

commit will not work in triggers

itsvtk
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 12522991
remove commit; statement and try....  if u get same problem  let me know

itsvtk
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 4

Expert Comment

by:boriskalavsky
ID: 12523531
trigger actions need to be able to rollback for uncommited transactions therefore you can not use commit in the trigger
0
 

Author Comment

by:Sandeep Sood
ID: 12529920
thanks all for your answers.
ok, i will try it. but the error it is displaying is at line  :

after logon on database
      *
ERROR at line 2:
ORA-04072: invalid trigger type

so is the trigger type valid. i means is it the correct way to create a database trigger. and does the user scott require any special privilege for it.
waiting..
Thanks.
0
 
LVL 4

Expert Comment

by:boriskalavsky
ID: 12533976
COMPATIBLE parameter needs to be set to at least 8.1.6
It is nopt supported if you do not have =>8.1.6
0
 
LVL 4

Expert Comment

by:boriskalavsky
ID: 12533988
not supported at least 8.1.6
0
 

Author Comment

by:Sandeep Sood
ID: 12536403
Hi. boriskalavsky  feedback
sorry, i was unable to understand your answer.
PLEASE. can you explain a little more.
what do you mean by 8.1.6 ?
Waiting..
Thanks.
0
 
LVL 4

Expert Comment

by:boriskalavsky
ID: 12552121
COMPATIBLE parameter needs to be set to at least 8.1.6 in init.ora
"after logon on database" triggers are not supported if you do not have => 8.1.6
0
 

Author Comment

by:Sandeep Sood
ID: 12705709
Hi boriskalavsky !!
thanks for your help. but..

i could not set Compatible = 8.1.6.
first, i dont have a file named init.ora instead i have initorcl.ora and initseed.ora files in c:\orawin95\database directory.

second, i tried to write compatible = 8.1.6 at the end of the file initorcl.ora  but then could not start oracle. it resultd in error "ora-401" saying "Can Not Satrt Oracle 8 Instance"

3rd
I am using SQL*Plus: Release 8.0.4.0.0
I tried to update it using alter system command also. but got the following error.



SQL> alter system set compatible='8.1.6' ;
alter system set compatible='8.1.6'
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified


Please look into the matter and reply.
thanks again.
waiting..
0
 

Author Comment

by:Sandeep Sood
ID: 12723581
hello.
Please any experts can solve this problem.
waiting..
0
 
LVL 4

Expert Comment

by:boriskalavsky
ID: 12724218
"after logon on database" triggers are not supported if you do not have => 8.1.6 and seem to be running version 8.0.4 of Oracle. Try to run "select * from v$version;" in SQLPlus. Result lower then 8.1.6 would mean that you can not run "after logon on database". Oracle did not have this functionality in versions before 8.1.6.
0
 

Author Comment

by:Sandeep Sood
ID: 12725311
Thanks boriskalavsky  !!
Ok Sir.
i promise to accept your answer.
but just as a closing comment  pleae tell me is there any other way to achieve this in Oracle 8.0
thanks.
0
 
LVL 4

Accepted Solution

by:
boriskalavsky earned 375 total points
ID: 12726620
All new sessions are going to be instarted in v$session (catalog view).
0
 
LVL 4

Expert Comment

by:boriskalavsky
ID: 12733412
All new sessions are going to be inserted in v$session (catalog view).
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question