How to modify a record from EVENT

[jamiecarl]

Hello! Day 2 of SurrealDB for me, and I'm trying to figure out some common concepts I've been using for years in other relational databases.

One that I can't figure out is how to modify a record in an EVENT while it is being updated. For example, a table has a modified_on field that I would like to automatically update to the time::now() during an UPDATE query.

In PostgreSQL I would define a TRIGGER function which just modifies the column on the NEW record and returns is. Is there something similar in SurrealDB? I have tried to modify the field on $before and $after. No luck. Copilot suggested calling UPDATE tablename SET modified_on = time::now(); from within the event, but obviously this results in an infinite loop.

How would I do this in SurrealDB, or is this concept achieved in a different way that I have not learned yet?

Thanks for any guidance!

[suleimanas]

You must have figured it out by now :) the key is in WHEN
WHEN $event = "UPDATE" AND $before.modified_on = $after.modified_on

[jamiecarl]

You must have figured it out by now :) the key is in WHEN WHEN $event = "UPDATE" AND $before.modified_on = $after.modified_on

To be honest, I gave up and moved on to other things hoping to circle back to this issue when I had time.

Such a simple solution, I have to admit I'm a bit embarrassed I didn't think of it. LOL.

Thanks!