First, create a new table named BankAccount by the following statement: CREATE TABLE BankAccount( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(64), balance REAL); Secondly, create another new table named BankAccount Log by the following statement: CREATE TABLE BankAccountLog( id INT AUTO_INCREMENT PRIMARY KEY, time DATETIME, event ENUM('D', 'W'), preBalance REAL, postBalance REAL); Let us insert a record to BankAccount table and query the data from BankAccount and BankAccount Log tables: INSERT BankAccount (name, balance) VALUES ('Ellen', 100); SELECT * FROM BankAccount; SELECT * FROM BankAccountLog; Now create a trigger to record every update operation on the BankAccount table and save the update history information. If the update operation reduced the balance on a bank account, the event column in the BankAccountLog table would be recorded as 'W' (namely, withdraw); if the update operation increases the balance on a bank account, the event column in the BankAccountLog table would be recorded as 'D' (namely, deposit). Execute an update operation and then observe whether the trigger's goal has been met.
