简单的T-SQL而不是触发器

小编典典

简单的T-SQL而不是触发器

sql

有人可以通过板载触发器帮助解决简单的t-
sql脚本问题吗？我使用非常简单的触发器将数据从一个表复制到另一个表（这些表之间没有关系）。当我尝试在触发器创建之后（从同一脚本）直接在第一时间插入数据时，我得到了期望的结果，但是所有下一次尝试都在下一个提示下失败了：’
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.‘我很困惑，这意味着什么。查看下面的触发器：

CREATE TRIGGER AuthorInsert ON Author
INSTEAD OF INSERT
AS
BEGIN -- //- 1 -//
--***************** if insert was correct ********************
    IF (SELECT COUNT(*) FROM INSERTED) > 0
    BEGIN --//- 2 -//
        DECLARE @id int, @roleId int;
        DECLARE @nameId int, @reestrCodeId int, @passportDataId int, @addressId int, @phoneId int;
        SET @nameId = (SELECT INSERTED.NameID FROM INSERTED);
        SET @reestrCodeId = (SELECT INSERTED.ReestrCodeID FROM INSERTED);
        SET @passportDataId = (SELECT INSERTED.PassportDataID FROM INSERTED);
        SET @addressId = (SELECT INSERTED.AddressID FROM INSERTED);
        SET @phoneId = (SELECT INSERTED.PhoneID FROM INSERTED);
        BEGIN TRY
            INSERT INTO Role(RoleName) VALUES('Author');
        END TRY
        BEGIN CATCH
        END CATCH
        SET @roleId = (SELECT Role.RoleID FROM Role WHERE Role.RoleName = 'Author');
        INSERT INTO Employee(NameID, ReestrCodeID, RoleID, PassportDataID, AddressID, PhoneID)VALUES
                            (@nameId, @reestrCodeId, @roleId, @passportDataId, @addressId, @phoneId);
        SET @id = (SELECT Employee.EmployeID FROM Employee WHERE Employee.EmployeID = @@IDENTITY) + 1;
        INSERT INTO Author VALUES(@id, @nameId, @reestrCodeId, @passportDataId, @addressId, @phoneId);
    END -- //- 2 -//
END -- //- 1 -//

没有理由发布表格的图表，因为它非常原始（正如我在上面提到的那样，这些表格之间没有直接的关系）是显而易见的。我的脚本中有什么错误？该奇怪的错误实际上意味着什么？

阅读 243

2021-03-23

共1个答案

小编典典

我不确定BEGIN TRY块的意义是什么。很明显，您想确保有一个Role命名Author-但是您真的需要每次在触发器中检查它吗？您现在可以填充它，而不必再次检查吗？

创建/更改/引用对象时，请始终使用架构前缀。
我BEGIN/END在模块主体周围添加了适当的包装，并添加了SET NOCOUNT ON;
我更改COUNT(*) from inserted为IF EXISTS。当您真的不在乎它是1还是10或6000时，没有理由去烦恼实际计数。
我从中删除了使用变量作为值的方法inserted。正如@marc_s指出的那样，您不能依赖于此，因为触发器是按语句而不是按行触发的。因此，如果您有一个多行插入，那么您现有的触发器将只处理过任意一行。
我删除了TRY/CATCH块。在大多数情况下，比起让SQL Server为您进行检查，您自己检查违例更为有效，尤其是在触发器中，因为这样做不仅会影响触发代码，而且还会影响外部事务（如您所见）。尤其是在您的情况下，此触发器第一次运行后可能每次都会引发异常。

我不得不猜测的列名称Author。请始终在SELECT和INSERT语句中包括您的列列表。除了该文章中指出的原因外，它还使其他人可以更轻松地帮助您重新编写代码。

CREATE TRIGGER dbo.AuthorInsert ON dbo.Author INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT 1 FROM inserted)
BEGIN
DECLARE @emps TABLE(id INT, NameID INT);
DECLARE @RoleID INT;
SELECT @RoleID = RoleID FROM dbo.Roles WHERE RoleName = ‘Author’;

IF @RoleID IS NULL
BEGIN
  -- probably not necessary to do this over and over again
  -- unless someone is sabotaging your Roles table.
  INSERT dbo.Roles(RoleName) SELECT 'Author';
  SELECT @RoleID = SCOPE_IDENTITY();
END

INSERT dbo.Employee(NameID, ReestrCodeID, RoleID, PassportDataID, 
  AddressID, PhoneID) OUTPUT inserted.EmployeeID, inserted.NameID 
  INTO @emps SELECT NameID, ReestrCodeID, @RoleID, PassportDataID, 
   AddressID, PhoneID FROM inserted;

-- this seems redundant. If an author is linked to an employee, 
-- why do we need to store all of this information again?
INSERT dbo.Author(EmployeeID, NameID, ReestrCodeID, RoleID, 
  PassportDataID, AddressID, PhoneID)
  SELECT e.id, i.NameID, i.ReestrCodeID, @RoleID, 
    i.PassportDataID, i.AddressID, i.PhoneID FROM @emps AS e
    INNER JOIN inserted AS i ON e.NameID = i.NameID;

END
END
GO

2021-03-23