SHOW TRIGGERS;
SHOW TRIGGERS LIKE 'payments%';
SELECT * from sql_invoice.invoices i ;
SELECT * FROM sql_invoice.payments p ;
DROP TABLE IF EXISTS payments_audit;
CREATE TABLE payments_audit (
    client_id INT,
    `date` DATE,
    amount DECIMAL(9,2),
    action_type VARCHAR(50),
    action_date DATETIME
);
-- 新建trigger
DROP TRIGGER IF EXISTS payments_after_insert;
DELIMITER $$
CREATE TRIGGER payments_after_insert
    AFTER INSERT ON payments
    FOR EACH ROW 
BEGIN 
    UPDATE invoices 
    SET payment_total = payment_total + NEW.amount
    WHERE invoice_id = NEW.invoice_id;
    -- 审计
    INSERT INTO payments_audit (
        client_id,
        `date`,
        amount,
        action_type,
        action_date
    ) VALUES (
        NEW.client_id,
        NEW.date,
        NEW.amount,
        'Insert',
        NOW()
    );
END$$
DELIMITER ;
INSERT INTO payments (
    payment_id, 
    client_id,
    invoice_id,
    `date`,
    amount,
    payment_method 
) VALUES (
    DEFAULT,
    5,
    3,
    NOW(),
    10,
    1
);
-- 删除的trigger
DROP TRIGGER IF EXISTS payments_after_delete;
DELIMITER $$
CREATE TRIGGER payments_after_delete
    AFTER DELETE ON payments
    FOR EACH ROW 
BEGIN 
    UPDATE invoices 
    SET payment_total = payment_total - OLD.amount
    WHERE invoice_id = OLD.invoice_id;
    -- 审计
    INSERT INTO payments_audit (
        client_id,
        `date`,
        amount,
        action_type,
        action_date
    ) VALUES (
        OLD.client_id,
        OLD.date,
        OLD.amount,
        'Delete',
        NOW()
    );
END$$
DELIMITER ;
DELETE FROM payments 
WHERE payment_id = 16;

Event

-- MYSQL所有系统变量
SHOW VARIABLES LIKE 'event%';
SET GLOBAL event_scheduler = 'ON';
SHOW EVENTS LIKE 'monthly%';
ALTER EVENT monthly_delete_stale_audit_rows DISABLE;
ALTER EVENT monthly_delete_stale_audit_rows ENABLE;
DROP EVENT IF EXISTS monthly_delete_stale_audit_rows;
DELIMITER $$
-- ALTER EVENT monthly_delete_stale_audit_row;
CREATE EVENT monthly_delete_stale_audit_rows
ON SCHEDULE
--     AT '2022-05-01'
    EVERY 1 MONTH STARTS '2022-05-01' ENDS '2032-01-01'
DO BEGIN 
    DELETE FROM payments_audit
    WHERE action_date < NOW() - INTERVAL 3 MONTH;
END$$
DELIMITER ;