POST Request after row creation

🍱 Features & apps Tables
1

Hey there!

I tried throuroughly searching the internet for a solution, but couldn’t find one, maybe you all can help me:

I am trying to accomplish that everytime a new row in a table is created or modified, it sends a POST requests to a server (in my instance Powerautomate).

Many thanks!

2

That is possible, since the rows of the tables app are stored in the database.

You should monitor the ‘oc_tables_rows’ table in the database for new rows and trigger a script accordingly, you can use the following approaches for MariaDB and PostgreSQL:

For MariaDB:

1. Triggers:

MariaDB supports triggers, and you can create a trigger that fires on an INSERT event in the ‘oc_tables_rows’ table and calls your script.

Example:

DELIMITER //
CREATE TRIGGER after_insert_trigger
AFTER INSERT ON nextcloud.oc_tables_rows
FOR EACH ROW
BEGIN
    CALL /path/to/your_script();
END;
//
DELIMITER ;

2. Event Scheduler:

You can utilize the Event Scheduler in MariaDB to periodically check for new rows and trigger your script if necessary.

Example:

CREATE EVENT check_for_new_rows
ON SCHEDULE EVERY 1 MINUTE
DO
BEGIN
    IF EXISTS (SELECT * FROM nextcloud.oc_tables_rows WHERE timestamp_column > NOW() - INTERVAL 1 MINUTE) THEN
        CALL /path/to/your_script();
    END IF;
END;

For PostgreSQL:

1. Triggers:

PostgreSQL also supports triggers. You can create a trigger that reacts to an INSERT event in the ‘oc_tables_rows’ table and calls your script.

Example:

CREATE OR REPLACE FUNCTION your_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    PERFORM pg_notify('new_row_added', '');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER after_insert_trigger
AFTER INSERT ON nextcloud.oc_tables_rows
FOR EACH ROW
EXECUTE FUNCTION your_trigger_function();

2. LISTEN/NOTIFY:

You can use the PostgreSQL LISTEN/NOTIFY system to respond to changes in the table. Your script can subscribe to NOTIFY events and react accordingly.

Example:

-- In your script
LISTEN new_row_added;

-- Trigger function in the database
CREATE OR REPLACE FUNCTION your_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    PERFORM pg_notify('new_row_added', '');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER after_insert_trigger
AFTER INSERT ON nextcloud.oc_tables_rows
FOR EACH ROW
EXECUTE FUNCTION your_trigger_function();

Remember to replace your_script with the actual name or path of your script. You must ensure that the script is executable (chmod +x /path/to/your_script.sh ) and that the database user has the necessary permissions to execute the script! Additionally, ensure proper security considerations when executing external code in triggers or trigger functions!

This is just my first idea, which I would resort to if I had to solve this. You now have to find out yourself exactly how it works. I just wanted to point you in the right direction.

Much and good luck,
ernolf