Trigger for automatically updating email field?

I am trying to create a trigger which fires when a new row is inserted into my StudentInformation table without the Email field specified. The trigger should fire and update the email field with following pattern:

[e-mail](e.g. Erik Kellener would be [e-mail])

If the insert statement already contains an email address, the trigger does not update the Email field. (e.g ignores the trigger’s action)

So something like this:

create trigger trg_assignEmail

on StudentInformation
for insert
as begin
     if (email is null ) then
     update email 
     set email = (pattern)
....

Can someone please help?

Answer

In SQL Server, you would typically do this with an instead of insert trigger.

The idea is to select the values that were given for insert from pseudo-table inserted, and then apply the business rule on the email column.

Note that this requires listing all columns for insert. Assuming that you have firstName, lastName and email, that would be:

create trigger trgAssignEmail on StudentInformation
instead of insert
as
    set nocount on
    insert into StudentInformation(firstName, lastName, email)
    select 
        firstName,
        lastName,
        coalesce(email,  firstName + '.' + lastName + '@disney.com')
    from inserted

An alternative is to use an after insert trigger that updates the last inserted row(s) whose email is null. This is less efficient (since you need to scan the table for the modified row(s)), but saves you from listing all columns. For this, we need a primary key – let me assume id:

create trigger trgAssignEmail on StudentInformation
after insert
as
    set nocount on
    update s
    set s.email  = s.firstName + '.' + s.lastName + '@disney.com'
    from StudentInformation s
    inner join inserted i on i.id = s.id
    where s.email is null