https://supabase.com/ logo
Join Discord
Powered by
Need some help with triggers (and functions) in Su...
# help
j
Need some help with triggers (and functions) in Supabase. I created a small table, with a 
last_active timestamptz
column, and the thought was to have a trigger set the field to 
now()
on every INSERT and UPDATE. So, I created a function for this (which returns a trigger) and then set that as a trigger "on" the table. However, I can't see that anything is happening
n
Hello @joa! This thread has been automatically created from your message in #843999948717555735 a ``few seconds ago``. Pinging @User so that they see this as well! Want to unsubscribe from this thread? Right-click the thread in Discord (or use the ... menu) and select Leave Thread to unsubscribe from future updates. Want to change the title? Use the 
/title
command! We have solved your problem? Click the button below to archive it.
j
The function is called "update_lastactive_timestamp_column" (returns TRIGGER) and is:
Copy code
BEGIN
   NEW.last_active = now(); 
   RETURN NEW;
END;
I used the Add a new Trigger page to create the trigger, where I selected the table, events (INSERT + UPDATE), and trigger type "Before the event". Then, it asks for a function (which returns a trigger), and I selected my function (above)
I was expecting the timestamp to update if I were to insert a row, or change (update) a row, in the Table Editor? I also tried an insert from the SQL Editor -- deliberately omitting the 
last_active
field -- but I only wound up with value NULL
g
Your function seems OK. Is the trigger on the correct table, last_active is correct column? Did you use per row and not statement?
n
joa (2022-05-01)
j
Hey 🙂
Let me re-check!
It was on "Statement"...
g
try row
j
Sure, that makes sense!
Before or After?
g
before if you want to change something always
j
Gotcha 🙂
Perfect! It works now 🙂 Thanls
Mayyybe this is a weird approach, but my idea here is to have a table (tick) which contains essentially one row per sensor, with the sensor ID and the last_active. The sensors will POST to the table every (say) 5 mins, just their ID, such that the last_active field shows when the sensor was last active
In case it goes offline, or powers off, then the tick table will hold that information...
I don't want the sensors to be responsible for actually submitting the timestamp -- this should be the responsibility of the backend (supabase)
hehe, they actually don't even have a clock (ESP8266)
g
As long as you don't need the exact time the sensor collected the info to less than a second, should be fine. Just with internet delays you don't know the exact time the reading was taken.
j
No, I only want to be able to see if some sensor is offline 🙂
g
pretty easy to run a query then and see which sensors have not ticked in the past 10 minutes or longer.
j
yeah!
so, the sensors main job is to submit some data to an "events" table, when something happens. I'll use the same trigger on this table as well, to log the time
And it occurs to me, it might be important to know -- after the fact -- that a sensor was indeed offline for some time! If it comes back online, it'll update the timestamp and I won't have any record of the downtime
So, maybe I should make another trigger which will insert an event (sensor offline or something) when a sensor becomes "stale" 🙂
Would be another trigger on 
tick
table, but which would call a different function which would log the event "internally"
Just thinking out loud 😄 sorry
g
I would just have the trigger function you just made compare old.time and new.time if they are greater than is OK then you do an sql insert to an error table with the sensor id and what ever info you want to log it is down. You could even have another column in the same table as your updated time that gets set for error and not cleared until you clear it with a separate call.
j
hehe yes, that's of course much simpler!
thanks again
PreviousNext
Powered by