Event triggers on MS SQL Server database
Tags: sql, graphql, database
Published: 2022-09-20 14:41:52

---
title: Event triggers on MS SQL Server database
published: true
date: 2022-09-16 07:03:35 UTC
tags: sql,graphql,database
canonical_url: https://hasura.io/blog/event-triggers-on-sql-server/
---

![Event triggers on MS SQL Server database](https://hasura.io/blog/content/images/2022/09/event_triggers--1-.png)

The [event triggers in Hasura](https://hasura.io/docs/latest/event-triggers/index/) enable you to perform custom business logic on your data. They capture events (insert, update, delete) on a specified table and then invoke an HTTP webhook to run the custom business logic.

This article shows how to use event triggers with an MS SQL Server database.

## SQL Server database

Before creating a Hasura application, you should have a SQL Server database available. Additionally, ensure that the database is reachable by Hasura.

Update the database settings to allow connections from the IP address of your Hasura project. You can find the IP address in the project's settings.

![Event triggers on MS SQL Server database](https://hasura.io/blog/content/images/2022/09/hasura-cloud-ip.png)

After that, you can connect the database to Hasura.

## Connect SQL Server database to Hasura

Create a new Hasura project and launch the project console.

[![Event triggers on MS SQL Server database](https://camo.githubusercontent.com/949a2db267b00f49757865d585f16687118d796d41064e21c3d933d21884d533/68747470733a2f2f6772617068716c2d656e67696e652d63646e2e6861737572612e696f2f696d672f6465706c6f795f746f5f6861737572612e706e67)](https://cloud.hasura.io/signup)

Then navigate to the "Data Manager" and connect Hasura to your existing SQL Server database. Choose a database name, select `MS SQL Server` and enter the database URL.

The database URL has the following format:

```
Driver={ODBC Driver 17 for SQL Server};Server=tcp:<b>hasura-test.database.windows.net</b>,<b>1433</b>;Database=<b>db-name</b>;Uid=<b>username</b>;Pwd=<b>password</b>;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;

```

![Event triggers on MS SQL Server database](https://hasura.io/blog/content/images/2022/09/hasura-data-manager-ms-sql-server.png)

After connecting the database, create the `user` table with the following fields:

- `id` of type "int" (Primary Key)
- `name` of type "varchar(250)"
- `email` of type "varchar(250)"

![Event triggers on MS SQL Server database](https://hasura.io/blog/content/images/2022/09/hasura-create-user-table.png)

Choose the `id` field as the primary key and save the table.

It's time to test the database connection. Navigate to the GraphiQL page and insert a new user into the database. Here's an example mutation you can use:

```
mutation MyMutation {
  insert_user(objects: {id: 1, name: "Hasura User", email: "hasura_user@email.com"}) {
    affected_rows
    returning {
      id
      name
      email
    }
  }
}

```

If the mutation is successful, you should see a response similar to the one in the image below.

![Event triggers on MS SQL Server database](https://hasura.io/blog/content/images/2022/09/hasura-mutation-example.png)

## Set up Hasura event trigger

With the event triggers, you need to have a webhook available that Hasura can invoke. In this case, let's assume you have a server that sends a welcome email to new users.

Each time a new user is added to the database, the server automatically sends a welcome email to that user.

Navigate to the "Events" page and create a new event trigger with the following details:

- Trigger name - `send_email`
- Database - `default`
- Schema/Table - `public/users`
- Trigger Operations - `insert`

In the "Webhook (HTTP/S) Handler" field, enter the endpoint Hasura needs to invoke when a new user is inserted into the database.

![Event triggers on MS SQL Server database](https://hasura.io/blog/content/images/2022/09/create-event-trigger-hasura-part-1.png)

Now scroll down until you see the section "Configure REST Connectors" and click on the "Add Request Options Transform" button.

That opens a new section where you can modify the request method and URL template. You need to change the URL template to pass the name and email of the newly created user.

In the "Query Params" section, add the following parameters:

- name - `{{$body.event.data.new.name}}`
- email - `{{$body.event.data.new.email}}`

![Event triggers on MS SQL Server database](https://hasura.io/blog/content/images/2022/09/change-request-options-transform.png)

Save the new event trigger and you are done!

## Test the event trigger

To test the event trigger, you need to insert a new user into the database. Run the following mutation in the GraphiQL editor:

```
mutation {
  insert_user(objects: {id: 2, name: "Charlie Kirby", email: "chby@email.com"}) {
    affected_rows
    returning {
      id
      name
      email
    }
  }
}

```

The figure illustrates the mutation in action. Since the mutation is successful, the event trigger should invoke the webhook, and the user should receive a welcome email.

![Event triggers on MS SQL Server database](https://hasura.io/blog/content/images/2022/09/hasura-mutation-create-user.png)

Checking the email inbox confirms that the user received the email.

![Event triggers on MS SQL Server database](https://hasura.io/blog/content/images/2022/09/email-example.png)

You can also check the "Invocation logs" to see whether the event trigger was successful or not.

![Event triggers on MS SQL Server database](https://hasura.io/blog/content/images/2022/09/event-triggers-invocation-logs-hasura.png)

This is just one example of using event triggers with a SQL Server database. The Hasura documentation covers [event triggers](https://hasura.io/docs/latest/event-triggers/index/) in-depth if you want to learn more. There is also an extensive section covering [MS SQL Server](https://hasura.io/docs/latest/databases/ms-sql-server/index/).

<!--kg-card-end: markdown-->