I am working with power apps, I have connected to a postgres database and inside my power apps I can add rows and update values from my database. Everything was goung good, until I tried to create a shadow table or an audit_table (so I could get the information of changes made in the table). I was using the following queries in PostgreSQL:
CREATE TABLE shadow_table (
id SERIAL PRIMARY KEY,
original_id INT,
original_data TEXT, -- or match the column structure of your main table
operation VARCHAR(10), -- 'INSERT', 'UPDATE', 'DELETE'
changed_at TIMESTAMP DEFAULT NOW(),
changed_by VARCHAR(50) -- optional to track who made the change
);
CREATE OR REPLACE FUNCTION audit_changes()
RETURNS TRIGGER AS $$
BEGIN
IF (TG_OP = 'INSERT') THEN
INSERT INTO shadow_table (original_id, original_data, operation, changed_by)
VALUES (NEW.id, row_to_json(NEW), 'INSERT', current_user);
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO shadow_table (original_id, original_data, operation, changed_by)
VALUES (NEW.id, row_to_json(NEW), 'UPDATE', current_user);
ELSIF (TG_OP = 'DELETE') THEN
INSERT INTO shadow_table (original_id, original_data, operation, changed_by)
VALUES (OLD.id, row_to_json(OLD), 'DELETE', current_user);
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON primary_table
FOR EACH ROW EXECUTE FUNCTION audit_changes();
I thought this would work, but I got a conflict: now my power apps cannot add rows nor update values. I get a message of error but no more info than that.
Do you know how can I see the full error? Can you imagine what is going on? Is there a way to change my queries in order to have both things?
