CODEWITHSUNDEEP
sqlsql-servertriggersinsert
kim
kim

Reputation: 1

Using trigger to automatically update field when inserting a new row

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!

Upvotes: 0

Views: 2721

Answers (2)

Stu
Stu

Reputation: 32579

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 which are not blank (and implied also not NULL).

update s 
  set s.email = Concat(s.FirstName, '.', s.LastName, '@youremail.com')
from inserted i 
join Studentinformation s on s.StudentId = i.StudentId 
where i.email != '';

Upvotes: 1

Meyssam Toluie
Meyssam Toluie

Reputation: 1071

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

Upvotes: 0

Related Questions