Cómo crear triggers de auditoría en PostgreSql con PgAdmin 4
Código Sql para auditorías con la base de datos Northwind en PgAdmin 4
--auditoría tabla products
CREATE TABLE IF NOT EXISTS audit_products(
audit_id serial PRIMARY KEY,
product_id smallint,
old_product_name varchar(60),
new_product_name varchar(60),
old_supplier_id smallint,
new_supplier_id smallint,
old_category_id smallint,
new_category_id smallint,
old_quantity_per_unit varchar(60),
new_quantity_per_unit varchar(60),
old_unit_price real,
new_unit_price real,
old_units_in_stock smallint,
new_units_in_stock smallint,
old_units_on_order smallint,
new_units_on_order smallint,
old_reorder_level smallint,
new_reorder_level smallint,
old_discontinued integer,
new_discontinued integer,
action_type varchar(60),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
created_by varchar(60)
)
--función
CREATE OR REPLACE FUNCTION ft_audit_products() RETURNS TRIGGER
AS $$
DECLARE
BEGIN
IF tg_op='INSERT' THEN
INSERT INTO audit_products(product_id, new_product_name,
new_supplier_id, new_category_id, new_quantity_per_unit,
new_unit_price, new_units_in_stock, new_units_on_order,
new_reorder_level, new_discontinued, action_type, created_by)
VALUES(new.product_ID, new.product_name,
new.supplier_id, new.category_id, new.quantity_per_unit,
new.unit_price, new.units_in_stock, new.units_on_order,
new.reorder_level, new.discontinued, 'INSERT', CURRENT_USER);
ELSIF tg_op='UPDATE' THEN
INSERT INTO audit_products(product_ID, old_product_name,
new_product_name, old_supplier_id, new_supplier_id, old_category_id,
new_category_id, old_quantity_per_unit, new_quantity_per_unit,
old_unit_price, new_unit_price, old_units_in_stock, new_units_in_stock,
old_units_on_order, new_units_on_order, old_reorder_level,
new_reorder_level, old_discontinued, new_discontinued, action_type,
created_by)
VALUES(old.product_ID, old.product_name, new.product_name,
old.supplier_id, new.supplier_id, old.category_id, new.category_id,
old.quantity_per_unit, new.quantity_per_unit, old.unit_price,
new.unit_price, old.units_in_stock, new.units_in_stock,
old.units_on_order, new.units_on_order, old.reorder_level,
new.reorder_level, old.discontinued, new.discontinued, 'UPDATE',
CURRENT_USER);
ELSIF tg_op='DELETE' THEN
INSERT INTO audit_products(product_ID, old_product_name,
old_supplier_id, old_category_id, old_quantity_per_unit,
old_unit_price, old_units_in_stock, old_units_on_order,
old_reorder_level, old_discontinued, action_type, created_by)
VALUES(old.product_ID, old.product_name,
old.supplier_id, old.category_id, old.quantity_per_unit,
old.unit_price, old.units_in_stock, old.units_on_order,
old.reorder_level, old.discontinued, 'DELETE', CURRENT_USER);
END IF;
RETURN NULL;
END $$
LANGUAGE plpgsql;
--trigger
CREATE OR REPLACE TRIGGER tg_audit_products
AFTER INSERT OR UPDATE OR DELETE ON products
FOR EACH ROW
EXECUTE PROCEDURE ft_audit_products();