How to setup a SQL trigger in CartoDB

Question

I need to setup 2 SQL triggers in CartoDB that will update my inland and coastal tables each time my obs table is updated. I have never setup a SQL trigger but I have looked at the documentation and it appears as though I need to have a function stored somewhere that the trigger activates.

So far my first trigger looks like this:

CREATE TRIGGER update_inland
AFTER INSERT ON obs
FOR EACH ROW
EXECUTE PROCEDURE update_inland()

But I get an error message that "function update_inland() does not exist". I want my function to be this:

UPDATE inland SET lt_dispatch_level = obs.named_lt_dispatch_level 
FROM obs 
WHERE obs.created_at = (SELECT MAX(created_at) FROM obs) 
AND inland.cartodb_id = 1

How can I create this function and subsequent trigger in CartoDB? I have been reading the documentation for both Postgres triggers and functions but I need some help.

Answer 1

So, you first need to create the function the trigger will fire.

In a rush sorry, here is another thread with an example:

https://stackoverflow.com/questions/13753320/creating-triggers-and-functions-in-postgresql

Create the function that returns the trigger and then the trigger itself attached to the table. CartoDB runs on top of PostgreSQL 9.3, so if you need help with triggers search on Internet for PostgreSQL questions in general, and it should all apply to CartoDB.

Good luck and sorry I could not provide a full example.

Answer 2

Here are the triggers I ended up using:

CREATE FUNCTION codispatch()
RETURNS trigger AS $$

BEGIN

UPDATE coastal SET ag_dispatch_level = obs.named_ag_dispatch_level
FROM obs
WHERE obs.created_at = (SELECT MAX(created_at) FROM obs)
AND coastal.cartodb_id = 1;

RETURN NEW;

END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER update_coastal_dispatch
AFTER INSERT 
ON obs
FOR EACH ROW
EXECUTE PROCEDURE codispatch();

I had a second trigger but I just needed to change out the table names and alter the function.