Using trigger to automatically update field when inserting a new row-CodePudding

I have this table Studentinformation
I want to create a trigger so that whenever a new row is inserted, without the Email column specified, the trigger will fire and automatically update the Email column. The email format will look like this FirstName.LastName@youremail.com

For example: ('John', 'Smith')
This would look like: [email protected]

However, if the insert statement already contains an email address, the trigger will not update the email field.

Can someone help me write or modify the query I have so far?

Studentinformation table:

CREATE TABLE Studentinformation (
                    StudentID int NOT NULL IDENTITY(100,1) PRIMARY KEY,
                    Title char(10) NULL,
                    FirstName char(10) NULL,
                    LastName char(10) NULL,
                    Address1 char(50) NULL,
                    Address2 char(50) NULL,
                    City char(50) NULL,
                    County char(50) NULL,
                    Zip char(10) NULL,
                    Country char(50) NULL,
                    Telephone char(10) NULL,
                    Email varchar(255) NULL,
                    Enrolled char(10) NULL,
                    AltTelephone char(10) NULL);

What I have so far:

-- This code creates a trigger
DROP TRIGGER IF EXISTS trg_assignEmail;

CREATE TRIGGER trg_assignEmail ON Studentinformation
FOR INSERT
AS
UPDATE Studentinformation
SET Email = FirstName '.' LastName '@youremail.com'

Thank you in advance!

CodePudding user response：

The basic statement you need is as follows. Using the inserted table which contains only the row(s) inserted for the current server process, join back to the table on its primary key and update only the inserted rows. Using a case expression, if the inserted value is NULL or blank then apply your logic, otherwise leave it unchanged.

update s set s.email = 
  case when s.email is null or s.email = '' then
      Concat(s.FirstName, '.', s.LastName, '@youremail.com')
    else s.Email end
from inserted i join Studentinformation s on s.StudentId = i.StudentId

CodePudding user response：

I complete the answer with ISNULL function.

CREATE TRIGGER trg_assignEmail ON Studentinformation
After INSERT
AS  
    UPDATE s
      SET Email = Isnull(i.Email, Concat(i.FirstName, '.', i.LastName,'@youremail.com'))
    FROM inserted i JOIN Studentinformation s on s.StudentId = i.StudentId