Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In the database, it may sometimes be necessary to disable system triggers during operations such as java installation, downgrade, or upgrade. An error in a system trigger that we create can cause serious problems in the database. In these cases, system triggers may need to be disabled.
To disable system triggers, the value of parameter _system_trig_enabled must be set to false. We can learn the current value of this parameter with the following query.
1 2 3 4 5 6 | SELECT x.ksppinm name, y.ksppstvl VALUE, ksppdesc description FROM x$ksppi x, x$ksppcv y WHERE x.inst_id = USERENV ('Instance') AND y.inst_id = USERENV ('Instance') AND x.indx = y.indx AND x.ksppinm = '_system_trig_enabled' |
We can change the parameter as follows.
1 2 3 | SQL> alter system set "_system_trig_enabled"=false scope=both sid='*'; System altered. |
You can use the following query to find out which triggers will be disabled.
1 2 3 | SELECT a.obj#, a.sys_evts, b.name FROM trigger$ a, obj$ b WHERE a.sys_evts > 0 AND a.obj# = b.obj#; |
The events that the parameter will affect are as follows:
Resource Manager Events
- STARTUP
- SHUTDOWN
- SERVERERROR
Client Events
- AFTER LOGON
- BEFORE LOGOFF
- BEFORE CREATE
- AFTER CREATE
- BEFORE ALTER
- AFTER ALTER
- BEFORE DROP
- AFTER DROP
- BEFORE ANALYZE
- AFTER ANALYZE
- BEFORE ASSOCIATE STATISTICS
- AFTER ASSOCIATE STATISTICS
- BEFORE AUDIT
- AFTER AUDIT
- BEFORE NOAUDIT
- AFTER NOAUDIT
- BEFORE COMMENT
- AFTER COMMENT
- BEFORE CREATE
- AFTER CREATE
- BEFORE DDL
- AFTER DDL
- BEFORE DISASSOCIATE STATISTICS
- AFTER DISASSOCIATE STATISTICS
- BEFORE GRANT
- AFTER GRANT
- BEFORE RENAME
- AFTER RENAME
- BEFORE REVOKE
- AFTER REVOKE
- BEFORE TRUNCATE
- AFTER TRUNCATE
