Why cannot I create triggers on objects owned by SYS?
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.
![Suhail Gupta](https://i.stack.imgur.com/gzQEA.jpg?s=256&g=1)
Suhail Gupta
"There's nothing more permanent than a temporary hack." - Kyle Simpson "The strength of JavaScript is that you can do anything. The weakness is that you will." - Reg Braithwaite I am on internet Twitter @suhail3 E-mail [email protected]
Updated on July 18, 2022Comments
-
Suhail Gupta almost 2 years
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
andghazal_current_audit
were created bySYS
. Why cannot I create a trigger on the table created bySYS
.-
Iswanto San over 11 yearsOracle don't allow you to creating triggers on objects owned by SYS.
-
Jacob over 11 yearsIt 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?
-