To create a trigger in your schema on a table in your schema or on your schema (
If the trigger issues SQL statements or invokes procedures or functions, then the owner of the trigger must have the privileges necessary to perform these operations. These privileges must be granted directly to the owner rather than acquired through roles. Semantics OR REPLACE Re-creates the trigger if it exists, and recompiles it. Users who were granted privileges on the trigger before it was redefined can still access the procedure without being regranted the privileges. schema Name of the schema for the trigger to be created. Default: your schema. trigger Name of the trigger to be created. Triggers in the same schema cannot have the same names. Triggers can have the same names as other schema objects—for example, a table and a trigger can have the same name—however, to avoid confusion, this is not recommended. If a trigger produces compilation errors, then it is still created, but it fails on execution. A trigger that fails on execution effectively blocks all triggering DML statements until it is disabled, replaced by a version without compilation errors, or dropped. You can see the associated compiler error messages with the SQL*Plus command `TRIGGER`4 `TRIGGER`5. Note: If you create a trigger on a base table of a materialized view, then you must ensure that the trigger does not fire during a refresh of the materialized view. During refresh, the `TRIGGER`6 procedure `TRIGGER`7 returns `TRIGGER`8. Restrictions on create_trigger See . simple_dml_trigger Creates a simple DML trigger (described in ). BEFORE Causes the database to fire the trigger before running the triggering event. For row triggers, the trigger fires before each affected row is changed. Restrictions on BEFORE
AFTER Causes the database to fire the trigger after running the triggering event. For row triggers, the trigger fires after each affected row is changed. Restrictions on AFTER
Note: When you create a materialized view log for a table, the database implicitly creates an `SCHEMA`6 row trigger on the table. This trigger inserts a row into the materialized view log whenever an `CREATE`4, `CREATE`5, or `CREATE`6 statement modifies data in the master table. You cannot control the order in which multiple row triggers fire. Therefore, do not write triggers intended to affect the content of the materialized view. See Also:
FOR EACH ROW Creates the trigger as a row trigger. The database fires a row trigger for each row that is affected by the triggering statement and meets the optional trigger constraint defined in the `CREATE`7 condition. If you omit this clause, then the trigger is a statement trigger. The database fires a statement trigger only when the triggering statement is issued if the optional trigger constraint is met. [ ENABLE | DISABLE ] Creates the trigger in an enabled (default) or disabled state. Creating a trigger in a disabled state lets you ensure that the trigger compiles without errors before you enable it. Note: `CREATE`8 is especially useful if you are creating a crossedition trigger, which affects the online application being redefined if compilation errors occur. WHEN (condition) Specifies a SQL condition that the database evaluates for each row that the triggering statement affects. If the value of `CREATE`9 is `TRIGGER`8 for an affected row, then `TRIGGER`1 runs for that row; otherwise, `TRIGGER`1 does not run for that row. The triggering statement runs regardless of the value of `CREATE`9. The `CREATE`9 can contain correlation names (see ). In `CREATE`9, do not put a colon (:) before the correlation name `TRIGGER`6, `TRIGGER`7, or `TRIGGER`8 (in this context, it is not a placeholder for a bind variable). Restrictions on WHEN (condition)
trigger_body The PL/SQL block or `schema`3 subprogram that the database runs to fire the trigger. A `schema`3 subprogram is either a PL/SQL subprogram or a Java subprogram in a PL/SQL wrapper. If Restriction on trigger_body The `schema`9 cannot declare variables of the data type `SCHEMA`0 or `SCHEMA`0 `SCHEMA`2. instead_of_dml_trigger Creates an `SCHEMA`3 `SCHEMA`4 DML trigger (described in ). Restriction on INSTEAD OF An `SCHEMA`3 `SCHEMA`4 trigger can read the `SCHEMA`2 and `SCHEMA`1 values, but cannot change them. Note:
DELETE If the trigger is created on a noneditioning view, then `CREATE`6 causes the database to fire the trigger whenever a `CREATE`6 statement removes a row from the table on which the noneditioning view is defined. If the trigger is created on a nested table column of a noneditioning view, then `CREATE`6 causes the database to fire the trigger whenever a `CREATE`6 statement removes an element from the nested table. INSERT If the trigger is created on a noneditioning view, then `CREATE`4 causes the database to fire the trigger whenever an `CREATE`4 statement adds a row to the table on which the noneditioning view is defined. If the trigger is created on a nested table column of a noneditioning view, then `CREATE`4 causes the database to fire the trigger whenever an `CREATE`4 statement adds an element to the nested table. UPDATE If the trigger is created on a noneditioning view, then `CREATE`5 causes the database to fire the trigger whenever an `CREATE`5 statement changes a value in a column of the table on which the noneditioning view is defined. If the trigger is created on a nested table column of a noneditioning view, then `CREATE`5 causes the database to fire the trigger whenever an `CREATE`5 statement changes a value in a column of the nested table. nested_table_column Name of the `TRIGGER`7 on which the trigger is to be created. The trigger fires only if the DML operates on the elements of the nested table. Performing DML operations directly on nested table columns does not cause the database to fire triggers defined on the table containing the nested table column. For more information, see . See Also: `TRIGGER`8 `TRIGGER`9 clause of in Oracle Database SQL Language Reference for a list of constructs that prevent inserts, updates, or deletes on a view schema Name of the schema containing the noneditioning view. Default: your schema. noneditioning_view If you specify `TRIGGER`7, then `TRIGGER`03 is the name of the noneditioning view that includes `TRIGGER`7. Otherwise, `TRIGGER`03 is the name of the noneditioning view on which the trigger is to be created. FOR EACH ROW For documentation only, because an `SCHEMA`3 `SCHEMA`4 trigger is always a row trigger. ENABLE (Default) Creates the trigger in an enabled state. DISABLE Creates the trigger in a disabled state, which lets you ensure that the trigger compiles without errors before you enable it. Note: `CREATE`8 is especially useful if you are creating a crossedition trigger, which affects the online application being redefined if compilation errors occur. trigger_body The PL/SQL block or `schema`3 subprogram that the database runs to fire the trigger. A `schema`3 subprogram is either a PL/SQL subprogram or a Java subprogram in a PL/SQL wrapper. If Restriction on trigger_body The `schema`9 cannot declare variables of the data type `SCHEMA`0 or `SCHEMA`0 `SCHEMA`2. compound_dml_trigger Creates a compound DML trigger (described in ). ENABLE (Default) Creates the trigger in an enabled state. DISABLE Creates the trigger in a disabled state, which lets you ensure that the trigger compiles without errors before you enable it. Note: `CREATE`8 is especially useful if you are creating a crossedition trigger, which affects the online application being redefined if compilation errors occur. WHEN (condition) Specifies a SQL condition that the database evaluates for each row that the triggering statement affects. If the value of `CREATE`9 is `TRIGGER`8 for an affected row, then `TRIGGER`22 runs for that row; otherwise, `TRIGGER`22 does not run for that row. The triggering statement runs regardless of the value of `CREATE`9. The `CREATE`9 can contain correlation names (see ). In `CREATE`9, do not put a colon (:) before the correlation name `TRIGGER`6, `TRIGGER`7, or `TRIGGER`8 (in this context, it is not a placeholder for a bind variable). Restrictions on WHEN (condition)
system_trigger Defines a system trigger (described in ). BEFORE Causes the database to fire the trigger before running the triggering event. AFTER Causes the database to fire the trigger after running the triggering event. INSTEAD OF Creates an `SCHEMA`3 `SCHEMA`4 trigger. Restriction on INSTEAD OF The triggering event must be a ddl_event One or more types of DDL SQL statements that can cause the trigger to fire. You can create triggers for these events on Note: Some objects are created, altered, and dropped using PL/SQL APIs (for example, scheduler jobs are maintained by subprograms in the `TRIGGER`51 package). Such PL/SQL subprograms do not fire DDL triggers. The following `TRIGGER`52 values are valid:
database_event One of the following database events. You can create triggers for these events on either
[schema.]SCHEMA Defines the trigger on the specified schema. Default: current schema. The trigger fires whenever any user connected as the specified schema initiates the triggering event. DATABASE `TRIGGER`0 defines the trigger on the root. The trigger fires whenever any user of the specified database or pluggable database initiates the triggering event. ENABLE (Default) Creates the trigger in an enabled state. DISABLE Creates the trigger in a disabled state, which lets you ensure that the trigger compiles without errors before you enable it. WHEN (condition) Specifies a SQL condition that the database evaluates. If the value of `CREATE`9 is `TRIGGER`8, then `TRIGGER`1 runs for that row; otherwise, `TRIGGER`1 does not run for that row. The triggering statement runs regardless of the value of `CREATE`9. Restrictions on WHEN (condition)
trigger_body The PL/SQL block or `schema`3 subprogram that the database runs to fire the trigger. A `schema`3 subprogram is either a PL/SQL subprogram or a Java subprogram in a PL/SQL wrapper. If Restrictions on trigger_body
dml_event_clause Specifies the triggering statements for `SCHEMA`34 or `SCHEMA`35. The database fires the trigger in the existing user transaction. DELETE Causes the database to fire the trigger whenever a `CREATE`6 statement removes a row from `SCHEMA`37 or the table on which `SCHEMA`38 is defined. INSERT Causes the database to fire the trigger whenever an `CREATE`4 statement adds a row to `SCHEMA`37 or the table on which `SCHEMA`38 is defined. UPDATE [ OF column [, column ] ] Causes the database to fire the trigger whenever an `CREATE`5 statement changes a value in a specified column. Default: The database fires the trigger whenever an `CREATE`5 statement changes a value in any column of `SCHEMA`37 or the table on which `SCHEMA`38 is defined. If you specify a `SCHEMA`46, then you cannot change its value in the body of the trigger. schema Name of the schema that contains the database object on which the trigger is to be created. Default: your schema. table Name of the database table or object table on which the trigger is to be created. Restriction on schema.table You cannot create a trigger on a table in the schema `SCHEMA`47. view Name of the database view or object view on which the trigger is to be created. Note: A compound DML trigger created on a noneditioning view is not really compound, because it has only one timing point section. referencing_clause Specifies correlation names, which refer to old, new, and parent values of the current row. Defaults: `TRIGGER`7, `TRIGGER`6, and `TRIGGER`8. If your trigger is associated with a table named `TRIGGER`7, `TRIGGER`6, or `TRIGGER`8, then use this clause to specify different correlation names to avoid confusion between the table names and the correlation names. If the trigger is defined on a nested table, then `TRIGGER`7 and `TRIGGER`6 refer to the current row of the nested table, and `TRIGGER`8 refers to the current row of the parent table. If the trigger is defined on a database table or view, then `TRIGGER`7 and `TRIGGER`6 refer to the current row of the database table or view, and `TRIGGER`8 is undefined. Restriction on referencing_clause The `SCHEMA`60 is not valid if `TRIGGER`1 is `schema`3 `SCHEMA`63. trigger_edition_clause Creates the trigger as a crossedition trigger. The handling of DML changes during edition-based redefinition (EBR) of an online application can entail multiple steps. Therefore, it is likely, though not required, that a crossedition trigger is also a compound trigger. Restriction on trigger_edition_clause You cannot define a crossedition trigger on a view. FORWARD (Default) Creates the trigger as a forward crossedition trigger. A forward crossedition trigger is intended to fire when DML changes are made in a database while an online application that uses the database is being patched or upgraded with EBR. The body of a crossedition trigger is designed to handle these DML changes so that they can be appropriately applied after the changes to the application code are completed. REVERSE Creates the trigger as a reverse crossedition trigger, which is intended to fire when the application, after being patched or upgraded with EBR, makes DML changes. This trigger propagates data to columns or tables used by the application before it was patched or upgraded. trigger_ordering_clause FOLLOWS | PRECEDES Specifies the relative firing of triggers that have the same timing point. It is especially useful when creating crossedition triggers, which must fire in a specific order to achieve their purpose. Use `SCHEMA`64 to indicate that the trigger being created must fire after the specified triggers. You can specify `SCHEMA`64 for a conventional trigger or for a forward crossedition trigger. Use `SCHEMA`66 to indicate that the trigger being created must fire before the specified triggers. You can specify `SCHEMA`66 only for a reverse crossedition trigger. The specified triggers must exist, and they must have been successfully compiled. They need not be enabled. If you are creating a noncrossedition trigger, then the specified triggers must be all of the following:
If you are creating a crossedition trigger, then the specified triggers must be all of the following:
In the following definitions, A, B, C, and D are either noncrossedition triggers or forward crossedition triggers:
In the following definitions, A, B, C, and D are reverse crossedition triggers:
compound_trigger_block Belongs to `SCHEMA`35. If the trigger is created on a noneditioning view, then `SCHEMA`77 must have only the `SCHEMA`3 `SCHEMA`4 `schema`0 `schema`1 section. If the trigger is created on a table or editioning view, then timing point sections can be in any order, but no section can be repeated. The `SCHEMA`77 cannot have an `SCHEMA`3 `SCHEMA`4 `schema`0 `schema`1 section. Restriction on compound_trigger_block The `schema`9 of `SCHEMA`77 cannot include `SCHEMA`89 `SCHEMA`90. BEFORE STATEMENT Specifies the `TRIGGER`9 `SCHEMA`92 section of a `SCHEMA`35 on a table or editioning view. This section causes the database to fire the trigger before running the triggering event. Restriction on BEFORE STATEMENT This section cannot specify `SCHEMA`1 or `SCHEMA`2. BEFORE EACH ROW Specifies the `TRIGGER`9 `schema`0 `schema`1 section of a `SCHEMA`35 on a table or editioning view. This section causes the database to fire the trigger before running the triggering event. The trigger fires before each affected row is changed. This section can read and write the `SCHEMA`2 and `SCHEMA`1 fields. AFTER STATEMENT Specifies the `SCHEMA`6 `SCHEMA`92 section of `SCHEMA`35 on a table or editioning view. This section causes the database to fire the trigger after running the triggering event. Restriction on AFTER STATEMENT This section cannot specify `SCHEMA`1 or `SCHEMA`2. AFTER EACH ROW Specifies the `SCHEMA`6 `schema`0 `schema`1 section of a `SCHEMA`35 on a table or editioning view. This section causes the database to fire the trigger after running the triggering event. The trigger fires after each affected row is changed. This section can read but not write the `SCHEMA`2 and `SCHEMA`1 fields. INSTEAD OF EACH ROW Specifies the `SCHEMA`3 `SCHEMA`4 `schema`0 `schema`1 section (the only timing point section) of a `SCHEMA`35 on a noneditioning view. The database runs `TRIGGER`22 instead of running the triggering DML statement. For more information, see . Restriction on INSTEAD OF EACH ROW
tps_body The PL/SQL block or `schema`3 subprogram that the database runs to fire the trigger. A `schema`3 subprogram is either a PL/SQL subprogram or a Java subprogram in a PL/SQL wrapper. If |