SQL Stored Procedures and Triggers Quiz
63 Questions
378 Views

Choose a study mode

Play Quiz
Study Flashcards
Spaced Repetition
Chat to lesson

Podcast

Play an AI-generated podcast conversation about this lesson

Questions and Answers

What type of operations can trigger stored procedures?

  • Server configuration
  • DML (correct)
  • Data retrieval
  • Database backup

    • When can triggers run implicitly?

  • DML and DDL operations (correct)
  • Application code execution
  • Server maintenance tasks
  • Database query operations

    • Which event can activate triggers in a database?

  • Data encryption
  • User logon (correct)
  • Server monitoring
  • Database indexing

    • What is the syntax for a trigger function in SQL?

    <p>CREATE OR REPLACE TRIGGER BEFORE/AFTER UPDATE OR INSERT OR DELETE ON FOR EACH ROW</p> Signup and view all the answers

    What is the purpose of a trigger in SQL?

    <p>To automatically perform a specified action when a certain event occurs in the database</p> Signup and view all the answers

    Which type of trigger is fired after the triggering action has been completed?

    <p>AFTER trigger</p> Signup and view all the answers

    Which type of trigger is based on a specific time occurrence?

    <p>Timing trigger</p> Signup and view all the answers

    Which type of trigger is based on the state of data in a table?

    <p>Level trigger</p> Signup and view all the answers

    Which type of trigger is based on a specific database event?

    <p>Event trigger</p> Signup and view all the answers

    In PL/SQL triggers, what is the purpose of using conditional predicates?

    <p>To control the firing of the trigger based on specified conditions</p> Signup and view all the answers

    What are the two triggers based upon firing level?

    <p>Row level and statement level</p> Signup and view all the answers

    What is the defining characteristic of a row level trigger?

    <p>It fires once for each row affected by the triggering event</p> Signup and view all the answers

    What is the defining characteristic of a statement level trigger?

    <p>It fires once per triggering event regardless of the rows modified</p> Signup and view all the answers

    What is the defining difference between row level and statement level triggers?

    <p>Row level triggers operate on each affected row, while statement level triggers operate on the entire set of affected rows</p> Signup and view all the answers

    What is the syntax for a row level trigger in SQL?

    <p>FOR EACH ROW</p> Signup and view all the answers

    What is the syntax for a statement level trigger in SQL?

    <p>ON STATEMENT</p> Signup and view all the answers

    What data can a trigger that fires at row level access using correlation names?

    <p>Data in the row it is processing</p> Signup and view all the answers

    Which pseudorecords can be used by a row level trigger to access data in the row it is processing?

    <p>:NEW and :OLD</p> Signup and view all the answers

    What is the purpose of correlation names in a trigger that fires at row level?

    <p>To reference data in the row being processed</p> Signup and view all the answers

    What is the structure of :NEW and :OLD in PL/SQL triggers?

    <p>They have a structure like %ROWTYPE</p> Signup and view all the answers

    How can a column be referenced using :OLD and :NEW in a trigger?

    <p>As predefined system variables</p> Signup and view all the answers

    What is the purpose of :OLD and :NEW in a PL/SQL trigger?

    <p>To access the old and new values of the affected row</p> Signup and view all the answers

    What is the syntax of a row level trigger that utilizes :OLD and :NEW?

    <p>CREATE OR REPLACE TRIGGER trigger_name BEFORE INSERT OR UPDATE OR DELETE ON table_name FOR EACH ROW</p> Signup and view all the answers

    How can a column be referenced using :OLD and :NEW in a trigger?

    <p>Using :OLD.column_name and :NEW.column_name</p> Signup and view all the answers

    What operations do INSTEAD OF triggers control?

    <p>Insert, update, merge, and delete</p> Signup and view all the answers

    What can INSTEAD OF triggers also be used as?

    <p>Non-updatable views</p> Signup and view all the answers

    What is the primary purpose of INSTEAD OF triggers?

    <p>To modify the behavior of views</p> Signup and view all the answers

    What is the correct syntax for an INSTEAD OF INSERT trigger in PL/SQL?

    <p>CREATE OR REPLACE TRIGGER trigger_name INSTEAD OF INSERT ON table_name</p> Signup and view all the answers

    What is the purpose of an INSTEAD OF INSERT trigger in PL/SQL?

    <p>To perform custom actions instead of the actual INSERT operation</p> Signup and view all the answers

    Which pseudorecords can be used by an INSTEAD OF INSERT trigger in PL/SQL to access data?

    <p>:NEW</p> Signup and view all the answers

    What type of events do DDL triggers respond to?

    <p>Schema modification events</p> Signup and view all the answers

    What is the syntax for creating a DDL trigger in SQL?

    <p>CREATE TRIGGER trigger_name BEFORE event ON DATABASE</p> Signup and view all the answers

    Which of the following are DDL events in a database?

    <p>CREATE</p> Signup and view all the answers

    Which event in a database triggers the removal of a table and its data?

    <p>DROP &amp; TRUNCATE</p> Signup and view all the answers

    Which event in a database is used to provide privileges on database objects to users or roles?

    <p>GRANT</p> Signup and view all the answers

    Which type of trigger option does not apply to DDL triggers?

    <p>FOR EACH ROW trigger</p> Signup and view all the answers

    What is the difference between ON DATABASE and ON SCHEMA triggers in SQL?

    <p>ON DATABASE trigger fires regardless of the schema, while ON SCHEMA only fires in the same schema where the trigger was created</p> Signup and view all the answers

    What does the ddl_trigger log when it is executed?

    <p>The name of the object, the type of DDL statement, and the time of execution</p> Signup and view all the answers

    When does the ddl_trigger execute?

    <p>After a CREATE, ALTER, or DROP statement on the schema</p> Signup and view all the answers

    When is a database trigger fired?

    <p>LOGON</p> Signup and view all the answers

    Which event does a database trigger respond to?

    <p>LOGOFF</p> Signup and view all the answers

    What is the syntax for database trigger in SQL?

    <p>CREATE OR REPLACE TRIGGER trigger_name BEFORE/AFTER {database event} on {DATABASE}</p> Signup and view all the answers

    What is the syntax for dropping a trigger in SQL?

    <p>DROP TRIGGER trigger_name;</p> Signup and view all the answers

    What is the purpose of :OLD and :NEW in a PL/SQL trigger?

    <p>To reference the previous and new values of a column affected by the triggering action</p> Signup and view all the answers

    What is the syntax for disabling a trigger in PL/SQL?

    <p>ALTER TRIGGER trigger_name DISABLE;</p> Signup and view all the answers

    What is the syntax for enabling a trigger in PL/SQL?

    <p>ALTER TRIGGER trigger_name ENABLE;</p> Signup and view all the answers

    Which statement is true about disabling and enabling triggers in PL/SQL?

    <p>Disabling a trigger prevents it from firing in response to its triggering event.</p> Signup and view all the answers

    What are user_triggers and user_resource in PL/SQL?

    <p>User_triggers are triggers created by a specific user, and user_resource refers to the resources available to that user</p> Signup and view all the answers

    What is the purpose of defining execution orders for triggers with the same timing point?

    <p>To guarantee the order of execution for triggers with the same timing point</p> Signup and view all the answers

    What is the syntax for creating a execution order of trigger

    <p>CREATE TRIGGER trigger_2 BEFORE INSERT ON table_name FOR EACH ROW follows trigger_1</p> Signup and view all the answers

    What is a compound trigger allow in the context of database triggers?

    <p>A code for combining one or more timing points for a specific object in a single trigger</p> Signup and view all the answers

    What is the primary purpose of an INSTEAD OF trigger in SQL?

    <p>To perform an action instead of the triggering action</p> Signup and view all the answers

    When does a BEFORE statement trigger execute in PL/SQL?

    <p>Before each SQL statement is executed</p> Signup and view all the answers

    What is the timing point for AFTER INSERT on a table?

    <p>After each row is inserted</p> Signup and view all the answers

    What is the syntax for creating a compound trigger in PL/SQL?

    <p>CREATE OR REPLACE TRIGGER trigger_name FOR event ON COMPOUND TRIGGER BEGIN -- Trigger logic END trigger_name;</p> Signup and view all the answers

    What is the defining characteristic of a compound trigger in PL/SQL?

    <p>It can be used to avoid mutating table errors.</p> Signup and view all the answers

    When can compound triggers be used in PL/SQL?

    <p>To create triggers for handling complex scenarios and avoiding mutating table errors.</p> Signup and view all the answers

    What is a mutating table exception?

    <p>An exception that occurs when trying to reference the triggering table in a query from within row-level trigger code</p> Signup and view all the answers

    In which scenario does a mutating table exception occur?

    <p>When attempting to reference the triggering table in a query from within row-level trigger code</p> Signup and view all the answers

    What action triggers a mutating table exception?

    <p>Referencing the triggering table in a query from within row-level trigger code</p> Signup and view all the answers

    What should be included in a compound trigger to resolve a mutating table exception?

    <p>AFTER EACH ROW predicate of inserting or updating emp_audit</p> Signup and view all the answers

    When updating a specific salary of an employee, what type of trigger is used to ensure the total_sal is updated after the statement?

    <p>AFTER STATEMENT trigger</p> Signup and view all the answers

    What should be the timing point for the predicate of inserting or updating emp_audit in a compound trigger?

    <p>AFTER EACH ROW</p> Signup and view all the answers

    Study Notes

    Studying That Suits You

    Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

    Quiz Team

    Description

    Test your knowledge of SQL stored procedures and triggers with this quiz. Explore the concepts of DML, DDL, user logon/log off, server errors, database startup, and instance shutdown triggers.

    More Like This

    PL/SQL Triggers Quiz
    5 questions

    PL/SQL Triggers Quiz

    CoolestSeal avatar
    CoolestSeal
    Quiz de objetos de base de datos en SQL Server
    10 questions

    Quiz de objetos de base de datos en SQL Server

    WieldyRoseQuartz avatar
    WieldyRoseQuartz
    Module 2 Triggers, Views, and Schema Modification Chapter 7 Outline Quiz
    12 questions

    DBMS Quiz: Assertion and Trigger Concepts in Module 2

    StateOfTheArtMonkey avatar
    StateOfTheArtMonkey
    SQL Triggers Fundamentals
    10 questions

    SQL Triggers Fundamentals

    RosyPrologue6564 avatar
    RosyPrologue6564
    Use Quizgecko on...
    Browser
    Open
    Browser
    Browser