$1.$ Task One:

Logging New Purchases

Create a trigger that logs every new book purchase $($insert$)$ into the Transactions table with

the action 'Purchase'.

Is this a Row$-$Level Trigger or Statement$-$Level Trigger?

SQL$/$MySQL Code:

Run the following Execution Code:

$--$ Execution

INSERT INTO Transactions $($book$\_$id$,$ action$)$

VALUES $(1,$ 'Purchase'$)$;

Screenshot showing execution:

$2.$ Task Two:

Preventing Discount Abuse

Create a trigger that prevents purchases of books priced below $$60$ if the stock quantity is

less than $5.$

Hint: Use a BEFORE INSERT trigger to raise an error.

Is this a Row$-$Level Trigger or Statement$-$Level Trigger?

SQL$/$MySQL Code:

Run the following Execution Code:

$--$ Execution

INSERT INTO Transactions $($book$\_$id$,$ action$)$

VALUES $(2,$ 'Purchase'$)$;

Screenshot showing execution:

$3.$ Task Three:

Author Discounts

Create a trigger to apply a $10\%$ discount if the author's name contains "John" when a new

book is inserted.

Is this a Row$-$Level Trigger or Statement$-$Level Trigger?

SQL$/$MySQL Code:

Run the following Execution Code:

$--$ Execution

INSERT INTO Books $($title$,$ author, price, quantity$)$

VALUES $(\text{'}$Advanced SQL$\text{'},$ 'John Doe', $100\mathrm{.}00,5)$;

Screenshot showing execution:

Challenge Tasks:

$1.$ Challenge Task One:

Checking Inventory

Create a trigger to prevent reducing stock quantity below $1$ during an update to the

Books table.

Is this a Row$-$Level Trigger or Statement$-$Level Trigger?

SQL$/$MySQL Code:

Run the following Execution Code:

$--$ Execution

UPDATE Books SET quantity $=0$ WHERE book$\_$id $=1$;

Screenshot showing execution:

$2.$ Challenge Task Two:

Logging Bulk Deletions

Create a statement$-$level trigger to log a message in the Transactions table if more than $2$

books are deleted in a single operation.

Is this a Row$-$Level Trigger or Statement$-$Level Trigger?

SQL$/$MySQL Code:

Run the following Execution Code:

$--$ Execution

DELETE FROM Books WHERE price $<100$;

Screenshot showing execution: