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

You have a MAJOR flaw here. You are assuming there will only ever be a single row in the inserted table. You need to make this set based. You can use a join in an update statement which is what you need here. — Sean Lange
– Sean Lange, Commented Feb 5, 2018 at 15:17
Can you write it or give me some example? I am beginner and really don't know how to even correct it... — blaise
– blaise, Commented Feb 5, 2018 at 15:26
This is obviously homework and I am not going to write this for you. How are you going to learn the subject if people online do your work for you? Try searching for using FROM in an UPDATE statement. It has been asked and answered around here dozens if not hundreds of times. — Sean Lange
– Sean Lange, Commented Feb 5, 2018 at 15:31
@blaise Added the trigger approach. And your question is fine for SO, but it is missing is a true question (meaning with a question mark) hence the CodeReview suggestion. If you ended on Below is my code attempt, what am I doing wrong? then it would be perfect for SO. I don't get the hate on helping with homework when someone has clearly tried, and most people here feel the same way. — Aaron Dietz
– Aaron Dietz, Commented Feb 7, 2018 at 14:47

Aaron Dietz · Accepted Answer · 2018-02-07 14:38:41Z

2

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

edited Feb 7, 2018 at 14:38

answered Feb 5, 2018 at 20:05

Aaron Dietz

10.3k1 gold badge17 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

creating a trigger in transact SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related