创建用于分配NULL值的触发器

Question

我正在尝试在ssms中创建一个触发器

这是我到目前为止所拥有的，但肯定看起来不正确：

Drop Trigger if exists trg_assignEmail
Go
Create Trigger 
    trg_assignEmail 
On StudentInformation
For Insert 
As
Begin
    Insert Into StudentInformation
    Set Email = Null
    Select rtrim(FirstName + LastName) + '@gmail.com'
    From StudentInformation
Where Email is Null

模式：

Create Table StudentInformation (
    StudentID int not null identity (100,1),
    Title nchar(50) null, 
    FirstName nchar (50) null,
    LastName nchar (50) null,
    Address1 nchar (50) null,
    Address2 nchar (50) null,
    City nchar (50) null, 
    County nchar (50) null,
    Zip nchar (10) null, 
    Country nchar (50) null,
    Telephone nchar (50) null,
    Email nchar (50) null, 
    Enrolled nchar (50) null,
    AltTelephone nchar(50) null
    Constraint PK_StudentID Primary Key (StudentID)
);

Answer 1

触发代码会引起各种错误：首先，INSERT ... SET ... FROM ...不是有效的SQL语法。

我认为您用例的相关方法是创建一个AFTER INSERT触发器，它将检查刚刚插入的值（使用伪表inserted），并在需要时更新Email。

。

CREATE TRIGGER trg_assignEmail ON StudentInformation
AFTER INSERT
As
BEGIN
    UPDATE s
    SET s.Email = TRIM(i.FirstName) + TRIM(i.LastName) + '@gmail.com'
    FROM StudentInformation s
    INNER JOIN inserted i ON i.StudentID = s.StudentID AND i.email IS NULL
END

inserted上的INNER JOIN用于选择刚刚插入的记录，如果给出了Email。

DB小提琴上的演示：

insert into StudentInformation(Title, FirstName, LastName) values('Foo', 'Bar', 'Baz');
select Title, FirstName, LastName, Email from StudentInformation;

标题|firstName |lastname |电子邮件：----- |：---------- |：-------- |：-----------------------foo |酒吧|巴兹|[e-mail]

Answer 2

2017年前，请尝试。并不是说trim（）无法用rtrim（）或ltrim（）替换，也无法处理空字符串（使用表单保存时的应用程序常见）

）

Create Trigger 
    trg_assignEmail 
On StudentInformation
For Insert 
As
Begin
    declare @email as nchar(50)
        ,@id int
    select @email = Email, @id = StudentID from inserted
    if nullif(@email,'') is null 
        begin
            update StudentInformation
            set Email = rtrim(FirstName + LastName) + '@gmail.com'
            where StudentID = @id
        end
end