Ass Soon a Funtion Is Triger Stop From Triggering Again

Alter TRIGGER

Purpose

Utilise the ALTER TRIGGER statement to enable, disable, or compile a database trigger.

Annotation:

This statement does not change the declaration or definition of an existing trigger. To redeclare or redefine a trigger, employ the CREATE TRIGGER statement with the OR Supersede keywords.

Prerequisites

The trigger must be in your ain schema or you lot must have ALTER ANY TRIGGER organisation privilege.

In addition, to alter a trigger on DATABASE, you lot must have the ADMINISTER database events organisation privilege.

Come across Also:

CREATE TRIGGER for more information on triggers based on DATABASE triggers

Syntax

alter_trigger::=

Description of the analogy alter_trigger.gif

compiler_parameters_clause::=

Clarification of the illustration compiler_parameters_clause.gif

Semantics

schema

Specify the schema containing the trigger. If you omit schema , then Oracle Database assumes the trigger is in your ain schema.

trigger

Specify the name of the trigger to be altered.

ENABLE | DISABLE

Specify ENABLE to enable the trigger. Yous can also use the ENABLE ALL TRIGGERS clause of Alter Tabular array to enable all triggers associated with a table. See ALTER TABLE.

Specify DISABLE to disable the trigger. You can likewise employ the DISABLE ALL TRIGGERS clause of ALTER TABLE to disable all triggers associated with a table.

RENAME Clause

Specify RENAME TO new_name to rename the trigger. Oracle Database renames the trigger and leaves it in the aforementioned state it was in before existence renamed.

When you rename a trigger, the database rebuilds the remembered source of the trigger in the USER_SOURCE, ALL_SOURCE, and DBA_SOURCE information dictionary views. As a result, comments and formatting may alter in the TEXT column of those views even though the trigger source did not alter.

COMPILE Clause

Specify COMPILE to explicitly compile the trigger, whether it is valid or invalid. Explicit recompilation eliminates the demand for implicit run-time recompilation and prevents associated run-time compilation errors and performance overhead.

Oracle Database first recompiles objects upon which the trigger depends, if any of these objects are invalid. If the database recompiles the trigger successfully, then the trigger becomes valid.

During recompilation, the database drops all persistent compiler switch settings, retrieves them again from the session, and stores them at the end of compilation. To avoid this procedure, specify the REUSE SETTINGS clause.

If recompiling the trigger results in compilation errors, then the database returns an error and the trigger remains invalid. You tin can encounter the associated compiler mistake letters with the SQL*Plus command Testify ERRORS.

DEBUG

Specify DEBUG to instruct the PL/SQL compiler to generate and store the code for employ by the PL/SQL debugger. Specifying this clause has the same effect as specifying PLSQL_DEBUG = TRUE in the compiler_parameters_clause .

compiler_parameters_clause

This clause has the same behavior for a trigger as information technology does for a role. Delight refer to the Change FUNCTION compiler_parameters_clause .

REUSE SETTINGS

This clause has the same behavior for a trigger as information technology does for a office. Please refer to the Change Part clause REUSE SETTINGS.

Examples

Disabling Triggers: ExampleThe sample schema 60 minutes has a trigger named update_job_history created on the employees table. The trigger is fired whenever an UPDATE statement changes an employee'southward job_id. The trigger inserts into the job_history tabular array a row that contains the employee's ID, begin and end engagement of the last job, and the job ID and department.

When this trigger is created, Oracle Database enables it automatically. You can subsequently disable the trigger with the post-obit argument:

ALTER TRIGGER update_job_history DISABLE;        

When the trigger is disabled, the database does non fire the trigger when an UPDATE statement changes an employee's chore.

Enabling Triggers: CaseAfter disabling the trigger, you can subsequently enable information technology with the following statement:

ALTER TRIGGER update_job_history ENABLE;        

After you reenable the trigger, Oracle Database fires the trigger whenever an employee's job changes equally a result of an UPDATE statement. If an employee's task is updated while the trigger is disabled, then the database does not automatically burn the trigger for this employee until another transaction changes the job_id once more.

collinsnessittere40.blogspot.com

Source: https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_4001.htm

Share this post