Scribd Logo
Upload

Welcome to Scribd!

  • 267 views

    Chapter 13 Solutions

    Uploaded by

    Funny Momentz
    Triggers allow specified actions to be performed automatically within the database in response to events like DML operations or DDL statements. The most important thing to check before creating a database trigger is the recursive and cascading effects. When creating a DML statement trigger on a table, the timing, event, object name, and trigger body must be defined. A business rule that requires an audit record to be created each time employees are added can be enforced using a DML statement trigger that inserts a row into an audit table after insert operations on the employees table. A statement trigger fires once for a whole DML statement while a row trigger fires once for each affected row. A row trigger may fire even if no rows are affected.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    Chapter 13 Solutions

    Uploaded by

    Funny Momentz
    267 views2 pages
    Triggers allow specified actions to be performed automatically within the database in response to events like DML operations or DDL statements. The most important thing to check before creating a database trigger is the recursive and cascading effects. When creating a DML statement trigger on a table, the timing, event, object name, and trigger body must be defined. A business rule that requires an audit record to be created each time employees are added can be enforced using a DML statement trigger that inserts a row into an audit table after insert operations on the employees table. A statement trigger fires once for a whole DML statement while a row trigger fires once for each affected row. A row trigger may fire even if no rows are affected.

    Original Description:

    These are the solutions to pl sql 11g Chapter 13-1

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Triggers allow specified actions to be performed automatically within the database in response to events like DML operations or DDL statements. The most important thing to check before creating a database trigger is the recursive and cascading effects. When creating a DML statement trigger on a table, the timing, event, object name, and trigger body must be defined. A business rule that requires an audit record to be created each time employees are added can be enforced using a DML statement trigger that inserts a row into an audit table after insert operations on the employees table. A statement trigger fires once for a whole DML statement while a row trigger fires once for each affected row. A row trigger may fire even if no rows are affected.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    267 views2 pages

    Chapter 13 Solutions

    Uploaded by

    Funny Momentz
    Triggers allow specified actions to be performed automatically within the database in response to events like DML operations or DDL statements. The most important thing to check before creating a database trigger is the recursive and cascading effects. When creating a DML statement trigger on a table, the timing, event, object name, and trigger body must be defined. A business rule that requires an audit record to be created each time employees are added can be enforced using a DML statement trigger that inserts a row into an audit table after insert operations on the employees table. A statement trigger fires once for a whole DML statement while a row trigger fires once for each affected row. A row trigger may fire even if no rows are affected.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 2

    Informatica II - Sursa Oracle Academy

    2010

    Tema 5 – PL/SQL
    1. What are triggers commonly used for in the database?
    Triggers allow specified actions to be performed automatically within the database, without
    having to write extra application code.

    2. List the types of events that cause a database trigger to fire?


    • DML operations on a table
    • DML operations on a view, with an INSTEAD OF trigger
    • DDL statements such as create and alter
    • Database system events, such as when a user logs on or the DBA shuts down the
    database

    3. What is the most important thing to check before creating a database trigger?
    The recursive and cascading effects.

    4. When creating a DML statement trigger on a table, what are the components that you must
    define?

    Timing; event; object_name and trigge_body

    5. A business rule states that each time one or more employees are added to the employees
    table, an audit record must also be created. This rule could be enforced using application
    code, but we have decided to enforce it using a DML statement trigger.
    A. Create an audit table by executing the following SQL statement:

    CREATE TABLE audit_table


    (action VARCHAR2(15),
    user_name VARCHAR2(30) DEFAULT USER,
    last_change_date TIMESTAMP DEFAULT SYSTIMESTAMP);

    B. Create a statement level trigger that inserts a row into the audit table immediately after one
    or more rows are added to the employees_dup table. The audit table row should contain value
    “Inserting” in the action column. The other two columns should have their default values.

    CREATE OR REPLACE TRIGGER employees_dup_change


    AFTER INSERT ON employees_dup
    BEGIN
    insert into audit_table (action, user_name, last_change_date)
    values ("inserting",user, systimestamp );
    end;

    C. Test your trigger by inserting a row into employees, then querying the audit table to see
    that it contains a row.
    Select * from audit_table;
    D. Make sure the trigger does not fire with a DELETE by deleting the employee you just
    entered. Recheck the audit_table table to make sure that there is not another new row.
    Informatica II - Sursa Oracle Academy
    2010
    6. What is the difference between a statement trigger and a row trigger?

    A statement trigger fires one time for the whole DML statement and a row trigger fires
    once for each row affected by the DML statement.

    7. A row trigger fires at least once even if no rows are affected. True or false?
    true.

    8. Imagine that the following four DML triggers have been defined on the employees table: a
    BEFORE INSERT statement trigger, a BEFORE UPDATE statement trigger, an AFTER
    UPDATE row trigger, and an AFTER DELETE statement trigger. An UPDATE statement
    updates three employee rows. How many times will each trigger fire?

    1 time the before update trigger, 3 times the after update row trigger, and 0 time the delete
    and insert statement trigger.

    You might also like