11

While trying to create a trigger named ghazal_current_bef_upd_row :

create trigger ghazal_current_bef_upd_row
before update on ghazal_current
for each row 
when (new.Rating < old.Rating)
begin

insert into ghazal_current_audit
 (GhazalName,Old_Rating,New_Rating)
 values
 (:old.GhazalName,:old.Rating,:new.Rating);
end;

I get the following error :

Error report:
ORA-04089: cannot create triggers on objects owned by SYS
04089. 00000 -  "cannot create triggers on objects owned by SYS"
*Cause:    An attempt was made to create a trigger on an object owned by SYS.
*Action:   Do not create triggers on objects owned by SYS.

Both the tables named ghazals_current and ghazal_current_audit were created by SYS. Why cannot I create a trigger on the table created by SYS .

APC
  • 144,005
  • 19
  • 170
  • 281
Suhail Gupta
  • 22,386
  • 64
  • 200
  • 328
  • 1
    Oracle don't allow you to creating triggers on objects owned by SYS. – Iswanto San Mar 13 '13 at 05:08
  • 3
    It is not at all a good practice to create user based triggers in SYS schema. Why not create tables, triggers and other objects in a new schema? – Jacob Mar 13 '13 at 05:08

1 Answers1

12

You should not be creating any objects in the SYS schema. That user is part of the Oracle database management system, and changing its schema is likely to break your database. Certainly it could invalidate your Oracle Support contract (if you have one). From the documentation:

"The administrative account SYS is automatically created when a database is created. This account can perform all database administrative functions. The SYS schema stores the base tables and views for the data dictionary. These base tables and views are critical for the operation of Oracle Database. Tables in the SYS schema are manipulated only by the database and must never be modified by any user."

Oh, in case you're wondering, the same applies to SYSTEM too.

Triggers are particularly prone to abuse and are a major source of scaling problems. That's why Oracle forbids us to build triggers in SYS, because doing so might corrupt or at least impact the performance of the data dictionary.

Of course that's not what's happening here. You have built your own tables in SYS. Well drop them. Now. Use SYS to create your own user, GHAZAL or whatever name suits, and grant it the required privileges: CREATE SESSION, CREATE TABLE, CREATE TRIGGER, and so forth. Then connect as that new user to create your tables and other schema objects.

APC
  • 144,005
  • 19
  • 170
  • 281