63 Questions
What type of operations can trigger stored procedures?
DML
When can triggers run implicitly?
DML and DDL operations
Which event can activate triggers in a database?
User logon
What is the syntax for a trigger function in SQL?
CREATE OR REPLACE TRIGGER BEFORE/AFTER UPDATE OR INSERT OR DELETE ON FOR EACH ROW
What is the purpose of a trigger in SQL?
To automatically perform a specified action when a certain event occurs in the database
Which type of trigger is fired after the triggering action has been completed?
AFTER trigger
Which type of trigger is based on a specific time occurrence?
Timing trigger
Which type of trigger is based on the state of data in a table?
Level trigger
Which type of trigger is based on a specific database event?
Event trigger
In PL/SQL triggers, what is the purpose of using conditional predicates?
To control the firing of the trigger based on specified conditions
What are the two triggers based upon firing level?
Row level and statement level
What is the defining characteristic of a row level trigger?
It fires once for each row affected by the triggering event
What is the defining characteristic of a statement level trigger?
It fires once per triggering event regardless of the rows modified
What is the defining difference between row level and statement level triggers?
Row level triggers operate on each affected row, while statement level triggers operate on the entire set of affected rows
What is the syntax for a row level trigger in SQL?
FOR EACH ROW
What is the syntax for a statement level trigger in SQL?
ON STATEMENT
What data can a trigger that fires at row level access using correlation names?
Data in the row it is processing
Which pseudorecords can be used by a row level trigger to access data in the row it is processing?
:NEW and :OLD
What is the purpose of correlation names in a trigger that fires at row level?
To reference data in the row being processed
What is the structure of :NEW and :OLD in PL/SQL triggers?
They have a structure like %ROWTYPE
How can a column be referenced using :OLD and :NEW in a trigger?
As predefined system variables
What is the purpose of :OLD and :NEW in a PL/SQL trigger?
To access the old and new values of the affected row
What is the syntax of a row level trigger that utilizes :OLD and :NEW?
CREATE OR REPLACE TRIGGER trigger_name BEFORE INSERT OR UPDATE OR DELETE ON table_name FOR EACH ROW
How can a column be referenced using :OLD and :NEW in a trigger?
Using :OLD.column_name and :NEW.column_name
What operations do INSTEAD OF triggers control?
Insert, update, merge, and delete
What can INSTEAD OF triggers also be used as?
Non-updatable views
What is the primary purpose of INSTEAD OF triggers?
To modify the behavior of views
What is the correct syntax for an INSTEAD OF INSERT trigger in PL/SQL?
CREATE OR REPLACE TRIGGER trigger_name INSTEAD OF INSERT ON table_name
What is the purpose of an INSTEAD OF INSERT trigger in PL/SQL?
To perform custom actions instead of the actual INSERT operation
Which pseudorecords can be used by an INSTEAD OF INSERT trigger in PL/SQL to access data?
:NEW
What type of events do DDL triggers respond to?
Schema modification events
What is the syntax for creating a DDL trigger in SQL?
CREATE TRIGGER trigger_name BEFORE event ON DATABASE
Which of the following are DDL events in a database?
CREATE
Which event in a database triggers the removal of a table and its data?
DROP & TRUNCATE
Which event in a database is used to provide privileges on database objects to users or roles?
GRANT
Which type of trigger option does not apply to DDL triggers?
FOR EACH ROW trigger
What is the difference between ON DATABASE and ON SCHEMA triggers in SQL?
ON DATABASE trigger fires regardless of the schema, while ON SCHEMA only fires in the same schema where the trigger was created
What does the ddl_trigger log when it is executed?
The name of the object, the type of DDL statement, and the time of execution
When does the ddl_trigger execute?
After a CREATE, ALTER, or DROP statement on the schema
When is a database trigger fired?
LOGON
Which event does a database trigger respond to?
LOGOFF
What is the syntax for database trigger in SQL?
CREATE OR REPLACE TRIGGER trigger_name BEFORE/AFTER {database event} on {DATABASE}
What is the syntax for dropping a trigger in SQL?
DROP TRIGGER trigger_name;
What is the purpose of :OLD and :NEW in a PL/SQL trigger?
To reference the previous and new values of a column affected by the triggering action
What is the syntax for disabling a trigger in PL/SQL?
ALTER TRIGGER trigger_name DISABLE;
What is the syntax for enabling a trigger in PL/SQL?
ALTER TRIGGER trigger_name ENABLE;
Which statement is true about disabling and enabling triggers in PL/SQL?
Disabling a trigger prevents it from firing in response to its triggering event.
What are user_triggers and user_resource in PL/SQL?
User_triggers are triggers created by a specific user, and user_resource refers to the resources available to that user
What is the purpose of defining execution orders for triggers with the same timing point?
To guarantee the order of execution for triggers with the same timing point
What is the syntax for creating a execution order of trigger
CREATE TRIGGER trigger_2 BEFORE INSERT ON table_name FOR EACH ROW follows trigger_1
What is a compound trigger allow in the context of database triggers?
A code for combining one or more timing points for a specific object in a single trigger
What is the primary purpose of an INSTEAD OF trigger in SQL?
To perform an action instead of the triggering action
When does a BEFORE statement trigger execute in PL/SQL?
Before each SQL statement is executed
What is the timing point for AFTER INSERT on a table?
After each row is inserted
What is the syntax for creating a compound trigger in PL/SQL?
CREATE OR REPLACE TRIGGER trigger_name FOR event ON COMPOUND TRIGGER BEGIN -- Trigger logic END trigger_name;
What is the defining characteristic of a compound trigger in PL/SQL?
It can be used to avoid mutating table errors.
When can compound triggers be used in PL/SQL?
To create triggers for handling complex scenarios and avoiding mutating table errors.
What is a mutating table exception?
An exception that occurs when trying to reference the triggering table in a query from within row-level trigger code
In which scenario does a mutating table exception occur?
When attempting to reference the triggering table in a query from within row-level trigger code
What action triggers a mutating table exception?
Referencing the triggering table in a query from within row-level trigger code
What should be included in a compound trigger to resolve a mutating table exception?
AFTER EACH ROW predicate of inserting or updating emp_audit
When updating a specific salary of an employee, what type of trigger is used to ensure the total_sal is updated after the statement?
AFTER STATEMENT trigger
What should be the timing point for the predicate of inserting or updating emp_audit in a compound trigger?
AFTER EACH ROW
Study Notes
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.
Make Your Own Quizzes and Flashcards
Convert your notes into interactive study material.
Get started for free
