# NEW is null in a trigger function

> Why NEW (or a field of it) is null inside a Postgres trigger. Covers statement-level triggers, DELETE triggers, wrong return values and missing user metadata in sign-up triggers.

Source: https://supahost.app/kb/new-is-null-in-trigger · Updated 2026-11-11

## What you see

One of these inside a trigger function:

```
ERROR:  55000: record "new" is not assigned yet
DETAIL:  The tuple structure of a not-yet-assigned record is indeterminate.
```

```
ERROR:  23502: null value in column "message_id" of relation "audit_log" violates not-null constraint
```

Or no error at all: rows are silently not inserted, or a column the trigger was meant to fill stays empty.

## Why it happens

`NEW` holds the row being written, but only in some kinds of trigger:

| Trigger | `NEW` | `OLD` |
|---|---|---|
| `for each row`, INSERT | the new row | null |
| `for each row`, UPDATE | the row after the change | the row before |
| `for each row`, DELETE | null | the deleted row |
| `for each statement` | null | null |

So the usual causes are:

- **The trigger is `for each statement`.** It fires once per statement with no row. `for each statement` is the default if you leave it out.
- **The trigger also fires on DELETE** and the function reads `NEW` unconditionally.
- **A `before` trigger returns the wrong thing.** Returning `null` from a row-level `before` trigger cancels the write for that row with no error. On DELETE, return `OLD`.
- **The row is fine but the field is empty.** A sign-up trigger reads `NEW.raw_user_meta_data ->> 'display_name'`, but the app never sent that key.

## How to fix it

1. Check how the trigger is defined:

   ```sql
   select tgname, pg_get_triggerdef(oid)
   from pg_trigger
   where tgrelid = 'public.messages'::regclass and not tgisinternal;
   ```

   If it says `FOR EACH STATEMENT`, recreate it per row:

   ```sql
   drop trigger if exists messages_audit on public.messages;

   create trigger messages_audit
   after insert or update or delete on public.messages
   for each row execute function public.audit_message();
   ```

2. Branch on `TG_OP` and use the right record:

   ```sql
   create or replace function public.audit_message()
   returns trigger
   language plpgsql
   set search_path = ''
   as $$
   begin
     if tg_op = 'DELETE' then
       insert into public.audit_log (message_id, action) values (old.id, tg_op);
       return old;
     end if;

     insert into public.audit_log (message_id, action) values (new.id, tg_op);
     return new;
   end;
   $$;
   ```

   In an `after` trigger the return value is ignored, but returning the record keeps the function safe to reuse as a `before` trigger.

3. For sign-up triggers on `auth.users`, send the metadata when signing up and give it a fallback:

   ```ts
   await supabase.auth.signUp({
     email,
     password,
     options: { data: { display_name: 'Sam Rivera' } },
   })
   ```

   ```sql
   insert into public.profiles (id, display_name)
   values (
     new.id,
     coalesce(new.raw_user_meta_data ->> 'display_name', split_part(new.email, '@', 1))
   );
   ```

   OAuth providers fill metadata with their own keys (often `full_name`, `name` or `avatar_url`), so look at a real user's `raw_user_meta_data` in Studio before relying on a key.

4. Debug with `raise log` or `raise notice`:

   ```sql
   raise notice 'op=%, new=%, old=%', tg_op, new, old;
   ```

   Notices show in Studio's SQL editor when you fire the trigger there.

## On SupaHost

Studio's Logs pages aren't available, so `raise log` output from triggers fired by your app isn't searchable in the dashboard. Reproduce the write in Studio's SQL editor (*Dashboard → your project → **Database** tab → Open Studio*) and use `raise notice` to see the values.

## Related

- [Triggers](/docs/database/triggers)
- [Database functions](/docs/database/functions)
- [Database error saving new user](/kb/database-error-saving-new-user)
- [Users and the auth schema](/docs/auth/users)