Feature: provide a $start variable similar to $before to compare current transaction changes to record

[og-dev]

Original: Bug: $before var is null on event

Describe the bug

When creating a new record, the variable $before is empty(null).
Before inserting a new record, under certain conditions, it may be necessary to modify or perform some operation on the data.

When trying to access the value contained in $before in a CREATE event, $before is null.

I understand that when creating a new record, $before could be empty because there is no previous data for the new record, but it is necessary to have the possibility to access the values of a new record in a CREATE event before inserting it.

Steps to reproduce

-- Create schemafull user table.
DEFINE TABLE user SCHEMAFULL;

-- Define some fields.
DEFINE FIELD firstName ON TABLE user TYPE string;
DEFINE FIELD lastName ON TABLE user TYPE string;
DEFINE FIELD dinner_table ON TABLE user TYPE string;

-- SEE IT IN ACTION
-- 1: Add a user with all required fields.
CREATE user CONTENT {
    firstName: 'Tobie',
    lastName: 'Hitchcock',
    dinner_table: 'restaurant_table:AB101Z'
};

-- Assign dinner table whenever a new user is created
-- or assign new table if not available
DEFINE EVENT new_user ON TABLE user WHEN $event = "CREATE" THEN {
    LET $table_is_available = (SELECT is_reserved FROM restaurant_table WHERE id == $before.dinner_table);
    IF $table_is_available == FALSE THEN
	-- Change selected dinner table before insert a new record
        -- Reassigning another table
	$before.dinner_table = 'another_dinn_table_here'
    END 

};

Expected behaviour

Something similar to a before insert trigger in SQL.

The modification of the example is possible to do it with $after, I know. What I am trying to show is that it is not possible to access or modify a record before inserting it. Doing it after, means executing another query unnecessarily.

SurrealDB version

1.4.2 for windows on x86_64

Contact Details

No response

Is there an existing issue for this?

• I have searched the existing issues

Code of Conduct

• I agree to follow this project's Code of Conduct

[phughk]

Hi @og-dev, the $before variable is a reflection of the state of the storage and not a running track of the state. We may want to include a running track of the state, but that isn't currently possible.
I am changing this to a feature request instead as it is working as intended.