1Z0-909 practice question 3 of 15

Examine these commands which execute successfully: mYsql> CREATE TABLE income (acct_num INT, amount DECIMAL(10,2)); mysql> CREATE TRIGGER subtotal BEFORE INSERT ON income FOR EACH ROW SET @subtotal = ©subtotal + NEW.amount; Which is true for the income table?

Full set: 65 questions
Answer options
A: The trigger activates after any row has been inserted into the table.
B: The trigger body set causes trigger activation.
C: The trigger activates after any row in the table has been updated.
D: Execution of an insert statement causes the trigger to activate. Correct answer
Correct Answer:
D. Execution of an insert statement causes the trigger to activate.
Explanation

The trigger is declared BEFORE INSERT ON income FOR EACH ROW. MySQL therefore fires the trigger once for every row that an INSERT statement attempts to add to income, immediately before each row is written. No other DML statement (UPDATE, DELETE) or timing (AFTER) will invoke it. Consequently, executing any INSERT statement on income is what causes the trigger to run, making option D the only accurate choice.

Why Incorrect

A. Uses AFTER timing; the trigger is defined with BEFORE, so it does not fire after the row is stored.
B. The SET statement is the trigger body; it runs because the trigger fires, not vice-versa.
C. Trigger timing is tied to INSERT, not UPDATE, so updates never invoke it.

References

1. MySQL 8.0 Reference Manual, §13.1.22 “CREATE TRIGGER Statement”, paragraph “BEFORE INSERT triggers are activated for each row that is inserted.” (https://dev.mysql.com/doc/refman/8.0/en/create-trigger.html)

2. Oracle MySQL 8.0 Developer Guide, Chapter “Using Triggers”, subsection “Trigger Activation Timing and Event”, lines describing “BEFORE INSERT … fires once for each row inserted.” (Document ID: E87930-19, PDF p. 865)

Scroll to Top