hi, i need a audit table to follow the changes and the changer.
in order to that i used retool db to similate my situation. for retool datas it works, but my real db is located on mariaDB, and now i am taking errors. do you have an idea?
error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'or update or delete on mr_car for each row execute procedure audit_tf()' at line 1
my queris: `create or replace function audit_tf() returns trigger language plpgsql as
$$
declare
k text;
v text;
j_new jsonb := to_jsonb(new);
j_old jsonb := to_jsonb(old);
begin
if TG_OP = 'INSERT' then -- only shows new values
for k, v in select * from jsonb_each_text(j_new) loop
insert into log_table2 (src_table, col_name, v_new, action)
values (TG_TABLE_NAME, k, v, TG_OP);
end loop;
elsif TG_OP = 'UPDATE' then -- shows new and old values
for k, v in select * from jsonb_each_text(j_new) loop
if (v <> j_old ->> k) then
insert into log_table2 (src_table, col_name, v_new, v_old, action)
values (TG_TABLE_NAME, k, v, j_old ->> k, TG_OP);
end if;
end loop;
elsif TG_OP = 'DELETE' then -- only shows old values
for k, v in select * from jsonb_each_text(j_old) loop
insert into log_table2 (src_table, col_name, v_old, action)
values (TG_TABLE_NAME, k, v, TG_OP);
end loop;
end if;
return null;
end;
$$;
create trigger test_audit_trigger
after insert or update or delete on users
for each row execute procedure audit_tf();
`
my query structure:
