creating a trigger in transact SQL

Question

I created simple table in Microsoft SQL Server and now I want to create "probably" easy trigger.

Here is my task:

When creating or modifying an employee, complete this column email consisting of:

• the first two letters of the firstname,
• one dot,
• the first four letters of the surname and string '@gmail.com'.

Remember to remove any external whitespace and characters to swap the inner spaces for the underscore.

This is my table which I'm using for trigger:

CREATE TABLE  employee
(
empID int IDENTITY(1,1) PRIMARY KEY,
surname varchar(20),
firstname varchar(20),
age int,
email varchar(100)
)

I've tried to do it. Here is my version. Please check it and write what do you think about my solution:

create trigger create_email on employee
for insert,update
as
begin           
    declare @var_email varchar(100)
    set @var_email = substring((select firstname from inserted),1,2) + '.' + substring((select surname from inserted),1,4) + '@gmail.com'
    set @var_email = replace(@var_email,' ','_')
    update employee set email = @var_email where empID in (select empID from inserted)
end

end

Answer 1

It would make more sense to use a computed column:

CREATE TABLE employee
(
empID int IDENTITY(1,1) PRIMARY KEY,
surname varchar(20),
firstname varchar(20),
age int,
email as REPLACE(LTRIM(RTRIM(LEFT(firstname,2)+'.'+LEFT(surname,4)+'@gmail.com')),' ','_')
)

The trigger version would be:

CREATE TRIGGER create_email ON employee
FOR INSERT, UPDATE
AS
BEGIN
    UPDATE e
    SET e.email = REPLACE(LTRIM(RTRIM(LEFT(i.firstname,2)+'.'+LEFT(i.surname,4)+'@gmail.com')),' ','_')
)
    FROM employee e
    JOIN inserted i on e.empID = i.empID
END