triggers cannot invoke or indirectly invoke a DCL statement such as Commit,rollback
DDL statements and Commit,rollback statements cannot be run in triggers
DDL statements: DDL statement terms define and manage objects in the database, such as Create,alter,drop,truncate, and DDL operations are implicitly committed!
The operation takes effect immediately and the original data is not placed in the rollback segment and cannot be rolled back. Operation does not trigger trigger
The DML (data manipulation Language) Manipulation Language command enables users to query the database and manipulate data in existing databases.
such as Insert,delete,update,select, are all DML.
triggers do not require a commit, and they cannot write a commit The trigger and the DML that triggered it are committed by the same transaction DML, and the action of the trigger is also committed, so no commit is required, otherwise an error message is created. Of course, if you must write a commit in a trigger, that is also possible, and can be handled by an autonomous transaction in Oracle, which is equivalent to a child transaction within a transaction.
Under normal circumstances, Oracle stipulates that DDL (that is, create/alter/drop) statements and Commit/rollback statements cannot be run in triggers, Because DDL operations are implicitly committed, when a trigger does not allow a commit, such as a DDL statement in a trigger, this implicit commit causes an error message, but sometimes a DDL statement needs to be used in a trigger in special cases.
can take the following workaround:
1. In the trigger, you can add: pragma autonomous_transaction; (behind declare) means free transactions.
such as:
CREATE OR REPLACE TRIGGER t_create before insert on T_TAX_INS_BD
for each row
DECLARE
pragma aut onomous_transaction;
Nrdsid varchar ($): = ';
begin
This way you can resolve a problem in a trigger that cannot have DDL statements!
One: use DDL statements in triggers. If the drop table T1, the trigger will be error,
Ora-04092:cannot commit in a trigger because the DDL statement implicitly commits.
Two:
Cannot COMMIT in a trigger because DML (DELETE/UPDATE/INSERT) triggers
You cannot use DDL (Create,drop,alter) statements, or you cannot use Transaction control statements (DCL)
(ROLLBACK, Commit,savepoint). It is particularly important to note that the object referenced in the body of the trigger
There is no object control statement in function/procedure (procedure).
Three: NOTE: DDL statements can be used in system-level triggers (systems Triggers).
Four: The processing method: Remove the transaction control statement, if the procedure must have a commit, then you can
Take the commit away from the outside control.
Oracle daily practice trigger cannot invoke or indirectly invoke a DCL statement such as Commit,rollback