INSTEAD OF trigger for views in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a view named 'employee_view' selecting all columns from 'employees'.

PostgreSQL

CREATE VIEW employee_view AS SELECT [1] FROM employees;

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

A*

Bdepartment

Csalary

Did, name

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting only one column when all are needed.

Forgetting to specify the table name.

The * selects all columns from the table.

2fill in blank

medium

Complete the code to create an INSTEAD OF trigger function named 'employee_view_insert' for the view.

PostgreSQL

CREATE FUNCTION employee_view_insert() RETURNS trigger AS $$ BEGIN INSERT INTO employees VALUES ([1]); RETURN NEW; END; $$ LANGUAGE plpgsql;

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

ANEW.id, NEW.name, NEW.salary, NEW.department

Bid, NEW.name, NEW.salary, NEW.department

Cid, name, salary, department

DNEW.id

Attempts:

3 left

💡 Hint

Common Mistakes

Using column names without NEW. prefix.

Missing some columns in the insert statement.

The NEW keyword references the new row's columns in the trigger function.

3fill in blank

hard

Fix the error in the trigger creation by completing the code to attach the INSTEAD OF INSERT trigger to 'employee_view'.

PostgreSQL

CREATE TRIGGER employee_view_insert_trigger INSTEAD OF INSERT ON employee_view FOR EACH ROW EXECUTE FUNCTION [1]();

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

Ainsert_trigger

Bemployee_insert_trigger

Cinsert_employee

Demployee_view_insert

Attempts:

3 left

💡 Hint

Common Mistakes

Using a trigger name instead of the function name.

Misspelling the function name.

The trigger must call the function employee_view_insert defined earlier.

4fill in blank

hard

Fill both blanks to complete the INSTEAD OF UPDATE trigger function that updates the 'employees' table when the view is updated.

PostgreSQL

CREATE FUNCTION employee_view_update() RETURNS trigger AS $$ BEGIN UPDATE employees SET [1] = NEW.[2], salary = NEW.salary, department = NEW.department WHERE id = OLD.id; RETURN NEW; END; $$ LANGUAGE plpgsql;

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

Aname

Bid

Csalary

Ddepartment

Attempts:

3 left

💡 Hint

Common Mistakes

Using different column names for the SET and NEW parts.

Trying to update the primary key column.

The name column is updated by setting it to NEW.name.

5fill in blank

hard

Fill all three blanks to complete the INSTEAD OF DELETE trigger function that deletes from 'employees' when a row is deleted from the view.

PostgreSQL

CREATE FUNCTION employee_view_delete() RETURNS trigger AS $$ BEGIN DELETE FROM employees WHERE [1] = OLD.[2]; RETURN [3]; END; $$ LANGUAGE plpgsql;

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

Aid

COLD

DNEW

Attempts:

3 left

💡 Hint

Common Mistakes

Using NEW instead of OLD in a DELETE trigger.

Mismatching column names in WHERE clause.

The delete matches on id column and returns OLD row after deletion.