mysql trigger to store log

记录指定参数变化

https://zhuanlan.zhihu.com/p/439273702

DELIMITER //
CREATE TRIGGER log_sales_updates
AFTER UPDATE
ON sales
FOR EACH ROW
Insert into audit_log(sales_id, previous_amount, new_amount, updated_by, updated_on) VALUES (NEW.sales_id,OLD.sales_amount, NEW.sales_amount,(SELECT USER()), NOW() )//
DELIMITER ;

 

记录全表参数

https://blog.csdn.net/weixin_44377973/article/details/128173506

DROP TRIGGER IF EXISTS `trigger_after_update_emp`;

delimiter //
create trigger trigger_after_update_emp
after update on emp
for each row
begin
insert into emp_log(
    operate_type,
    operate_user,
    operate_time,
    emp_id,
    emp_name,
    birth,
    salary,
    comm,
    phone,
    addr
)
values(
    'UPDATE',
    user(),
    now(),
    old.emp_id,
    old.emp_name,
    old.birth,
    old.salary,
    old.comm,
    old.phone,
    old.addr
);
end //
delimiter ;

 

Manage Trigger Script in Mysqlalchemy code

https://sqlalchemy-declarative-extensions.readthedocs.io/en/stable/triggers.html

from sqlalchemy.orm import declarative_base
from sqlalchemy_declarative_extensions import declarative_database, Function, Functions, Triggers
from sqlalchemy_declarative_extensions.dialects.postgresql import Trigger

_Base = declarative_base()


@declarative_database
class Base(_Base):
    __abstract__ = True

    functions = Functions().are(
        Function(
            "fancy_trigger",
            """
            BEGIN
            INSERT INTO foo (id) select NEW.id + 1;
            RETURN NULL;
            END
            """,
            language="plpgsql",
            returns="trigger",
        )
    )

    triggers = Triggers().are(
        Trigger.after("insert", on="foo", execute="fancy_trigger")
        .named("on_insert_foo")
        .when("pg_trigger_depth() < 1")
        .for_each_row(),
    )