DELIMITER //

CREATE TRIGGER trigger_name
  {BEFORE | AFTER} {INSERT | UPDATE | DELETE}
  ON table_name
  FOR EACH ROW
BEGIN
  -- trigger body
END //

DELIMITER ;

-- In a BEFORE UPDATE trigger you can read AND modify NEW values
CREATE TRIGGER before_price_update
  BEFORE UPDATE ON products
  FOR EACH ROW
BEGIN
  -- Force price to never go negative
  IF NEW.price < 0 THEN
    SET NEW.price = 0;
  END IF;
END;

-- First, create an audit log table
CREATE TABLE customers_audit (
  audit_id    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  action      ENUM('INSERT','UPDATE','DELETE') NOT NULL,
  customer_id INT             NOT NULL,
  changed_by  VARCHAR(100),
  changed_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  old_email   VARCHAR(255),
  new_email   VARCHAR(255),
  old_status  VARCHAR(50),
  new_status  VARCHAR(50)
);

DELIMITER //

-- Log every new customer
CREATE TRIGGER trg_customers_after_insert
  AFTER INSERT ON customers
  FOR EACH ROW
BEGIN
  INSERT INTO customers_audit
    (action, customer_id, changed_by, new_email, new_status)
  VALUES
    ('INSERT', NEW.customer_id, USER(), NEW.email, NEW.status);
END //

-- Log every customer update
CREATE TRIGGER trg_customers_after_update
  AFTER UPDATE ON customers
  FOR EACH ROW
BEGIN
  INSERT INTO customers_audit
    (action, customer_id, changed_by, old_email, new_email, old_status, new_status)
  VALUES
    ('UPDATE', NEW.customer_id, USER(),
     OLD.email, NEW.email,
     OLD.status, NEW.status);
END //

-- Log every customer deletion
CREATE TRIGGER trg_customers_after_delete
  AFTER DELETE ON customers
  FOR EACH ROW
BEGIN
  INSERT INTO customers_audit
    (action, customer_id, changed_by, old_email, old_status)
  VALUES
    ('DELETE', OLD.customer_id, USER(), OLD.email, OLD.status);
END //

DELIMITER ;

DELIMITER //

-- Prevent order items from having quantity <= 0
CREATE TRIGGER trg_order_items_before_insert
  BEFORE INSERT ON order_items
  FOR EACH ROW
BEGIN
  IF NEW.quantity <= 0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Order item quantity must be greater than zero';
  END IF;

  IF NEW.unit_price < 0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Unit price cannot be negative';
  END IF;
END //

-- Auto-set updated_at timestamp before any update
CREATE TRIGGER trg_products_before_update
  BEFORE UPDATE ON products
  FOR EACH ROW
BEGIN
  SET NEW.updated_at = NOW();
END //

DELIMITER ;

DELIMITER //

CREATE TRIGGER trg_prevent_order_delete
  BEFORE DELETE ON orders
  FOR EACH ROW
BEGIN
  -- Prevent deleting shipped or completed orders
  IF OLD.status IN ('shipped', 'completed') THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Cannot delete a shipped or completed order';
  END IF;
END //

-- Prevent salary reduction using BEFORE UPDATE
CREATE TRIGGER trg_no_salary_cut
  BEFORE UPDATE ON employees
  FOR EACH ROW
BEGIN
  IF NEW.salary < OLD.salary THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Salary cannot be reduced';
  END IF;
END //

DELIMITER ;

-- These will now raise errors:
DELETE FROM orders WHERE order_id = 100;   -- if status = 'shipped'
UPDATE employees SET salary = 30000 WHERE employee_id = 5; -- if current > 30000

-- orders table has a total_amount column we keep in sync automatically
DELIMITER //

CREATE TRIGGER trg_order_items_after_insert
  AFTER INSERT ON order_items
  FOR EACH ROW
BEGIN
  UPDATE orders
  SET total_amount = (
    SELECT SUM(quantity * unit_price)
    FROM order_items
    WHERE order_id = NEW.order_id
  )
  WHERE order_id = NEW.order_id;
END //

CREATE TRIGGER trg_order_items_after_update
  AFTER UPDATE ON order_items
  FOR EACH ROW
BEGIN
  UPDATE orders
  SET total_amount = (
    SELECT SUM(quantity * unit_price)
    FROM order_items
    WHERE order_id = NEW.order_id
  )
  WHERE order_id = NEW.order_id;
END //

CREATE TRIGGER trg_order_items_after_delete
  AFTER DELETE ON order_items
  FOR EACH ROW
BEGIN
  UPDATE orders
  SET total_amount = COALESCE((
    SELECT SUM(quantity * unit_price)
    FROM order_items
    WHERE order_id = OLD.order_id
  ), 0)
  WHERE order_id = OLD.order_id;
END //

DELIMITER ;

DELIMITER //

-- First trigger for AFTER INSERT on orders
CREATE TRIGGER trg_orders_after_insert_log
  AFTER INSERT ON orders
  FOR EACH ROW
BEGIN
  INSERT INTO order_log (order_id, event, created_at)
  VALUES (NEW.order_id, 'created', NOW());
END //

-- Second trigger runs AFTER the first
CREATE TRIGGER trg_orders_after_insert_notify
  AFTER INSERT ON orders
  FOR EACH ROW
  FOLLOWS trg_orders_after_insert_log
BEGIN
  INSERT INTO notification_queue (type, reference_id, queued_at)
  VALUES ('new_order', NEW.order_id, NOW());
END //

DELIMITER ;

-- Show all triggers in the current database
SHOW TRIGGERSG

-- Show triggers on a specific table
SHOW TRIGGERS FROM mydb LIKE 'customers'G

-- Query information_schema for full detail
SELECT
  TRIGGER_NAME,
  EVENT_MANIPULATION,
  EVENT_OBJECT_TABLE,
  ACTION_TIMING,
  ACTION_STATEMENT,
  CREATED
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = DATABASE()
ORDER BY EVENT_OBJECT_TABLE, ACTION_TIMING, EVENT_MANIPULATION;

-- Drop a trigger
DROP TRIGGER IF EXISTS trg_customers_after_insert;

-- Triggers are also dropped when the table is dropped
DROP TABLE customers;  -- removes all triggers on customers automatically

CREATE TABLE inventory_log (
  log_id       BIGINT AUTO_INCREMENT PRIMARY KEY,
  product_id   INT          NOT NULL,
  change_type  VARCHAR(20)  NOT NULL,
  qty_before   INT,
  qty_after    INT,
  changed_by   VARCHAR(100),
  changed_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  note         TEXT
);

DELIMITER //

CREATE TRIGGER trg_products_stock_after_update
  AFTER UPDATE ON products
  FOR EACH ROW
BEGIN
  IF OLD.stock_qty <> NEW.stock_qty THEN
    INSERT INTO inventory_log
      (product_id, change_type, qty_before, qty_after, changed_by, note)
    VALUES (
      NEW.product_id,
      CASE
        WHEN NEW.stock_qty > OLD.stock_qty THEN 'restock'
        ELSE 'deduction'
      END,
      OLD.stock_qty,
      NEW.stock_qty,
      USER(),
      CONCAT('Changed from ', OLD.stock_qty, ' to ', NEW.stock_qty)
    );
  END IF;
END //

DELIMITER ;