MySQL - what is OLD is referred to in triggers and how joins will work within triggers

 In MySQL triggers, `OLD` and `NEW` are keywords that allow access to the data in the rows affected by the triggering event. These keywords are used to reference the values of the columns **before** and **after** the operation (like an `INSERT`, `UPDATE`, or `DELETE`) is executed.

### `OLD` and `NEW` in Triggers

- **`OLD`**: Refers to the column values **before** the triggering event occurs. It's mainly used in `DELETE` and `UPDATE` triggers.

- **`NEW`**: Refers to the column values **after** the triggering event occurs. It's used in `INSERT` and `UPDATE` triggers.

### Example of `OLD` and `NEW`

#### 1. **For `UPDATE` Triggers**:

In an `UPDATE` trigger, you can reference both `OLD` (the value before the update) and `NEW` (the value after the update):

```sql

CREATE TRIGGER update_salary

AFTER UPDATE ON employees

FOR EACH ROW

BEGIN

  IF OLD.salary <> NEW.salary THEN

    INSERT INTO salary_log (employee_id, old_salary, new_salary, change_time)

    VALUES (OLD.id, OLD.salary, NEW.salary, NOW());

  END IF;

END;

```

In this example:

- `OLD.salary` is the employee's salary **before** the update.

- `NEW.salary` is the employee's salary **after** the update.

- The trigger logs the salary change into a `salary_log` table if the salary was updated.

#### 2. **For `DELETE` Triggers**:

In a `DELETE` trigger, `OLD` is used to refer to the data being deleted:

```sql

CREATE TRIGGER log_deletion

AFTER DELETE ON employees

FOR EACH ROW

BEGIN

  INSERT INTO employee_log (employee_id, action_time, action)

  VALUES (OLD.id, NOW(), 'DELETED');

END;

```

In this case, `OLD.id` refers to the `id` of the row that was deleted.

#### 3. **For `INSERT` Triggers**:

In an `INSERT` trigger, only `NEW` is available because the row is being newly created:

```sql

CREATE TRIGGER log_insertion

AFTER INSERT ON employees

FOR EACH ROW

BEGIN

  INSERT INTO employee_log (employee_id, action_time, action)

  VALUES (NEW.id, NOW(), 'INSERTED');

END;

```

Here, `NEW.id` refers to the `id` of the newly inserted row.

---

### Using Joins in Triggers

MySQL triggers can execute multiple SQL statements, including `SELECT` statements that involve **joins**. However, triggers cannot modify the same table they are associated with directly (this would result in a recursion issue), but they can use joins for reading data from related tables or even updating related tables.

#### Example of a Join in a Trigger

Suppose you want to update an inventory when a product is ordered, and the order details are stored in the `orders` table. You can use a join to retrieve related data from the `products` table:

```sql

CREATE TRIGGER update_inventory

AFTER INSERT ON orders

FOR EACH ROW

BEGIN

  DECLARE product_stock INT;

  

  -- Select the current stock of the ordered product

  SELECT stock INTO product_stock

  FROM products

  WHERE products.id = NEW.product_id;

  -- Update the product stock based on the order quantity

  UPDATE products

  SET stock = product_stock - NEW.quantity

  WHERE id = NEW.product_id;

END;

```

In this example:

- A `SELECT` statement with a join is used to get the current stock of the product.

- Based on the result of the join, the `products` table is updated by subtracting the ordered quantity (`NEW.quantity`).

#### Important Considerations with Joins in Triggers:

1. **Performance Impact**: Using joins within a trigger can add complexity and potential performance overhead, especially in high-traffic systems where many records are being inserted, updated, or deleted at once.

2. **Read-Only Joins**: Joins in triggers are typically read-only operations. Triggers should avoid complex update logic on large joined result sets as this could degrade performance.

3. **Trigger Limitations**: MySQL triggers cannot perform certain actions, like issuing a `COMMIT` or `ROLLBACK`. They also can't modify the same table that caused the trigger to fire.

Triggers are powerful tools for maintaining data integrity and performing automatic tasks, and using joins inside triggers can be helpful for operations that involve multiple related tables. However, they should be used cautiously to avoid introducing performance bottlenecks or overly complex business logic.