{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "e634ad2c-413f-4fb1-a89a-100cd5ee7bdf",
   "metadata": {},
   "source": [
    "# Demonstrate Triggers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b535e29d-579d-4cf3-aceb-417ab86451d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "from data201 import db_connection, df_query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8726f673-e77c-4b49-99c9-00c7a64952d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = db_connection(config_file = 'pay.ini')\n",
    "cursor = conn.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "79a4e681-fd8e-4156-9fd1-bd484445428d",
   "metadata": {},
   "source": [
    "## Payment table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "68a346d6-65e9-4988-a3a5-a776a52de0cc",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute('DROP TABLE IF EXISTS payment')\n",
    "\n",
    "sql = ( \"\"\"\n",
    "        CREATE TABLE payment\n",
    "        (\n",
    "            id INT UNIQUE NOT NULL AUTO_INCREMENT,\n",
    "            name VARCHAR(16) UNIQUE NOT NULL,\n",
    "            rate DOUBLE NOT NULL,\n",
    "            PRIMARY KEY (id)\n",
    "        )\n",
    "        \"\"\"\n",
    "    )\n",
    "\n",
    "cursor.execute(sql);"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dfdfe225-f588-4fd3-b4b4-08e867a673f9",
   "metadata": {},
   "source": [
    "## Log table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9f2de63a-d1d8-40ad-8be4-1647550ee8ea",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute('DROP TABLE IF EXISTS log')\n",
    "\n",
    "sql = ( \"\"\"\n",
    "        CREATE TABLE log\n",
    "        (\n",
    "            id INT UNIQUE NOT NULL AUTO_INCREMENT,\n",
    "            timestamp DATETIME NOT NULL,\n",
    "            name VARCHAR(16) UNIQUE NOT NULL,\n",
    "            rate DOUBLE NOT NULL,\n",
    "            PRIMARY KEY (id)\n",
    "        )\n",
    "        \"\"\"\n",
    ")\n",
    "\n",
    "cursor.execute(sql);"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "def6dbd6-f2e3-406a-9dd9-4d2595570394",
   "metadata": {},
   "source": [
    "## Initial data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9faab252-a3ad-45cd-934e-e58d1b67595c",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = ( \"\"\"\n",
    "        INSERT INTO payment(name, rate)\n",
    "        VALUES (%s, %s)\n",
    "        \"\"\"\n",
    "      ) \n",
    "\n",
    "data = [ ('Bob',   27), \n",
    "         ('Frank', 25), \n",
    "         ('Mary',  30)\n",
    "       ]\n",
    "\n",
    "cursor.executemany(sql, data)  # NOTE: executemany()\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "27adbdfa-e8da-4ce2-8fd1-f3a732496bba",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_query(conn, 'SELECT * FROM payment')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "928c7f7a-8afd-456c-8614-96c1f84c3818",
   "metadata": {},
   "source": [
    "## Trigger: Before\n",
    "\n",
    "#### **Before** inserting a new employee, calculate the employee's rate to be $1 more than the current minimum rate."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "996767a9-4127-43b0-bc65-45efc275d056",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute('DROP TRIGGER IF EXISTS insert_new_employee')\n",
    "\n",
    "sql = ( \"\"\"\n",
    "        CREATE TRIGGER insert_new_employee\n",
    "            BEFORE INSERT ON payment\n",
    "            FOR EACH ROW\n",
    "        BEGIN\n",
    "            DECLARE min_rate DOUBLE;\n",
    "            SELECT MIN(rate) FROM payment INTO min_rate;\n",
    "            SET NEW.rate = min_rate + 1;\n",
    "        END\n",
    "        \"\"\"\n",
    "      )\n",
    "\n",
    "cursor.execute(sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bf29aa6c-bd5a-4328-b44b-9d5b826a2f0f",
   "metadata": {},
   "source": [
    "## Trigger: After\n",
    "\n",
    "#### **After** insering a new employee, log the insertion."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4f94e2b3-8c45-46a0-8d39-5ff9ff5ef70c",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute('DROP TRIGGER IF EXISTS log_new_employee')\n",
    "\n",
    "sql = ( \"\"\"\n",
    "        CREATE TRIGGER log_new_employee\n",
    "            AFTER INSERT ON payment\n",
    "            FOR EACH ROW\n",
    "        BEGIN\n",
    "            INSERT INTO log(timestamp, name, rate)\n",
    "                VALUES (NOW(), NEW.name, NEW.rate);\n",
    "        END\n",
    "        \"\"\"\n",
    "      )\n",
    "\n",
    "cursor.execute(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "048e86c9-6264-48ce-899c-f3d3c48d3245",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_query(conn, 'SHOW TRIGGERS')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f3ec83b3-73c0-4b2c-ae0e-31d9493a6e18",
   "metadata": {},
   "source": [
    "#### You can also view and edit triggers in MySQL workbench."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6aaf7f4a-fa41-4890-bbd4-fb8f5927b40f",
   "metadata": {},
   "source": [
    "## Insert new employees"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4cdb7855-5914-4e03-bf06-24ad7a82d0d6",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = ( \"\"\"\n",
    "        INSERT INTO payment(name) \n",
    "        VALUES ('ron')\n",
    "        \"\"\"\n",
    "      )\n",
    "\n",
    "cursor.execute(sql)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bdcd59c4-e6a2-4540-a651-14f239885f1d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_query(conn, 'SELECT * FROM payment')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8d25e45b-1316-4dcc-81da-de396ceff65c",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = ( \"\"\"\n",
    "        INSERT INTO payment(name) \n",
    "        VALUES ('sara')\n",
    "        \"\"\"\n",
    "      )\n",
    "\n",
    "cursor.execute(sql)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3aa2eb41-6761-4c6e-be83-3bbce838fc95",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_query(conn, 'SELECT * FROM payment')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "179cd387-38fb-4f43-b2a4-4521dc2f2f18",
   "metadata": {},
   "source": [
    "## What got logged"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8a9def49-5c87-49af-88be-eb4d3600ba3a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_query(conn, 'SELECT * FROM log')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a1372407-a408-4de4-9790-bf8acc07e9d4",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "05c37ee3-e900-4749-817a-eea67a4f1152",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}