DEFINE EVENT statement

Events allow you to define custom logic that is executed when a record is created, updated, or deleted. These events are triggered automatically within the current transaction after data modifications in the record, giving you access to the state of the record before $before and after $after the change.

Note

Events are a side effect of other operations and thus are not triggered when data is imported.

Key Concepts

• Events: Triggered after changes (create, update, delete) to records in a table.

• $event: A preset parameter containing the type of event as a string, will always be one of “CREATE”, “UPDATE”, or “DELETE”.

• $before / $after: Refer to the record state before and after the modification. Learn more about the $before and $after parameters in the parameters documentation.
• $value: The record in question. For a CREATE or UPDATE event, this will be the record after the changes were made. For a DELETE statement, this will be the record before it was deleted.
• WHEN condition: Determines when the event should be triggered.

Requirements

• You must be authenticated as a root owner or editor, namespace owner or editor, or database owner or editor before you can use the DEFINE EVENT statement.
• You must select your namespace and database before you can use the DEFINE EVENT statement.

Statement syntax

SurrealQL Syntax
DEFINE EVENT [ OVERWRITE | IF NOT EXISTS ] @name ON [ TABLE ] @table [ WHEN @expression ] THEN @expression [ COMMENT @string ]

Clauses:

• OVERWRITE: Replaces the existing event if it already exists.
• IF NOT EXISTS: Only creates the event if it doesn’t already exist.
• WHEN: Conditional logic that controls whether the event is triggered.
• THEN: Specifies the action(s) to execute when the event is triggered.
• COMMENT: Optional comment for describing the event.

Example usage

• Email Change Detection: Create an event that logs whenever a user’s email is updated.

In this example:

• The WHEN clause checks if the email has changed.
• The THEN clause records this change in a log table.

-- Create a new event whenever a user changes their email address
-- One-statement event
DEFINE EVENT OVERWRITE test ON TABLE user WHEN $before.email != $after.email THEN (
    CREATE log SET 
        user       = $value.id,
        // Turn events like "CREATE" into string "email created"
        action     = 'email' + ' ' + $event.lowercase() + 'd',
        // `email` field may be NONE, log as '' if so
        old_email  = $before.email ?? '',
        new_email  = $after.email  ?? '',
        at         = time::now()
);
UPSERT user:test SET email = 'old_email@test.com';
UPSERT user:test SET email = 'new_email@test.com';
DELETE user:test;
SELECT * FROM log ORDER BY at ASC;

Output
[
	{
		action: 'email created',
		at: d'2024-11-25T02:59:41.003Z',
		id: log:e3thw1l0q7xiapznar1f,
		new_email: 'old_email@test.com',
		old_email: '',
		user: user:test
	},
	{
		action: 'email updated',
		at: d'2024-11-25T02:59:41.003Z',
		id: log:uaarfyk191jgod06xobm,
		new_email: 'new_email@test.com',
		old_email: 'old_email@test.com',
		user: user:test
	},
	{
		action: 'email deleted',
		at: d'2024-11-25T02:59:41.003Z',
		id: log:mlkag8h1xotglpz9wt2i,
		new_email: '',
		old_email: 'new_email@test.com',
		user: user:test
	}
]

More Complex Logic:

• Purchase Event with Multiple Actions: Log a purchase and establish relationships between the customer and product.

DEFINE EVENT purchase_made ON TABLE purchase
    WHEN $before == NONE
    THEN {
        LET $customer = (SELECT * FROM customer WHERE id = $after.customer);
        LET $product = (SELECT * FROM product WHERE id = $after.product);

        RELATE $customer->bought->$product CONTENT {
            quantity: $after.quantity,
            total: $after.total,
            status: 'Pending',
        };

        CREATE log SET
            customer_id = $after.customer,
            product_id = $after.product,
            action = 'purchase_created',
            timestamp = time::now();
    };

In this example:

• We perform multiple actions when a purchase is created: establishing relationships using the RELATE statement and creating a log entry.

Specific events

You can trigger events based on specific events. You can use the variable $event to detect what type of event is triggered on the table.

-- CREATE event is triggered when a new record is inserted into the table.
-- Here we are updating the status of the post to PUBLISHED
-- when a new record is inserted into the publish_post table.
DEFINE EVENT publish_post ON TABLE publish_post
    WHEN $event = "CREATE"
    THEN (
        UPDATE post SET status = "PUBLISHED" WHERE id = $after.post_id
    );

-- UPDATE event
-- Here we are creating a notification when a user is updated.
DEFINE EVENT user_updated ON TABLE user
    WHEN $event = "UPDATE"
    THEN (
        CREATE notification SET message = "User updated", user_id = $after.id, created_at = time::now()
    );

-- DELETE event is triggered when a record is deleted from the table.
-- Here we are creating a notification when a user is deleted.
DEFINE EVENT user_deleted ON TABLE user
    WHEN $event = "DELETE"
    THEN (
        CREATE notification SET message = "User deleted", user_id = $before.id, created_at = time::now()
    );

-- You can combine multiple events based on your use cases.
-- Here we are creating a log when a user is created, updated or deleted.
DEFINE EVENT user_event ON TABLE user
    WHEN $event = "CREATE" OR $event = "UPDATE" OR $event = "DELETE"
    THEN (
        CREATE log SET
            table = "user",
            event = $event,
            happened_at = time::now()
    );

Using IF NOT EXISTS clause

Available since: v1.3.0

The IF NOT EXISTS clause can be used to define an event only if it does not already exist. You should use the IF NOT EXISTS clause when defining an event in SurrealDB if you want to ensure that the event is only created if it does not already exist. If the event already exists, the DEFINE EVENT statement will return an error.

It’s particularly useful when you want to safely attempt to define a event without manually checking its existence first.

On the other hand, you should not use the IF NOT EXISTS clause when you want to ensure that the event definition is updated regardless of whether it already exists. In such cases, you might prefer using the OVERWRITE clause, which allows you to define a event and overwrite an existing one if it already exists, ensuring that the latest version of the event definition is always in use

-- Create a EVENT if it does not already exist
DEFINE EVENT IF NOT EXISTS example ON example THEN {};

Using OVERWRITE clause

Available since: v2.0.0

The OVERWRITE clause can be used to define an event and overwrite an existing one if it already exists. You should use the OVERWRITE clause when you want to modify an existing event definition. If the event already exists, the DEFINE EVENT statement will overwrite the existing event definition with the new one.

-- Create an EVENT and overwrite if it already exists
DEFINE EVENT OVERWRITE example ON example THEN {};

Events and permissions

Queries inside the event always execute without any permission checks, even when triggered by changes made by the currently authenticated user. This can be very useful to perform additional checks and changes that involve tables/records that are inaccessible for the user.

Consider a CREATE query sent by a record user that has CREATE access to the comment table only:

CREATE comment SET
    post = post:tomatosoup,
    content = "So delicious!",
    author = $auth.id
;

By having the following event defined, SurrealDB will perform the additional checks and changes:

DEFINE EVENT on_comment_created ON TABLE comment
    WHEN $event = "CREATE"
    THEN {
        -- Check if the post allows for adding comments.
        -- User record doesn't have access to the `post` table.
        IF $after.post.disable_comments {
            THROW "Can't create a comment - Comments are disabled for this post";
        };

        -- Set the `approved` field on the new comment - automatically approve
        -- comments made by the author of the post.
        -- For security reasons, record users don't have any permissions for the `approved` field.
        UPDATE $after.id SET
            approved = $after.post.author == $after.author;
    };