Scribd Logo
Upload

Welcome to Scribd!

  • 31 views

    Triggers

    Uploaded by

    Abdul Khaliq
    sql pl/sql

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOC, PDF, TXT or read online from Scribd

    Triggers

    Uploaded by

    Abdul Khaliq
    31 views2 pages
    sql pl/sql

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    DOC, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    sql pl/sql

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOC, PDF, TXT or read online from Scribd
    Download as doc, pdf, or txt
    31 views2 pages

    Triggers

    Uploaded by

    Abdul Khaliq
    sql pl/sql

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOC, PDF, TXT or read online from Scribd
    Download as doc, pdf, or txt
    of 2

    9 PL/SQL Triggers

    A trigger is like a stored procedure that Oracle Database invokes automatically whenever a specified event occurs. Note: The database can detect only system-defined events. You cannot define your own events. Topics

    Overview of Triggers Reasons to Use Triggers DML Triggers System Triggers Subprograms Invoked by Triggers Trigger Compilation, Invalidation, and Recompilation Exception Handling in Triggers Trigger Design Guidelines Trigger Restrictions Order in Which Triggers Fire Trigger Enabling and Disabling Trigger Changing and Debugging Triggers and Oracle Database Data Transfer Utilities Triggers for Publishing Events Views for Information About Triggers

    Overview of Triggers
    Like a stored procedure, a trigger is a named PL/SQL unit that is stored in the database and can be invoked repeatedly. Unlike a stored procedure, you can enable and disable a trigger, but you cannot explicitly invoke it. While a trigger is enabled, the database automatically invokes itthat is, the trigger fireswhenever its triggering event occurs. While a trigger is disabled, it does not fire. You create a trigger with the CREATE TRIGGER statement. You specify the triggering event in terms of triggering statements and the item on which they act. The trigger is said to be created on or defined on the item, which is either a table, a view, a schema, or the database. You also specify the timing point, which determines whether the trigger fires before or after the triggering statement runs and whether it fires for each row that the

    triggering statement affects. By default, a trigger is created in the enabled state. For more information about the CREATE TRIGGER statement, see "CREATE TRIGGER Statement". If the trigger is created on a table or view, then the triggering event is composed of DML statements, and the trigger is called a DML trigger. For more information, see "DML Triggers". If the trigger is created on a schema or the database, then the triggering event is composed of either DDL or database operation statements, and the trigger is called a system trigger. For more information, see "System Triggers". A conditional trigger has a WHEN clause that specifies a SQL condition that the database evaluates for each row that the triggering statement affects. For more information about the WHEN clause, see "WHEN (condition)". When a trigger fires, tables that the trigger references might be undergoing changes made by SQL statements in other users' transactions. SQL statements running in triggers follow the same rules that standalone SQL statements do. Specifically:

    Queries in the trigger see the current read-consistent materialized view of referenced tables and any data changed in the same transaction. Updates in the trigger wait for existing data locks to be released before proceeding. trigger is either:

    An

    INSTEAD OF

    A DML trigger created on either a noneditioning view or a nested table column of a noneditioning view A system trigger defined on a CREATE statement
    INSTEAD OF

    The database fires the statement.

    trigger instead of running the triggering

    Note: A trigger is often called by the name of its triggering statement (for example, DELETE trigger or LOGON trigger), the name of the item on which it is defined (for example, DATABASE trigger or SCHEMA trigger), or its timing point (for example, BEFOREstatement trigger or AFTER each row trigger).

    You might also like