AFTER trigger execution in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create an AFTER INSERT trigger on the employees table.

SQL

CREATE TRIGGER trg_after_insert ON employees
AFTER [1]
AS
BEGIN
  PRINT 'Employee inserted';
END;

Drag options to blanks, or click blank then click option'

ADELETE

BUPDATE

CINSERT

DSELECT

Attempts:

3 left

💡 Hint

Common Mistakes

Using UPDATE or DELETE instead of INSERT for an insert trigger.

Trying to use SELECT which is not a trigger event.

The trigger is set to run after an INSERT operation on the employees table.

2fill in blank

medium

Complete the code to reference the inserted row in an AFTER INSERT trigger.

SQL

CREATE TRIGGER trg_after_insert_salary ON employees
AFTER INSERT
AS
BEGIN
  SELECT salary FROM [1];
END;

Drag options to blanks, or click blank then click option'

Adeleted

Binserted

Cemployees

Dnew_rows

Attempts:

3 left

💡 Hint

Common Mistakes

Using deleted instead of inserted.

Using the base table name instead of the inserted pseudo-table.

In AFTER INSERT triggers, the inserted table holds the new rows.

3fill in blank

hard

Fix the error in the AFTER DELETE trigger code to correctly reference the deleted rows.

SQL

CREATE TRIGGER trg_after_delete ON employees
AFTER DELETE
AS
BEGIN
  SELECT * FROM [1];
END;

Drag options to blanks, or click blank then click option'

Adeleted

Bemployees

Cremoved

Dinserted

Attempts:

3 left

💡 Hint

Common Mistakes

Using inserted in a DELETE trigger.

Using the base table name instead of the deleted pseudo-table.

The deleted table holds the rows that were removed in a DELETE trigger.

4fill in blank

hard

Fill both blanks to create an AFTER UPDATE trigger that logs old and new salaries.

SQL

CREATE TRIGGER trg_after_update_salary ON employees
AFTER UPDATE
AS
BEGIN
  INSERT INTO salary_log (old_salary, new_salary)
  SELECT [1].salary, [2].salary
  FROM deleted [1], inserted [2]
  WHERE [1].employee_id = [2].employee_id;
END;

Drag options to blanks, or click blank then click option'

Ad

Bi

Cold

Dnew

Attempts:

3 left

💡 Hint

Common Mistakes

Using full table names without aliases.

Mixing up inserted and deleted aliases.

Aliases d and i are used for deleted and inserted tables to compare old and new values.

5fill in blank

hard

Fill all three blanks to create an AFTER INSERT trigger that updates a summary table with the new employee count.

SQL

CREATE TRIGGER trg_after_insert_count ON employees
AFTER INSERT
AS
BEGIN
  UPDATE employee_summary
  SET total_employees = total_employees + [1]
  WHERE department_id = (SELECT [2] FROM inserted LIMIT 1);
  PRINT '[3] employees added';
END;

Drag options to blanks, or click blank then click option'

A1

Bdepartment_id

DNew

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong column names.

Forgetting to update the correct department.

The trigger adds 1 to the count, uses department_id from inserted, and prints a message.