This set of PL/SQL Multiple Choice Questions & Answers (MCQs) focuses on “PL/SQL Triggers”.
1. Triggers are automatically executed.
a) True
b) False
View Answer
Explanation: In PL/SQL, Triggers are already stored program that are automatically invoked or called when a specified event occurs. Triggers can be defined over a table, view, database etc. with which the event is associated.
2. Which of the following can execute a trigger in PL/SQL?
a) User
b) Oracle server
c) Both user and oracle server
d) Neither user nor oracle server
View Answer
Explanation: A trigger is automatically executed by the Oracle server whenever a specified event occurs. A user can create and store a trigger and specify when to execute it but the its execution is done by the server automatically.
3. Which of the following syntax is correct way to create a trigger in PL/SQL?
a)
CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF} {INSERT [OR] | UPDATE [OR] | DELETE} [OF TABLE_NAME] ON row_name [REFERENCING OLD AS o NEW AS n] [FOR EACH ROW] WHEN (condition)
b)
CREATE [OR REPLACE] TRIGGER {BEFORE | AFTER | INSTEAD OF} {INSERT [OR] | UPDATE [OR] | DELETE} [OF column_name] TABLE_NAME [REFERENCING OLD AS o NEW AS n] [FOR EACH ROW] WHEN (condition)
c)
CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF} {INSERT [OR] | UPDATE [OR] | DELETE} [OF column_name] ON TABLE_NAME [REFERENCING OLD AS o NEW AS n] [FOR EACH ROW] WHEN (condition)
d)
CREATE [OR REPLACE] trigger_name {BEFORE | AFTER | INSTEAD OF} {INSERT [OR] | UPDATE [OR] | DELETE} [OF column_name] ON TABLE_NAME [REFERENCING OLD AS o NEW AS n] [FOR EACH ROW] (condition)
Explanation: The correct syntax used for creating a trigger in PL/SQL is –
CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF} {INSERT [OR] | UPDATE [OR] | DELETE} [OF column_name] ON TABLE_NAME [REFERENCING OLD AS o NEW AS n] [FOR EACH ROW] WHEN (condition)
The CREATE OR REPLACE keyword is sued to create or replace a trigger. {BEFORE | AFTER | INSTEAD OF} is used to specify when the trigger will execute. {INSERT [OR] | UPDATE [OR] | DELETE} states the DML operation. Column name tells which column is to be updated. Table name is the name of the table on which trigger is formed. [FOR EACH ROW] is used to tell whether the trigger is row-level or table-level. At last the condition is stated when will execute the trigger. The entries inside the square brackets [] are not mandatory to write.
4. Which of the following types of statement can trigger a trigger execution?
a) Only DML statements
b) Only DDL statements
c) Only DCL statements
d) Both DML & DDL statements
View Answer
Explanation: Both the DML statements (DELETE, INSERT, UPDATE) and the DDL statements (CREATE, ALTER, DROP) are used to trigger a trigger execution. Triggers are written to be executed in response to any of these events including any database operation like LOGON, LOGOFF, SHUTDOWN etc.
5. Which keyword is used to recreate a trigger if it already exists?
a) Create
b) Replace
c) Recreate
d) Respawn
View Answer
Explanation: The Replace keyword is used to recreate a trigger if it already exists. The previous trigger will be deleted and the new one will take its place. The Create keyword is used to create a trigger if it is not already available.
6. Which of the following statement does not execute a trigger?
a) INSERT
b) UPDATE
c) SELECT
d) CREATE
View Answer
Explanation: The SELECT command does not execute a trigger. A trigger is only executed automatically by a DDL (CREATE, ALTER, DROP) statement or a DML (DELETE, INSERT, UPDATE) statement and since SELECT does not comes under any of this category, hence it does not execute a trigger.
7. Which of the following statement is false regarding Triggers?
a) Triggers does not accept parameters
b) Triggers does not return any value
c) Triggers are executed automatically
d) Triggers accept parameters
View Answer
Explanation: The statement which is false about Triggers is that they accept parameters. Triggers do not accept any kind of parameters. It only takes time of execution and on which statement, DDL or DML, it has to start execution.
8. How many times does a statement/table level trigger is executed for each transaction?
a) 1
b) 3
c) 5
d) infinite times
View Answer
Explanation: A statement/table-level trigger is executed whenever the specified event occurs on a table regardless of how many rows are affected. In other words, a statement-level trigger executes once for each transaction. We can create a statement trigger by omitting the FOR EACH ROW clause form the CREATE TRIGGER command.
9. Which of the following clause is used to create a row level trigger?
a) FOR ALL ROW
b) FOR EVERY ROW
c) FOR EACH ROW
d) FOR THIS ROW
View Answer
Explanation: The FOR EACH ROW clause is used to create a row level trigger. A row level trigger is a kind of trigger which is executed every time whenever an affected row is updated, inserted or deleted. The FOR EACH ROW is used in conjunction with the CREATE TRIGGER command.
10. Triggers can be used to prevent invalid transactions.
a) True
b) False
View Answer
Explanation: In PL/SQL, Triggers have various uses. One such use is to prevent invalid transaction. The user creates and stores a trigger such that whenever any invalid action is performed then the execution of trigger takes place which in-turn issues a warning or terminates the current invalid action.
Sanfoundry Global Education & Learning Series – PL/SQL.
To practice all areas of PL/SQL, here is complete set of 1000+ Multiple Choice Questions and Answers.
If you find a mistake in question / option / answer, kindly take a screenshot and email to [email protected]
