Why triggers are needed in SQL - Challenge Your Understanding

Challenge - 5 Problems

🧠 Conceptual

intermediate

Purpose of Database Triggers

Why are triggers used in a database system?

A To manually update data by users through forms

B To speed up database queries by indexing columns

C To create backup copies of the entire database daily

D To automatically perform actions in response to certain changes in the database

💡 Hint

Think about automatic reactions inside the database when data changes.

Triggers are special procedures that run automatically when certain events happen in the database, like inserting or updating data. They help keep data consistent and enforce rules without manual intervention.

🧠 Conceptual

intermediate

When to Use Triggers

Which situation best shows why a trigger is useful?

A Creating a new table for storing data

B Writing a report based on user input

C Automatically recording the time when a record is updated

D Running a query to find the highest salary

💡 Hint

Think about automatic tracking of changes without user action.

Triggers can automatically record information like update times whenever data changes, ensuring accurate and consistent tracking without relying on users to enter this data.

❓ query_result

advanced

Effect of a Trigger on Insert

Given a trigger that sets a column 'created_at' to the current timestamp on insert, what will be the value of 'created_at' after inserting a new row without specifying it?

SQL

CREATE TRIGGER set_created_at BEFORE INSERT ON users FOR EACH ROW BEGIN SET NEW.created_at = CURRENT_TIMESTAMP; END;

INSERT INTO users (name) VALUES ('Alice');

SELECT created_at FROM users WHERE name = 'Alice';

A The current date and time when the row was inserted

B The default value of 'created_at' column if defined

C An error occurs because 'created_at' is missing

D NULL because 'created_at' was not specified in the insert

💡 Hint

Triggers can set values automatically even if not provided in the insert.

The trigger runs before the insert and sets 'created_at' to the current timestamp, so the inserted row will have the current date and time recorded.

🧠 Conceptual

advanced

Triggers and Data Integrity

How do triggers help maintain data integrity in a database?

A By allowing users to bypass constraints during data entry

B By automatically enforcing rules and constraints when data changes

C By deleting all related data when a record is updated

D By slowing down queries to prevent too many changes

💡 Hint

Triggers act like automatic guards for data rules.

Triggers can check and enforce rules automatically whenever data is inserted, updated, or deleted, helping keep the data accurate and consistent.

🧠 Conceptual

expert

Drawbacks of Using Triggers

What is a common drawback of using triggers in a database?

A They can make debugging harder because actions happen automatically

B They always improve query speed significantly

C They prevent any data from being deleted

D They require manual activation by users

💡 Hint

Think about how automatic actions might hide what is happening.

Because triggers run automatically behind the scenes, it can be difficult to track what caused certain changes, making debugging and understanding database behavior more complex.