Test Your PostgreSQL DB Trigger Knowledge with this Quiz

Progress

True / False

A trigger in PostgreSQL can automatically execute a specified function when a certain event occurs.

True False

A trigger in PostgreSQL is designed to automatically execute a specified function when a certain event (such as INSERT, UPDATE, or DELETE) occurs on a table.

True / False

Triggers in PostgreSQL are created using the CREATE TRIGGER command.

True False

The CREATE TRIGGER command is used to create triggers in PostgreSQL.

True / False

PostgreSQL triggers cannot be used to enforce business rules.

True False

Triggers in PostgreSQL can indeed be used to enforce business rules by executing custom functions in response to specific events.

True / False

A trigger function in PostgreSQL must return a TRIGGER data type.

True False

A trigger function in PostgreSQL must return the TRIGGER data type to indicate that it is intended to be used as a trigger.

True / False

Triggers in PostgreSQL can only be fired before an event occurs.

True False

Triggers in PostgreSQL can be fired before or after an event occurs, depending on how they are defined.

True / False

It is possible to create row-level and statement-level triggers in PostgreSQL.

True False

PostgreSQL supports both row-level triggers, which execute for each row affected by an event, and statement-level triggers, which execute once for the entire event.

True / False

PostgreSQL allows creating triggers that fire on TRUNCATE statements.

True False

PostgreSQL supports triggers that can fire on TRUNCATE statements, allowing actions to be taken when a table is truncated.

True / False

You can use the ORACLE-specific syntax for creating triggers in PostgreSQL without modification.

True False

PostgreSQL and ORACLE have different syntax for creating triggers. The ORACLE-specific syntax cannot be used directly in PostgreSQL without modification.

True / False

In PostgreSQL, triggers can be deferred to execute at the end of the transaction.

True False

PostgreSQL supports deferred triggers, which can be set to execute at the end of the transaction rather than immediately after the triggering event.

Q10

True / False

Migrating triggers from ORACLE to PostgreSQL typically involves rewriting the trigger function and adjusting the trigger definition syntax.

True False

When migrating triggers from ORACLE to PostgreSQL, the trigger function often needs to be rewritten to match PostgreSQL's PL/pgSQL syntax, and the trigger definition syntax may also need adjustment to align with PostgreSQL's requirements.

Q11

Single Choice

Which command is used to create a trigger in PostgreSQL?

CREATE FUNCTION CREATE TRIGGER CREATE EVENTCREATE RULE

In PostgreSQL, the CREATE TRIGGER command is used to create a new trigger that will execute a specified function when certain events occur in the database.

Q12

Single Choice

In PostgreSQL, when can a trigger be fired?

Only before an INSERT Only after an UPDATE Before or after an INSERT, UPDATE, or DELETEOnly before a DELETE

PostgreSQL triggers can be fired before or after an INSERT, UPDATE, or DELETE operation, depending on how the trigger is defined.

Q13

Single Choice

What is the default timing for a trigger in PostgreSQL if the timing is not specified?

AFTER BEFORE DURINGINSTEAD OF

If no timing is specified when creating a trigger in PostgreSQL, it defaults to BEFORE.

Q14

Single Choice

Which of the following is a valid SQL syntax to create a trigger in PostgreSQL that logs updates to a table called employee?

SQL Code

A) CREATE TRIGGER log_update
 AFTER UPDATE ON employee
 FOR EACH ROW
 EXECUTE PROCEDURE log_changes();
B) CREATE TRIGGER log_update
 AFTER UPDATE ON employee
 FOR EACH ROW
 CALL log_changes();
C) CREATE TRIGGER log_update
 BEFORE UPDATE ON employee
 FOR EACH ROW
 EXECUTE FUNCTION log_changes();
D) CREATE TRIGGER log_update
 BEFORE UPDATE ON employee
 FOR EACH ROW
 CALL FUNCTION log_changes();

CREATE TRIGGER log_update AFTER UPDATE ON employee FOR EACH ROW EXECUTE PROCEDURE log_changes(); CREATE TRIGGER log_update AFTER UPDATE ON employee FOR EACH ROW CALL log_changes(); CREATE TRIGGER log_update BEFORE UPDATE ON employee FOR EACH ROW EXECUTE FUNCTION log_changes();CREATE TRIGGER log_update BEFORE UPDATE ON employee FOR EACH ROW CALL FUNCTION log_changes();

The correct syntax to create a trigger in PostgreSQL includes the CREATE TRIGGER statement followed by the trigger name, event timing (e.g., AFTER UPDATE), target table, and the procedure to execute using EXECUTE PROCEDURE.

Q15

Single Choice

Which special variable is available in a PostgreSQL trigger function to access the row being inserted or updated?

NEW CURRENT OLDTHIS

In a PostgreSQL trigger function, the NEW variable is used to reference the new row being inserted or the row being updated.

Q16

Single Choice

How would you disable an existing trigger named audit_trigger on the sales table in PostgreSQL?

ALTER TRIGGER audit_trigger ON sales DISABLE; ALTER TABLE sales DISABLE TRIGGER audit_trigger; DROP TRIGGER audit_trigger ON sales;REMOVE TRIGGER audit_trigger FROM sales;

To disable a trigger in PostgreSQL, you use the ALTER TABLE statement with the DISABLE TRIGGER clause.

Q17

Single Choice

Which event will not fire a trigger that is defined as AFTER INSERT OR UPDATE ON orders in PostgreSQL?

INSERT INTO orders UPDATE orders SET status = 'shipped DELETE FROM orders WHERE order_id = 100INSERT INTO orders (order_id, order_date) VALUES (101, NOW());

The trigger is defined for INSERT and UPDATE events only, so it will not fire on a DELETE event.

Q18

Single Choice

Consider the following trigger creation statement. What is the purpose of this trigger?

SQL Code

CREATE TRIGGER check_stock
BEFORE UPDATE ON inventory
FOR EACH ROW
WHEN (NEW.stock < 0)
EXECUTE FUNCTION raise_exception();

It raises an exception if the new stock value is greater than zero. It raises an exception if the new stock value is less than zero. It updates the stock value to zero if it's negative.It logs the stock changes when the stock is less than zero.

This trigger raises an exception whenever an attempt is made to update the inventory table with a stock value less than zero.

Q19

Single Choice

Which of the following statements correctly creates a trigger in PostgreSQL that executes a function before a row in the orders table is deleted?

CREATE TRIGGER before_delete_order BEFORE DELETE ON orders FOR EACH ROW EXECUTE FUNCTION log_deletion(); CREATE TRIGGER before_delete_order AFTER DELETE ON orders FOR EACH ROW EXECUTE FUNCTION log_deletion(); CREATE TRIGGER before_delete_order INSTEAD OF DELETE ON orders FOR EACH ROW EXECUTE FUNCTION log_deletion();CREATE TRIGGER before_delete_order ON DELETE ON orders FOR EACH ROW EXECUTE FUNCTION log_deletion();

The correct syntax for creating a trigger that executes a function before a row is deleted from a table includes the BEFORE DELETE clause.

Q20

Single Choice

In PostgreSQL, how can you ensure that a trigger only fires for specific columns, such as salary, in the employees table during an UPDATE?

By using AFTER UPDATE OF salary ON employees By using BEFORE UPDATE ON employees FOR EACH ROW IF NEW.salary By using AFTER UPDATE IF NEW.salary IS NOT NULLBy using AFTER UPDATE IF NEW.salary IS NOT NULL

PostgreSQL allows specifying the column(s) for which the trigger should fire by using the AFTER UPDATE OF column_name ON table_name clause. This ensures that the trigger is executed only when the specified column is updated.

Q21

Multiple Choice

What is a database trigger in PostgreSQL?

SQL Code

CREATE OR REPLACE FUNCTION update_last_modified_column()
RETURNS TRIGGER AS $$
BEGIN
 NEW.last_modified := NOW();
 RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_last_modified
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION update_last_modified_column();

A mechanism to automatically execute SQL code on certain events A process that manually triggers events in the database A constraint to ensure data integrityA function that must be called explicitly

A database trigger in PostgreSQL is a set of actions automatically executed when specific events (INSERT, UPDATE, DELETE) occur in a database table. Triggers can be used to enforce business rules, maintain audit logs, or automatically update related data.

Q22

Multiple Choice

When would you use an AFTER INSERT trigger in PostgreSQL?

SQL Code

CREATE OR REPLACE FUNCTION log_employee_insert()
RETURNS TRIGGER AS $$
BEGIN
 INSERT INTO employee_audit(employee_id, action, action_date)
 VALUES (NEW.id, 'INSERT', NOW());
 RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER log_new_employee
AFTER INSERT ON employees
FOR EACH ROW
EXECUTE FUNCTION log_employee_insert();

To perform an action after a row is inserted To prevent an insert operation To modify data before insertionTo delete inserted data

An AFTER INSERT trigger is used when you want to perform an action after a new row has been inserted into a table. This is often used for logging, updating related data, or triggering additional processes.

Q23

Multiple Choice

What are the limitations of triggers in PostgreSQL?

SQL Code

CREATE OR REPLACE FUNCTION check_salary_increase()
RETURNS TRIGGER AS $$
BEGIN
 IF NEW.salary < OLD.salary THEN
 RAISE EXCEPTION 'Salary cannot be decreased';
 END IF;
 RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER prevent_salary_update
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION check_salary_increase();

Triggers can introduce performance overhead Triggers can be difficult to debug Triggers are not transactionalTriggers can cause unintended side effects

Triggers in PostgreSQL have several limitations, including potential performance overhead, complexity in debugging, and the risk of unintended side effects if not carefully implemented.

Q24

Multiple Choice

How can you create a trigger that fires only once per statement in PostgreSQL?

SQL Code

CREATE OR REPLACE FUNCTION audit_changes()
RETURNS TRIGGER AS $$
BEGIN
 INSERT INTO audit_log(table_name, action, action_date)
 VALUES ('employees', TG_OP, NOW());
 RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_employee_changes
AFTER UPDATE ON employees
FOR EACH STATEMENT
EXECUTE FUNCTION audit_changes();

Use the 'FOR EACH STATEMENT' clause Use the 'FOR EACH ROW' clause Use the 'BEFORE' clauseUse the 'AFTER' clause

To create a trigger that fires once per statement, rather than once per row, you use the 'FOR EACH STATEMENT' clause. This is useful when you need to log or perform actions at the statement level rather than for every affected row.

Q25

Multiple Choice

What is the purpose of a BEFORE DELETE trigger in PostgreSQL?

SQL Code

CREATE OR REPLACE FUNCTION check_active_projects()
RETURNS TRIGGER AS $$
BEGIN
 IF EXISTS (SELECT 1 FROM projects WHERE employee_id = OLD.id AND status = 'active') THEN
 RAISE EXCEPTION 'Cannot delete employee with active projects';
 END IF;
 RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_employee_termination
BEFORE DELETE ON employees
FOR EACH ROW
EXECUTE FUNCTION check_active_projects();

To perform checks before a row is deleted To log the deletion after it occurs To modify data before deletionTo prevent a deletion based on certain conditions

A BEFORE DELETE trigger is used to perform actions or checks before a row is deleted. This can be useful for enforcing business rules, such as preventing the deletion of data that should not be removed under certain conditions.

Q26

Multiple Choice

Can triggers in PostgreSQL be used to enforce referential integrity?

SQL Code

CREATE OR REPLACE FUNCTION check_customer_exists()
RETURNS TRIGGER AS $$
BEGIN
 IF NOT EXISTS (SELECT 1 FROM customers WHERE id = NEW.customer_id) THEN
 RAISE EXCEPTION 'Customer does not exist';
 END IF;
 RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER enforce_foreign_key
BEFORE INSERT ON orders
FOR EACH ROW
EXECUTE FUNCTION check_customer_exists();

Yes, triggers can enforce referential integrity No, only foreign key constraints can enforce referential integrity Yes, but with some limitationsNo, triggers are not meant for referential integrity

Yes, triggers in PostgreSQL can be used to enforce referential integrity, especially in situations where foreign key constraints are not sufficient or when custom logic is required.

Q27

Multiple Choice

What is the difference between a row-level and a statement-level trigger in PostgreSQL?

SQL Code

CREATE OR REPLACE FUNCTION log_row_update()
RETURNS TRIGGER AS $$
BEGIN
 INSERT INTO status_log(order_id, old_status, new_status, log_date)
 VALUES (OLD.id, OLD.order_status, NEW.order_status, NOW());
 RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER log_order_update
AFTER UPDATE ON orders
FOR EACH ROW
WHEN (OLD.order_status IS DISTINCT FROM NEW.order_status)
EXECUTE FUNCTION log_row_update();

CREATE OR REPLACE FUNCTION log_bulk_update()
RETURNS TRIGGER AS $$
BEGIN
 INSERT INTO bulk_log(action, log_date)
 VALUES ('Bulk order update', NOW());
 RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER log_bulk_order_update
AFTER UPDATE ON orders
FOR EACH STATEMENT
EXECUTE FUNCTION log_bulk_update();

Row-level triggers execute once per affected row Statement-level triggers execute once per statement Row-level triggers are faster than statement-level triggersStatement-level triggers execute for each row as well

A row-level trigger executes once for each row affected by the triggering event, while a statement-level trigger executes once per triggering statement, regardless of how many rows are affected. Row-level triggers are useful for auditing changes to individual rows, whereas statement-level triggers are suitable for logging actions or enforcing constraints at a higher level.

Q28

Multiple Choice

How can you disable a trigger in PostgreSQL temporarily?

SQL Code

ALTER TABLE employees DISABLE TRIGGER audit_employee_changes;
-- Perform necessary operations without the trigger
ALTER TABLE employees ENABLE TRIGGER audit_employee_changes;

Use the DISABLE TRIGGER command Use the DROP TRIGGER command Use the ALTER FUNCTION commandUse the DISABLE ALL TRIGGERS command

In PostgreSQL, triggers can be temporarily disabled and re-enabled using the ALTER TABLE command with the DISABLE TRIGGER and ENABLE TRIGGER options. This is useful when you need to perform bulk operations without triggering the associated triggers.

Q29

Multiple Choice

Can a trigger in PostgreSQL call another trigger?

SQL Code

CREATE OR REPLACE FUNCTION update_summary()
RETURNS TRIGGER AS $$
BEGIN
 UPDATE order_summary SET total_orders = total_orders + 1 WHERE customer_id = NEW.customer_id;
 RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_order_summary
AFTER UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION update_summary();

CREATE OR REPLACE FUNCTION log_order_update()
RETURNS TRIGGER AS $$
BEGIN
 INSERT INTO order_log(order_id, log_date)
 VALUES (NEW.id, NOW());
 RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER log_order_update
AFTER UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION log_order_update();

Yes, triggers can call other triggers No, triggers cannot call other triggers Yes, but with risk of infinite loopsNo, triggers are isolated events

Yes, in PostgreSQL, one trigger can call another trigger. This is possible because triggers can perform operations that, in turn, trigger other triggers. However, care must be taken to avoid circular references or infinite loops.

Q30

Multiple Choice

What is the purpose of a BEFORE INSERT trigger in PostgreSQL?

SQL Code

CREATE OR REPLACE FUNCTION enforce_order_limit()
RETURNS TRIGGER AS $$
DECLARE
 order_count INT;
BEGIN
 SELECT COUNT(*) INTO order_count FROM orders WHERE customer_id = NEW.customer_id;
 IF order_count >= 10 THEN
 RAISE EXCEPTION 'Order limit exceeded';
 END IF;
 RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_order_limit
BEFORE INSERT ON orders
FOR EACH ROW
EXECUTE FUNCTION enforce_order_limit();

To perform checks before a row is inserted To log the insertion after it occurs To modify data before insertionTo enforce business rules before inserting data

A BEFORE INSERT trigger is used to perform actions or checks before a row is inserted into the table. This can include validating data, modifying the data before insertion, or enforcing business rules, such as preventing excessive orders for a single customer.

PostgreSQL Database Quiz Questions