SQL Server 使用插入的表在SQL中创建触发器以连接数据,但最后有两行

a0zr77ik  于 2022-11-28  发布在  其他
关注(0)|答案(1)|浏览(107)

我试图创建一个触发器,当插入名字和姓氏(没有电子邮件),它会自动生成电子邮件,但它给我两行。一个是电子邮件为空,另一个是正确的结果。

CREATE TRIGGER trg_assignEmail
ON StudentInformation 
FOR INSERT
AS 
BEGIN
DECLARE 
    @FirstName NVARCHAR (50),
    @LastName NVARCHAR (50),
    @Email NVARCHAR (100)
    
SELECT
    @FirstName = inserted.FirstName,
    @LastName = inserted.LastName,
    @Email = inserted.Email
FROM
    inserted 

IF  NOT EXISTS (SELECT Email FROM inserted)
    
    SET @Email = @FirstName+'.'+@LastName+'@disney.com'
    
    SELECT 
        @FirstName = inserted.FirstName,
        @LastName = inserted.LastName,
        @Email = TRIM(@FirstName)+'.'+TRIM(@LastName)+'@disney.com'
    FROM 
        inserted
    

        INSERT INTO StudentInformation 
      (
             FirstName, LastName, Email
            )
        values (@FirstName, @LastName, @Email)
END

The results

py49o6xq

py49o6xq1#

我猜您正在寻找类似于以下内容的内容:

CREATE TRIGGER trg_assignEmail
ON StudentInformation 
INSTEAD OF INSERT
AS 
BEGIN
DECLARE 
    @FirstName NVARCHAR (50),
    @LastName NVARCHAR (50),
    @Email NVARCHAR (100)
    
    SELECT
        @FirstName = INSERTED.FirstName,
        @LastName = INSERTED.LastName,
        @Email = INSERTED.Email
    FROM
        INSERTED 
 
    IF (@Email IS NULL)
        BEGIN  
          SET @Email = TRIM(@FirstName)+'.'+TRIM(@LastName)+'@disney.com'
        END
            
  
    INSERT INTO StudentInformation (FirstName, LastName, Email)
    VALUES (@FirstName, @LastName, @Email)
END

当一个新的记录被插入到StudentInformation中时,需要检查是否提供了电子邮件地址,如果没有提供,则创建一个新的电子邮件地址。因此,在我上面的尝试中,使用了INSTEAD OF INSERT来在INSERT语句之前进行检查。

相关问题