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.

Practice

1. What is the main purpose of an INSERT trigger in a database?

easy

A. To update existing rows in a table

B. To automatically run code when a new row is added to a table

C. To delete rows from a table

D. To create a new table

Solution

1. Step 1: Understand what triggers do

   Triggers are special procedures that run automatically in response to certain events in the database.

2. Step 2: Focus on INSERT triggers

   An INSERT trigger specifically runs when a new row is added to a table.

3. Final Answer:

   To automatically run code when a new row is added to a table -> Option B

4. Quick Check:

   INSERT trigger = runs on new row insert [OK]

Hint: INSERT triggers run on new rows added [OK]

Common Mistakes:

• Confusing INSERT triggers with UPDATE or DELETE triggers
• Thinking triggers create or drop tables
• Believing triggers run manually

2. Which of the following is the correct syntax to create an INSERT trigger in SQL?

easy

A. CREATE TRIGGER trg AFTER INSERT ON table_name FOR EACH ROW BEGIN ... END;

B. CREATE TRIGGER trg BEFORE DELETE ON table_name FOR EACH ROW BEGIN ... END;

C. CREATE TRIGGER trg AFTER UPDATE ON table_name FOR EACH ROW BEGIN ... END;

D. CREATE TRIGGER trg ON table_name INSERT BEGIN ... END;

Solution

1. Step 1: Identify correct event and timing

   For an INSERT trigger, the event is INSERT and timing can be BEFORE or AFTER. CREATE TRIGGER trg AFTER INSERT ON table_name FOR EACH ROW BEGIN ... END; uses AFTER INSERT which is valid.

2. Step 2: Check syntax structure

   CREATE TRIGGER trg AFTER INSERT ON table_name FOR EACH ROW BEGIN ... END; uses correct syntax: CREATE TRIGGER name AFTER INSERT ON table FOR EACH ROW BEGIN ... END;

3. Final Answer:

   CREATE TRIGGER trg AFTER INSERT ON table_name FOR EACH ROW BEGIN ... END; -> Option A

4. Quick Check:

   Correct INSERT trigger syntax = CREATE TRIGGER trg AFTER INSERT ON table_name FOR EACH ROW BEGIN ... END; [OK]

Hint: INSERT triggers use AFTER or BEFORE INSERT ON table [OK]

Common Mistakes:

• Using DELETE or UPDATE instead of INSERT
• Wrong order of keywords
• Missing FOR EACH ROW clause

3. Given this trigger code:

CREATE TRIGGER trg_log AFTER INSERT ON employees FOR EACH ROW BEGIN INSERT INTO log_table VALUES (NEW.id, NEW.name); END;

What happens when a new employee row is inserted?

medium

A. A new row is added to log_table with the inserted employee's id and name

B. The employees table is deleted

C. The trigger causes a syntax error

D. Nothing happens because NEW is invalid

Solution

1. Step 1: Understand the trigger action

   The trigger runs AFTER a new row is inserted into employees. It inserts a row into log_table using NEW.id and NEW.name.

2. Step 2: Confirm NEW usage

   NEW refers to the newly inserted row's values, so the insert into log_table uses correct values.

3. Final Answer:

   A new row is added to log_table with the inserted employee's id and name -> Option A

4. Quick Check:

   NEW accesses inserted row data [OK]

Hint: NEW accesses inserted row values inside INSERT triggers [OK]

Common Mistakes:

• Thinking NEW is invalid outside triggers
• Assuming trigger deletes data
• Confusing AFTER and BEFORE timing

4. Identify the error in this trigger:

CREATE TRIGGER trg_check BEFORE INSERT ON orders FOR EACH ROW BEGIN IF NEW.quantity < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Quantity cannot be negative'; END IF; END;

medium

A. Trigger must be AFTER INSERT, not BEFORE INSERT

B. NEW cannot be used in BEFORE INSERT triggers

C. SIGNAL SQLSTATE is invalid syntax

D. Missing delimiter to separate statements inside BEGIN...END

Solution

1. Step 1: Check trigger syntax for multiple statements

   Inside BEGIN...END, multiple statements need delimiters (like semicolons) or the delimiter must be changed before creating the trigger.

2. Step 2: Confirm other parts are valid

   NEW can be used in BEFORE INSERT triggers, SIGNAL SQLSTATE is valid in many SQL dialects, and BEFORE INSERT is allowed.

3. Final Answer:

   Missing delimiter to separate statements inside BEGIN...END -> Option D

4. Quick Check:

   Multiple statements need delimiters [OK]

Hint: Use delimiters inside BEGIN...END blocks [OK]

Common Mistakes:

• Forgetting to change delimiter before trigger creation
• Misusing NEW in triggers
• Confusing BEFORE and AFTER timing rules

5. You want to create an INSERT trigger that prevents inserting duplicate email addresses into a users table. Which approach is best?

hard

A. Use a trigger to insert the duplicate email into a log table

B. Use an AFTER INSERT trigger to delete the row if email is duplicate

C. Use a BEFORE INSERT trigger to check if NEW.email exists and raise an error if yes

D. Use a trigger to update the existing row with the new email

Solution

1. Step 1: Understand when to prevent duplicates

   To stop duplicates before they happen, use a BEFORE INSERT trigger to check if the email already exists.

2. Step 2: Why other options fail

   AFTER INSERT triggers run after insertion, so deleting duplicates after insert is inefficient and can cause issues. Updating existing rows or logging duplicates does not prevent duplicates.

3. Final Answer:

   Use a BEFORE INSERT trigger to check if NEW.email exists and raise an error if yes -> Option C

4. Quick Check:

   Prevent duplicates before insert with BEFORE INSERT trigger [OK]

Hint: Prevent duplicates before insert with BEFORE INSERT trigger [OK]

Common Mistakes:

• Trying to fix duplicates after insertion
• Using triggers to update instead of prevent
• Ignoring unique constraints and relying only on triggers