INSERT trigger in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output after inserting a new row?

Given the table employees and the following INSERT trigger that logs new hires into hire_log, what rows will hire_log contain after inserting (id=3, name='Alice', department='Sales') into employees?

SQL

CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(50), department VARCHAR(50));
CREATE TABLE hire_log (emp_id INT, emp_name VARCHAR(50));

CREATE TRIGGER trg_after_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
  INSERT INTO hire_log (emp_id, emp_name) VALUES (NEW.id, NEW.name);
END;

INSERT INTO employees VALUES (3, 'Alice', 'Sales');

SELECT * FROM hire_log;

A (3, NULL)

B (NULL, NULL)

C (3, 'Alice')

D No rows (empty table)

💡 Hint

Think about what the trigger does after a new employee is inserted.

The AFTER INSERT trigger copies the new employee's id and name into the hire_log table. So after inserting Alice, hire_log will have one row with (3, 'Alice').

🧠 Conceptual

intermediate

Which statement about INSERT triggers is true?

Consider these statements about INSERT triggers in SQL. Which one is correct?

A An INSERT trigger automatically fires on UPDATE statements as well.

B An AFTER INSERT trigger can modify the row being inserted before it is saved.

C An INSERT trigger can only be defined on tables with a primary key.

D A BEFORE INSERT trigger can prevent the insertion by raising an error.

💡 Hint

Think about when BEFORE and AFTER triggers run and what they can do.

BEFORE INSERT triggers run before the row is inserted and can raise errors to prevent insertion. AFTER INSERT triggers run after insertion and cannot modify the inserted row. Triggers are not limited to tables with primary keys and do not fire on UPDATE unless defined for UPDATE.

📝 Syntax

advanced

Identify the syntax error in this INSERT trigger

Which option correctly fixes the syntax error in this trigger code?

SQL

CREATE TRIGGER trg_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  SET NEW.name = UPPER(NEW.name);
END;

A Add a semicolon after SET NEW.name = UPPER(NEW.name);

B Replace SET with SELECT

C Add a semicolon after END;

D Add a semicolon after BEGIN;

💡 Hint

Check statement termination inside the trigger body.

In SQL triggers, each statement inside BEGIN...END must end with a semicolon. Missing it after SET causes syntax error.

❓ optimization

advanced

Optimize this INSERT trigger to avoid redundant logging

This trigger logs every inserted employee into hire_log. How can you optimize it to log only employees from the 'Engineering' department?

SQL

CREATE TRIGGER trg_after_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
  INSERT INTO hire_log (emp_id, emp_name) VALUES (NEW.id, NEW.name);
END;

A Add IF NEW.department = 'Engineering' THEN before the INSERT statement and END IF after it.

B Add WHERE department = 'Engineering' in the INSERT statement.

C Change AFTER INSERT to BEFORE INSERT and add a condition.

D Add a CHECK constraint on hire_log for department = 'Engineering'.

💡 Hint

Use conditional logic inside the trigger body.

You can use an IF statement inside the trigger to check the department before inserting into hire_log. WHERE clause is invalid in INSERT VALUES. BEFORE INSERT timing change is unrelated. CHECK constraints do not filter inserts.

🔧 Debug

expert

Why does this INSERT trigger cause a recursion error?

This trigger is defined on the employees table and inserts into employees inside the trigger body. What is the cause of the recursion error?

SQL

CREATE TRIGGER trg_recursive
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
  INSERT INTO employees (id, name, department) VALUES (NEW.id + 1000, NEW.name, NEW.department);
END;

A The trigger syntax is invalid because AFTER INSERT triggers cannot insert rows.

B The trigger causes infinite recursion by inserting into the same table it listens to.

C The trigger causes a deadlock because it locks the employees table twice.

D The trigger fails because NEW.id + 1000 is not a valid expression.

💡 Hint

Think about what happens when the trigger inserts into the same table it is triggered on.

The trigger fires after an insert on employees, then inserts another row into employees, which fires the trigger again, causing infinite recursion.