Test Your MySQL DB Trigger Knowledge with Our SQL Quiz

Progress

True / False

trigger in MySQL can be created to automatically update a table before an INSERT operation is performed.

True False

MySQL triggers can be set to activate before or after INSERT, UPDATE, or DELETE operations. A trigger can perform actions before the specified operation is executed.

True / False

You can only have one trigger for each type of operation (before/after insert/update/delete) on a single table in MySQL.

True False

MySQL allows multiple triggers of the same type (e.g., multiple BEFORE INSERT triggers) on a single table, as long as they have unique names.

True / False

Triggers in MySQL can be used to enforce data integrity by validating data before it is committed to the database.

True False

Triggers can be used to validate data and ensure it meets certain criteria before changes are applied to the database, thus helping enforce data integrity.

True / False

Triggers in MySQL can directly call other triggers.

True False

MySQL does not support direct triggering of other triggers. However, actions within a trigger can lead to changes that might activate other triggers if they are set on the same table.

True / False

MySQL, a BEFORE UPDATE trigger can modify the values of the row being updated.

True False

BEFORE UPDATE trigger can modify the values of the row being updated before the update operation is completed.

True / False

Triggers in MySQL can be used to automatically log changes made to a table by inserting records into an audit table.

True False

Triggers can be designed to log changes to an audit table, capturing details of operations performed on the original table.

True / False

MySQL triggers can only be used to manipulate data within the same table where the trigger is defined.

True False

Triggers can manipulate data in other tables as well, not just the table where the trigger is defined, though this should be done carefully to avoid unintended consequences.

True / False

MySQL, you can create triggers that run after an INSERT operation on a view.

True False

Triggers in MySQL can only be created for tables, not for views.

True / False

The OLD and NEW keywords in MySQL triggers refer to the values of the rows before and after an UPDATE operation, respectively

True False

In a trigger, OLD refers to the row’s values before the change, and NEW refers to the row’s values after the change, particularly in UPDATE triggers.

Q10

True / False

MySQL, a trigger created with the BEFORE DELETE timing cannot prevent the deletion of rows from a table.

True False

BEFORE DELETE trigger can indeed prevent the deletion of rows by raising an error or taking some other action to cancel the operation.

Q11

Single Choice

What is a MySQL trigger?

database backup mechanism procedure that is automatically executed in response to certain events on a particular table or view method to synchronize databasesA tool for database performance optimization

MySQL trigger is a database object that is automatically executed or fired when certain events occur on a particular table or view in the database.

Q12

Single Choice

Which of the following events can MySQL triggers be associated with

SELECT Update ConnectDisconnect

MySQL triggers can be associated with DML events like INSERT, UPDATE, and DELETE. They cannot be triggered by SELECT, CONNECT, or DISCONNECT events.

Q13

Single Choice

What keyword is used to create a MySQL trigger?

CREATE PROCEDURE CREATE TRIGGER CREATE FUNCTIONCREATE EVENT

The CREATE TRIGGER statement is used to create a new trigger in MySQL.

Q14

Single Choice

When creating a MySQL trigger, which clause specifies the timing of the trigger?

FOR EACH ROW BEFORE or AFTER ON TABLEWHEN

The BEFORE or AFTER clause specifies whether the trigger action should be executed before or after the triggering event.

Q15

Single Choice

Which of the following is not a valid MySQL trigger timing?

BEFORE INSERT AFTER UPDATE BEFORE DELETEAFTER SELECT

MySQL triggers cannot be created for SELECT events. Valid timings are BEFORE INSERT, AFTER INSERT, BEFORE UPDATE, AFTER UPDATE, BEFORE DELETE, and AFTER DELETE.

Q16

Single Choice

How many triggers can be created on a single table for the same event and timing in MySQL?

One Two ThreeUnlimited

Only one trigger can be created per table for the same event and timing (e.g., only one BEFORE INSERT trigger per table).

Q17

Single Choice

Which of the following is true about row-level triggers in MySQL?

They execute for each row affected by the trigger event They execute once per statement, regardless of how many rows are affected They are not supported in MySQLThey can only be created on views

Row-level triggers execute for each row affected by the trigger event, as specified by the FOR EACH ROW clause.

Q18

Single Choice

What are the NEW and OLD keywords used for in MySQL triggers?

They refer to new and old data types They are placeholders for the new and old values of the row being inserted, updated, or deleted They specify the age of the dataThey are used to create temporary tables

MySQL triggers, NEW and OLD are used as placeholders to refer to the new and old values of the row being inserted, updated, or deleted, respectively.

Q19

Single Choice

How can you prevent a trigger from causing a loop in MySQL?

By disabling recursive triggers By using the STOP LOOP command By ensuring the trigger does not modify the same table it is defined onBy limiting the trigger execution time

To prevent a trigger from causing a loop, ensure the trigger does not modify the same table it is defined on, thereby avoiding recursive trigger execution.

Q20

Single Choice

Which MySQL statement would correctly create a trigger that sets the created_at field to the current timestamp whenever a new row is inserted into the users table?

CREATE TRIGGER before_insert_users BEFORE INSERT ON users FOR EACH ROW SET NEW.created_at = NOW() CREATE TRIGGER before_insert_users BEFORE INSERT ON users FOR EACH ROW BEGIN SET NEW.created_at = NOW(); END; CREATE TRIGGER before_insert_users AFTER INSERT ON users FOR EACH ROW SET NEW.created_at = NOW();CREATE TRIGGER before_insert_users BEFORE INSERT ON users FOR EACH ROW SET OLD.created_at = NOW();

The correct statement includes the BEFORE INSERT timing, the table name, and the correct syntax to set the NEW value for the created_at field using the NOW() function. The statement should be wrapped in a BEGIN and END block for proper execution of multiple statements.

Q21

Multiple Choice

Identify the correct SQL statement to create a trigger that logs changes to a table after an update.

SQL Code

CREATE TRIGGER log_update
AFTER UPDATE ON employees
FOR EACH ROW
INSERT INTO employee_log (employee_id, old_salary, new_salary)
VALUES (OLD.employee_id, OLD.salary, NEW.salary);

CREATE TRIGGER log_update AFTER UPDATE ON employees FOR EACH ROW INSERT INTO employee_log (employee_id, old_salary, new_salary) VALUES (OLD.employee_id, OLD.salary, NEW.salary); CREATE TRIGGER log_update BEFORE UPDATE ON employees FOR EACH ROW INSERT INTO employee_log (employee_id, old_salary, new_salary) VALUES (OLD.employee_id, OLD.salary, NEW.salary); CREATE TRIGGER log_update AFTER UPDATE ON employees FOR EACH ROW INSERT INTO employee_log (employee_id, old_salary, new_salary) VALUES (NEW.employee_id, OLD.salary, NEW.salary);CREATE TRIGGER log_update AFTER UPDATE ON employees FOR EACH ROW INSERT INTO employee_log (employee_id, old_salary, new_salary) VALUES (OLD.employee_id, NEW.salary);

This trigger captures changes made to the employees table and logs the old and new salary values in the employee_log table.

Q22

Multiple Choice

Choose the correct SQL statement to create a BEFORE INSERT trigger that checks for duplicate entries.

SQL Code

CREATE TRIGGER prevent_duplicates
BEFORE INSERT ON customers
FOR EACH ROW
BEGIN
IF EXISTS (SELECT 1 FROM customers WHERE email = NEW.email) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Duplicate entry';
END IF;
END;

CREATE TRIGGER prevent_duplicates BEFORE INSERT ON customers FOR EACH ROW BEGIN IF EXISTS (SELECT 1 FROM customers WHERE email = NEW.email) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Duplicate entry'; END IF; END; CREATE TRIGGER prevent_duplicates BEFORE INSERT ON customers FOR EACH ROW IF EXISTS (SELECT 1 FROM customers WHERE email = NEW.email) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Duplicate entry'; END IF; CREATE TRIGGER prevent_duplicates AFTER INSERT ON customers FOR EACH ROW IF EXISTS (SELECT 1 FROM customers WHERE email = NEW.email) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Duplicate entry'; END IF;CREATE TRIGGER prevent_duplicates BEFORE INSERT ON customers FOR EACH ROW BEGIN IF NEW.email IN (SELECT email FROM customers) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Duplicate entry'; END IF; END;

This BEFORE INSERT trigger prevents the insertion of a duplicate email in the customers table by raising an error.

Q23

Multiple Choice

Determine the correct SQL statement to create a trigger that updates a summary table after a DELETE operation.

SQL Code

CREATE TRIGGER update_summary
AFTER DELETE ON sales
FOR EACH ROW
UPDATE sales_summary
SET total_sales = total_sales - OLD.amount;

CREATE TRIGGER update_summary AFTER DELETE ON sales FOR EACH ROW UPDATE sales_summary SET total_sales = total_sales - OLD.amount; CREATE TRIGGER update_summary AFTER DELETE ON sales FOR EACH ROW SET total_sales = total_sales - OLD.amount; CREATE TRIGGER update_summary BEFORE DELETE ON sales FOR EACH ROW UPDATE sales_summary SET total_sales = total_sales - OLD.amount;CREATE TRIGGER update_summary AFTER DELETE ON sales FOR EACH ROW UPDATE sales_summary SET total_sales = total_sales + OLD.amount;

This AFTER DELETE trigger adjusts the total_sales in the sales_summary table by subtracting the amount of the deleted sale.

Q24

Multiple Choice

Which SQL statement correctly creates a BEFORE DELETE trigger that checks for dependent records?

SQL Code

CREATE TRIGGER check_dependents
BEFORE DELETE ON products
FOR EACH ROW
BEGIN
IF EXISTS (SELECT 1 FROM order_details WHERE product_id = OLD.product_id) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete product with orders';
END IF;
END;

CREATE TRIGGER check_dependents BEFORE DELETE ON products FOR EACH ROW BEGIN IF EXISTS (SELECT 1 FROM order_details WHERE product_id = OLD.product_id) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete product with orders'; END IF; END; CREATE TRIGGER check_dependents BEFORE DELETE ON products FOR EACH ROW IF EXISTS (SELECT 1 FROM order_details WHERE product_id = OLD.product_id) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete product with orders'; END IF; CREATE TRIGGER check_dependents AFTER DELETE ON products FOR EACH ROW IF EXISTS (SELECT 1 FROM order_details WHERE product_id = OLD.product_id) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete product with orders'; END IF;CREATE TRIGGER check_dependents BEFORE DELETE ON products FOR EACH ROW BEGIN IF OLD.product_id IN (SELECT product_id FROM order_details) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete product with orders'; END IF; END;

This BEFORE DELETE trigger prevents the deletion of a product if it has associated records in the order_details table.

Q25

Multiple Choice

Select the correct SQL statement to create an AFTER INSERT trigger that automatically updates a status column.

SQL Code

CREATE TRIGGER update_status
AFTER INSERT ON orders
FOR EACH ROW
UPDATE orders
SET status = 'Processing'
WHERE order_id = NEW.order_id;

CREATE TRIGGER update_status AFTER INSERT ON orders FOR EACH ROW UPDATE orders SET status = 'Processing' WHERE order_id = NEW.order_id; CREATE TRIGGER update_status BEFORE INSERT ON orders FOR EACH ROW UPDATE orders SET status = 'Processing' WHERE order_id = NEW.order_id; CREATE TRIGGER update_status AFTER INSERT ON orders FOR EACH ROW SET status = 'Processing' WHERE order_id = NEW.order_id;CREATE TRIGGER update_status AFTER INSERT ON orders FOR EACH ROW UPDATE orders SET status = 'Processing';

This AFTER INSERT trigger automatically sets the status of a new order to 'Processing' after it is inserted.

Q26

Multiple Choice

Determine the correct SQL statement to create a trigger that prevents deletion of records based on a complex condition.

SQL Code

CREATE TRIGGER prevent_deletion
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
IF OLD.role = 'Manager' AND OLD.years_of_service > 10 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete long-serving managers';
END IF;
END;

CREATE TRIGGER prevent_deletion BEFORE DELETE ON employees FOR EACH ROW BEGIN IF OLD.role = 'Manager' AND OLD.years_of_service > 10 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete long-serving managers'; END IF; END; CREATE TRIGGER prevent_deletion AFTER DELETE ON employees FOR EACH ROW BEGIN IF OLD.role = 'Manager' AND OLD.years_of_service > 10 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete long-serving managers'; END IF; END; CREATE TRIGGER prevent_deletion BEFORE DELETE ON employees FOR EACH ROW BEGIN IF OLD.role = 'Manager' AND OLD.years_of_service > 10 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete managers'; END IF; END;CREATE TRIGGER prevent_deletion BEFORE DELETE ON employees FOR EACH ROW IF OLD.role = 'Manager' AND OLD.years_of_service > 10 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete long-serving managers'; END;

This trigger prevents the deletion of employees who are managers with more than 10 years of service, enforcing business rules at the database level.

Q27

Multiple Choice

Which SQL statement correctly creates a BEFORE UPDATE trigger that ensures data integrity across related tables?

SQL Code

CREATE TRIGGER ensure_integrity
BEFORE UPDATE ON orders
FOR EACH ROW
BEGIN
IF NEW.customer_id <> OLD.customer_id THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Customer ID cannot be changed';
END IF;
END;

CREATE TRIGGER ensure_integrity BEFORE UPDATE ON orders FOR EACH ROW BEGIN IF NEW.customer_id <> OLD.customer_id THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Customer ID cannot be changed'; END IF; END; CREATE TRIGGER ensure_integrity AFTER UPDATE ON orders FOR EACH ROW BEGIN IF NEW.customer_id <> OLD.customer_id THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Customer ID cannot be changed'; END IF; END; CREATE TRIGGER ensure_integrity BEFORE UPDATE ON orders FOR EACH ROW IF NEW.customer_id <> OLD.customer_id THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Customer ID cannot be changed';CREATE TRIGGER ensure_integrity BEFORE UPDATE ON orders FOR EACH ROW BEGIN IF NEW.customer_id <> OLD.customer_id THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Customer ID change detected'; END IF; END;

This BEFORE UPDATE trigger prevents changes to the customer_id in the orders table, maintaining data integrity between orders and customers.

Q28

Multiple Choice

Identify the correct SQL statement to create a trigger that logs changes to multiple columns in a table.

SQL Code

CREATE TRIGGER log_changes
AFTER UPDATE ON inventory
FOR EACH ROW
BEGIN
IF OLD.price <> NEW.price THEN
INSERT INTO inventory_log (item_id, old_price, new_price)
VALUES (OLD.item_id, OLD.price, NEW.price);
END IF;
IF OLD.stock <> NEW.stock THEN
INSERT INTO inventory_log (item_id, old_stock, new_stock)
VALUES (OLD.item_id, OLD.stock, NEW.stock);
END IF;
END;

CREATE TRIGGER log_changes AFTER UPDATE ON inventory FOR EACH ROW BEGIN IF OLD.price <> NEW.price THEN INSERT INTO inventory_log (item_id, old_price, new_price) VALUES (OLD.item_id, OLD.price, NEW.price); END IF; IF OLD.stock <> NEW.stock THEN INSERT INTO inventory_log (item_id, old_stock, new_stock) VALUES (OLD.item_id, OLD.stock, NEW.stock); END IF; END; CREATE TRIGGER log_changes AFTER UPDATE ON inventory FOR EACH ROW INSERT INTO inventory_log (item_id, old_price, new_price, old_stock, new_stock) VALUES (OLD.item_id, OLD.price, NEW.price, OLD.stock, NEW.stock); CREATE TRIGGER log_changes BEFORE UPDATE ON inventory FOR EACH ROW IF OLD.price <> NEW.price OR OLD.stock <> NEW.stock THEN INSERT INTO inventory_log (item_id, old_price, new_price, old_stock, new_stock) VALUES (OLD.item_id, OLD.price, NEW.price, OLD.stock, NEW.stock);CREATE TRIGGER log_changes AFTER UPDATE ON inventory FOR EACH ROW IF OLD.price <> NEW.price OR OLD.stock <> NEW.stock THEN INSERT INTO inventory_log (item_id, old_price, new_price, old_stock, new_stock) VALUES (OLD.item_id, OLD.price, NEW.price, OLD.stock, NEW.stock);

This AFTER UPDATE trigger logs changes to both the price and stock columns in the inventory table, capturing any modifications made.

Q29

Multiple Choice

Select the correct SQL statement to create a trigger that synchronizes data between two related tables.

SQL Code

CREATE TRIGGER sync_data
AFTER INSERT ON orders
FOR EACH ROW
INSERT INTO order_summary (order_id, order_total)
VALUES (NEW.order_id, NEW.total);

CREATE TRIGGER sync_data AFTER INSERT ON orders FOR EACH ROW INSERT INTO order_summary (order_id, order_total) VALUES (NEW.order_id, NEW.total); CREATE TRIGGER sync_data BEFORE INSERT ON orders FOR EACH ROW INSERT INTO order_summary (order_id, order_total) VALUES (NEW.order_id, NEW.total); CREATE TRIGGER sync_data AFTER INSERT ON orders FOR EACH ROW INSERT INTO order_summary (order_id, order_total) VALUES (NEW.order_id, NEW.total) ON DUPLICATE KEY UPDATE order_total = NEW.total;CREATE TRIGGER sync_data AFTER INSERT ON orders FOR EACH ROW UPDATE order_summary SET order_total = NEW.total WHERE order_id = NEW.order_id;

This AFTER INSERT trigger ensures that every new order inserted into the orders table is also recorded in the order_summary table, keeping both tables in sync.

Q30

Multiple Choice

Determine the correct SQL statement to create a trigger that prevents unauthorized changes to a critical column.

SQL Code

CREATE TRIGGER prevent_unauthorized_change
BEFORE UPDATE ON accounts
FOR EACH ROW
BEGIN
IF NEW.balance <> OLD.balance THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Unauthorized balance change';
END IF;
END;

CREATE TRIGGER prevent_unauthorized_change BEFORE UPDATE ON accounts FOR EACH ROW BEGIN IF NEW.balance <> OLD.balance THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Unauthorized balance change'; END IF; END; CREATE TRIGGER prevent_unauthorized_change AFTER UPDATE ON accounts FOR EACH ROW BEGIN IF NEW.balance <> OLD.balance THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Unauthorized balance change'; END IF; END; CREATE TRIGGER prevent_unauthorized_change BEFORE UPDATE ON accounts FOR EACH ROW IF NEW.balance <> OLD.balance THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Unauthorized balance change';CREATE TRIGGER prevent_unauthorized_change BEFORE UPDATE ON accounts FOR EACH ROW BEGIN IF OLD.balance <> NEW.balance THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Unauthorized balance change detected'; END IF; END;

This BEFORE UPDATE trigger prevents changes to the balance column in the accounts table unless authorized, ensuring the integrity of financial data.

MySQL Database Quiz Questions