MySQL - can a trigger created on multiple tables?

 In MySQL, **triggers** are associated with a single table, meaning you can only create a trigger that is tied to a specific event (e.g., `INSERT`, `UPDATE`, `DELETE`) on **one table**. You cannot directly create a trigger that listens to events on multiple tables simultaneously.

However, there are ways to achieve similar functionality by creating **multiple triggers**, each on different tables, and making them perform related tasks across those tables.

### Alternatives to Triggers on Multiple Tables

1. **Multiple Triggers on Different Tables**: 

   You can create individual triggers on each table and have them perform actions that indirectly affect other tables. This way, while the triggers are still defined on single tables, they can perform operations on multiple tables.

   Example:

   - A trigger on `bank_transactions` updates the `balance` in the `bank` table.

   - A separate trigger on the `bank` table could log changes to a transaction history table.

2. **Stored Procedures**:

   Instead of using triggers, you can use a stored procedure that handles operations on multiple tables in one go. You would then call this procedure from your application or use it within a trigger.

3. **Chained Triggers**:

   You can create triggers that invoke changes on different tables. For example, an `INSERT` trigger on one table could modify a second table, which itself might have a trigger. While indirect, this creates a sequence of actions across multiple tables.

### Example: Multiple Triggers Affecting Multiple Tables

Let’s say you want to update balances when transactions are inserted into the `bank_transactions` table and log changes into a `transaction_log` table. You would need two triggers:

#### 1. Trigger on `bank_transactions` to update the `bank` table:

```sql

DELIMITER $$

CREATE TRIGGER update_balance

AFTER INSERT ON bank_transactions

FOR EACH ROW

BEGIN

  IF NEW.trtype = 'DEPOSIT' THEN

    UPDATE bank

    SET balance = balance + NEW.amount

    WHERE accno = NEW.accno;

  

  ELSIF NEW.trtype = 'WITHDRAWAL' THEN

    UPDATE bank

    SET balance = balance - NEW.amount

    WHERE accno = NEW.accno;

  END IF;

END$$

DELIMITER ;

```

#### 2. Trigger on `bank_transactions` to log the transaction in a `transaction_log` table:

```sql

DELIMITER $$

CREATE TRIGGER log_transaction

AFTER INSERT ON bank_transactions

FOR EACH ROW

BEGIN

  INSERT INTO transaction_log (accno, trtype, amount, transaction_time)

  VALUES (NEW.accno, NEW.trtype, NEW.amount, NOW());

END$$

DELIMITER ;

```

In this example:

- The first trigger updates the `balance` in the `bank` table.

- The second trigger logs the transaction details into the `transaction_log` table.

### Why You Can't Have Triggers on Multiple Tables Directly

Triggers are designed to be **table-specific** in MySQL to avoid complications and performance issues related to maintaining multiple tables at once. The design also ensures clarity by having a one-to-one relationship between a trigger and the table it affects.

### Conclusion:

While you cannot create a trigger directly on multiple tables in MySQL, you can simulate this behavior by creating **individual triggers on different tables** or using other database features like **stored procedures** or **chained triggers**.