Sample Exam Questions

From the objective of OSS-DB Exam Silver
S3.1 SQL commands (Trigger)

Sample Question


You defined statement-level BEFORE triggers, statement-level AFTER triggers, row-level BEFORE triggers, and row-level AFTER triggers for an UPDATE of a table. What is the order in which triggers execute when an UPDATE is performed on this table?

  1. BEFORE statement -> BEFORE row -> AFTER statement -> AFTER row
  2. BEFORE statement -> BEFORE row -> AFTER row -> AFTER statement
  3. BEFORE row -> BEFORE statement -> AFTER row -> AFTER statement
  4. BEFORE row -> BEFORE statement -> AFTER statement -> AFTER row
  5. BEFORE -> AFTER, but the order of execution at the statement and row levels is determined by the alphabetical order of the function name that defines the trigger.

※This sample exam is different from those that appear in the actual OSS-DB Exam.

Answer and Explanation

In a database, you can set triggers to execute simultaneously with table updates, allowing other columns or tables to be updated at the same time. In PostgreSQL, triggers are defined by defining a function of type TRIGGER with CREATE FUNCTION and associating it with a table using CREATE TRIGGER.
There are several types of triggers, but the main categories are BEFORE triggers, which execute before table data is updated, and AFTER triggers, which execute after the update. In addition, there are statement-level triggers, which execute only once for each SQL statement execution, and row-level triggers, which execute individually for each row that is updated. Combined with BEFORE/AFTER, there are four types of triggers (there is also a type called an INSTEAD OF trigger).
When an UPDATE statement is issued to a table, the statement-level BEFORE trigger is executed first, followed by the row-level BEFORE trigger for each row to be updated. Once the BEFORE triggers have finished executing, the main body of the UPDATE statement is executed and the table is updated. Afterwards, in reverse order of BEFORE, the row-level AFTER trigger is executed for each row that was updated, followed by the statement-level AFTER trigger.

Therefore, the correct answer is B.