CODEWITHSUNDEEP
oracle-databasetriggersoracle10gsequence
user3239687
user3239687

Reputation: 29

I want to auto increment a column and make it as a primary key in oracle10g. But on trigger creation I get an error

I have created a Sequence for the table and on creation of a trigger I got the following error:

ORA-04089: cannot create triggers on objects owned by SYS.

Can someone explain me what is it and how can it be solved? Since I'm a beginner, is there a simple way to solve it? MY TABLE NAME IS-testing.

create sequence example_seq 
start with 1
increment by 1
nomaxvalue;

create trigger example_trigger
before insert on testing
for each row
begin
select example_seq.nextval into :new.id from dual;
end;

Upvotes: 0

Views: 129

Answers (1)

APC
APC

Reputation: 146239

The error message is quite clear:

ORA-04089: cannot create triggers on objects owned by SYS.

So obviously you are logged in as SYS, and creating objects in that user;s schemas. You must not do this. SYS is the user who owns the data dictionary and all the objects which manage the database. Changing the SYS schema is, at best, bad practice and runs the risk of corrupting the database. Find out more.

What you need to do is create a new user, for your application objects. This is quite simple. Connect as SYSTEM (avoid using SYS for menial tasks):

create user your_name_here identified by << password >>
/

grant create session, create table, create sequence, create trigger
     to your_name_here
/

Obviously you will need to grant more privileges. The full list is in the documentation.

Once you've created your new user you can connect to it and create all the objects you desire.

Upvotes: 1

Related Questions