Skip to main content

Notifications

Community site session details

Community site session details

Session Id : 5NdbIEYdnY2N3nYzLTcrf8
Power Apps - Building Power Apps
Unanswered

Power Apps + Triggers in PostgreSQL

Like (1) ShareShare
ReportReport
Posted on 1 Oct 2024 18:16:45 by 50
Hello there!
 
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?
  • EddieE Profile Picture
    4,641 Super User 2025 Season 1 on 02 Oct 2024 at 03:24:58
    Power Apps + Triggers in PostgreSQL
    Powerapps doesn't work with SQL table which have Triggers setup. My guess is this is causing your issues.
     
    You can, however, now work directly with Stored Procedures, if that is an option?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

🌸 Community Spring Festival 2025 Challenge Winners! 🌸

Congratulations to all our community participants!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard > Power Apps - Building Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 106 Most Valuable Professional

#2
MS.Ragavendar Profile Picture

MS.Ragavendar 73

#3
stampcoin Profile Picture

stampcoin 52

Overall leaderboard
Loading started
Loading complete