CODEWITHSUNDEEP
databaseoracle-databasetriggersoracle11g
Barry
Barry

Reputation: 1084

Replacing a Oracle 11g trigger automatically enables it

A bit of software I'm working on uses triggers in Oracle. We have a whole set that are currently created and disabled in the database. When running a CREATE or REPLACE to modify a trigger that already exists that trigger gets automatically enabled. I understand CREATE will automatically enable something but why is replace doing this?

Also what's weird however is that this only happens on one of our databases, the others are fine. Is there a setting inside the database that is causing this possibly or is enabling expected behavior of REPLACE?

Upvotes: 3

Views: 1081

Answers (2)

Álvaro González
Álvaro González

Reputation: 146450

I suppose that the OR REPLACE clause is considered a modifier of the CREATE command rather than a command by itself. And the CREATE TRIGGER command is designed to do so:

Use the CREATE TRIGGER statement to create and enable a database trigger [...]
When you create a trigger, the database enables it automatically. You can subsequently disable and enable a trigger with the DISABLE and ENABLE clause of the ALTER TRIGGER or ALTER TABLE statement.

Upvotes: 2

David Aldridge
David Aldridge

Reputation: 52356

Enabling and disabling triggers is not a "normal" thing for an application to be doing, so you may be getting misled by this piece of software. In general if you create a piece of code that has valid syntax and can be compiled, then it's going to be enabled on creation. "create or replce" is just a convenience to be able to modify an item of code without dropping it (and possibly causing undesirable side-effects, like dropping privileges on it) first. It's no different from create in respect of compilation.

Now what you can do is "create or replace trigger ... disable when ...", which would of course create it as disabled.

Upvotes: 0

Related Questions