Triggers and Database Events Quiz

Last Updated :

Discuss

Comments

Question 1

A trigger is defined to execute AFTER an INSERT on the 'Students' table. Inside the trigger, you try to access the 'NEW.student_name'. What will be the value of 'NEW.student_name'?

A
The value of 'student_name' from the row that was just inserted
B
It will cause an error because 'NEW' can only be used with BEFORE triggers
C
The value of 'student_name' from the previously existing last row of the table
D
NULL, because the insertion has already completed

Question 2

You want to prevent any update that sets a student's score to a negative value. Which of the following trigger implementations is the most appropriate?

A
CREATE TRIGGER prevent_negative_marks BEFORE INSERT ON 'Marks'
FOR EACH ROW BEGIN IF NEW.score < 0 THEN SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Score cannot be negative.'; END IF; END;
B
CREATE TRIGGER prevent_negative_marks AFTER INSERT ON 'Marks'
FOR EACH ROW BEGIN IF NEW.score < 0 THEN DELETE FROM 'Marks'
WHERE id = NEW.id; END IF; END;
C
CREATE TRIGGER prevent_negative_marks AFTER UPDATE ON 'Marks'
FOR EACH ROW BEGIN IF NEW.score < 0 THEN UPDATE 'Marks'
SET score = OLD.score WHERE id = NEW.id; END IF; END;
D
CREATE TRIGGER prevent_negative_marks BEFORE UPDATE ON 'Marks'
FOR EACH ROW BEGIN IF NEW.score < 0 THEN SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Score cannot be negative.'; END IF; END;

Question 3

What is the primary difference between a DML trigger and a DDL trigger?

A
DML triggers respond to data changes like INSERT, UPDATE, DELETE, while DDL triggers respond to schema changes like CREATE, ALTER, DROP.
B
DML triggers are executed for each row, whereas DDL triggers are executed once per statement.
C
DML triggers can only be 'BEFORE' triggers, while DDL triggers can only be 'AFTER' triggers.
D
DDL triggers are used for managing transactions, while DML triggers are used for auditing.

Question 4

You have a trigger that fires on DELETE from the 'student_details' table. Inside this trigger, which of the following is true?

A
Neither 'OLD' nor 'NEW' keywords can be used inside a DELETE trigger.
B
The 'OLD' keyword can be used to access the data of the row being deleted, but the 'NEW' keyword is not available.
C
Both 'OLD' and 'NEW' keywords can be used and they contain the same data.
D
The 'NEW' keyword can be used to access the data of the row being deleted.

Question 5

Which of the following scenarios is a good use case for a Logon trigger?

A
To create a backup of a table before it is altered
B
To update a student's total marks whenever a new exam score is inserted.
C
To restrict a user's login access to specific hours of the day.
D
To prevent the 'students' table from being dropped from the database.

Question 6

In the context of database design for event management, what is the most appropriate relationship between 'Events' and 'Attendees'?

A
A one-to-many relationship from 'Attendees' to 'Events'.
B
A many-to-many relationship, implemented with a junction table (e.g., 'Event_Registrations').
C
A one-to-many relationship from 'Events' to 'Attendees'.
D
A one-to-one relationship.

Question 7

When designing a database for event management, why might you have a separate 'Venues' table?

A
To avoid data redundancy, as multiple events can be held at the same venue, and to store venue-specific information like capacity and address.
B
Because every table in a database must have a corresponding lookup table.
C
To ensure that each event has a unique location.
D
To store the schedule of all events.

Question 8

You have two triggers on the 'Students' table: 'trigger_A' is an AFTER INSERT trigger, and 'trigger_B' is a BEFORE INSERT trigger. If you execute an INSERT statement, in what order will they fire?

A
They will fire in alphabetical order of their names.
B
trigger_B will fire first, then the INSERT operation, then trigger_A.
C
The database will throw an error because you cannot have two triggers for the same event on one table.
D
trigger_A will fire first, then the INSERT operation, then trigger_B.

Question 9

If a BEFORE UPDATE trigger modifies a value using 'SET NEW.column_name = ...', what happens to the data in the table?

A
It will update the row twice: once with the original value and once with the trigger's value.
B
The original value from the UPDATE statement is saved, and the trigger's modification is ignored.
C
The modified value from the trigger is what gets saved to the table.
D
It will cause the trigger to fail because 'NEW' values are read-only.

Question 10

What is a potential risk of using triggers extensively in a database?

A
They can create complex and hard-to-debug chains of operations, where one trigger causes another to fire.
B
Triggers are inherently insecure and can be easily bypassed.
C
Triggers do not support transactional control and can leave data in an inconsistent state.
D
They can only be written in SQL, not in other programming languages.

Tags:

SQL

There are 10 questions to complete.

Take a part in the ongoing discussion