SQL Stored Procedures and Triggers Quiz
63 Questions
380 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 (A)</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 (D)</p> Signup and view all the answers

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

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

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

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

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

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

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

<p>Event trigger (A)</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 (C)</p> Signup and view all the answers

What are the two triggers based upon firing level?

<p>Row level and statement level (D)</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 (A)</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 (C)</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 (C)</p> Signup and view all the answers

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

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

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

<p>ON STATEMENT (D)</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 (D)</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 (B)</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 (B)</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 (C)</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 (B)</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 (D)</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 (C)</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 (A)</p> Signup and view all the answers

What operations do INSTEAD OF triggers control?

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

What can INSTEAD OF triggers also be used as?

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

What is the primary purpose of INSTEAD OF triggers?

<p>To modify the behavior of views (C)</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 (C)</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 (D)</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 (B)</p> Signup and view all the answers

What type of events do DDL triggers respond to?

<p>Schema modification events (C)</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 (B)</p> Signup and view all the answers

Which of the following are DDL events in a database?

<p>CREATE (A)</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 (B)</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 (B)</p> Signup and view all the answers

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

<p>FOR EACH ROW trigger (D)</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 (D)</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 (D)</p> Signup and view all the answers

When does the ddl_trigger execute?

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

When is a database trigger fired?

<p>LOGON (C)</p> Signup and view all the answers

Which event does a database trigger respond to?

<p>LOGOFF (C)</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} (A)</p> Signup and view all the answers

What is the syntax for dropping a trigger in SQL?

<p>DROP TRIGGER trigger_name; (C)</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 (B)</p> Signup and view all the answers

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

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

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

<p>ALTER TRIGGER trigger_name ENABLE; (A)</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. (C)</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 (A)</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 (C)</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 (A)</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 (B)</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 (C)</p> Signup and view all the answers

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

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

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

<p>After each row is inserted (C)</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; (D)</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. (C)</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. (D)</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 (B)</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 (D)</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 (C)</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 (C)</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 (B)</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 (C)</p> Signup and view all the answers

Flashcards

Stored Procedure Trigger

A procedure that automatically executes in response to database events, such as INSERT, UPDATE, or DELETE operations.

Trigger Types

Triggers can be categorized as BEFORE or AFTER based on execution timing, and row or statement-level triggers focusing on the level of execution.

DML Trigger

Triggers activated by Data Manipulation Language (DML) operations like INSERT, UPDATE, DELETE.

DDL Trigger

Triggers activated by Data Definition Language (DDL) operations like CREATE, ALTER, or DROP.

Signup and view all the flashcards

Timing Trigger

Triggers that are based on specific time intervals.

Signup and view all the flashcards

Level Triggers

Triggers that focus on the level of execution – row-level or statement-level triggers.

Signup and view all the flashcards

Row-level vs Statement-level

Row-level triggers fire for each row affected, while statement-level triggers fire once for the entire statement.

Signup and view all the flashcards

FOR EACH ROW clause

Specifies that the trigger function should execute for each row affected by the triggering event.

Signup and view all the flashcards

:NEW record

Record containing the new values of the affected row in a row-level trigger.

Signup and view all the flashcards

:OLD record

Record containing the old values of the affected row in a row-level trigger.

Signup and view all the flashcards

INSTEAD OF trigger

Allows modification of the behavior of a view, like INSERT, UPDATE, DELETE, MERGE against a view.

Signup and view all the flashcards

Conditional Predicates

Conditions in a trigger that determine when the trigger should fire, based on specific criteria.

Signup and view all the flashcards

Event Trigger

Triggers fired based on specific database events, such as logon or logoff of a user

Signup and view all the flashcards

Trigger Syntax

CREATE TRIGGER trigger_name [BEFORE | AFTER] event ON table_name FOR EACH ROW, used to define a trigger in SQL

Signup and view all the flashcards

Dropping a Trigger

Using the command DROP TRIGGER, to remove a trigger definition from a database

Signup and view all the flashcards

Disabling a Trigger

Using the command ALTER TRIGGER, to prevent a trigger from firing, but keeping the definition in the database

Signup and view all the flashcards

Enabling a Trigger

Using ALTER TRIGGER to allow a disabled trigger to fire again.

Signup and view all the flashcards

Execution Order of Triggers

Defining the sequence in which triggers execute when multiple triggers are set for the same event

Signup and view all the flashcards

Pseudorecords

Predefined records used in triggers, like :NEW, :OLD, providing access to data modified during events.

Signup and view all the flashcards

SQL Trigger

Database object that automatically performs an action upon specified events.

Signup and view all the flashcards

ON DATABASE Trigger

Trigger fires on database-level events, affecting the entire database.

Signup and view all the flashcards

ON SCHEMA trigger

A trigger that only fires when a DDL event occurs within a specific schema.

Signup and view all the flashcards

DDL Events

Database schema modification actions, such as CREATE, ALTER, DROP.

Signup and view all the flashcards

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

Use Quizgecko on...
Browser
Open
Browser
Browser