Oracle daily practice trigger cannot invoke or indirectly invoke a DCL statement such as Commit,rollback

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.