Re: Can't create a trigger on table i created

In article <8o671m$v0b$1_at_nnrp1.deja.com>,   harkinj_at_my-deja.com wrote:
> Hi,
> I'm very new to Oracle so bare with me .
> I'm using Personal Oracle 8.0 on NT.
>
> I've logged into my conf database using
>
> connect internal/oracle_at_conf;
>
> I've created a table named inventory .
> When i attempt to create a trigger with the below coide in SQL Plus
>
> CREATE OR REPLACE TRIGGER Create_Awf_Job
> BEFORE UPDATE ON Inventory
> call CreateJob('http://johnh-p2-400/createnewjob.asp',
> '0XCF804840115111d48C6100104B71BD07',

 '0xF50517327A8E11D4A6CE00104B8D17A
> 2', 50, 10)
>
> I get the following error.
> ORA-04089: cannot create triggers on objects owned by SYS
>
> How can i get the trigger created on the table?. I Think Internal is a
> user name created when i installed the product.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

The 'internal' user is 'SYS', therefore you have created a table owned by SYS. As such, you cannot create a trigger on that table. You will need to create a new user, grant the necessary privileges to that user, then connect through SQL*Plus as that user and create your table:

First, create the user (I am presuming that the USERS tablespace and the TEMP tablespace exist):

connect internal/oracle_at_conf

create user <username> identified by <password> default tablespace users temporary tablespace temp;

grant connect, resource to <username>;

alter user <username> quota <some value|UNLIMITED> on users;

Now, connect as this new user and create your table:

connect <username>/<password>

create table Inventory ( ... );

You should now be able to create your trigger on the Inventory table:

CREATE OR REPLACE TRIGGER Create_Awf_Job  BEFORE UPDATE ON Inventory
 call
CreateJob('http://johnh-p2-400/createnewjob.asp','0XCF804840115111d48C61 00104B71BD07', '0xF50517327A8E11D4A6CE00104B8D17A2', 50, 10)  ...

You will need to connect as internal and drop the Inventory table you created earlier.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.