简单的 t-sql 而不是触发器 [英] Simple t-sql instead of trigger

问题描述

有人可以帮助解决简单的 t-sql 脚本问题吗?我使用非常简单的触发器将数据从一个表复制到另一个表(这些表之间没有关系).当我尝试在触发器创建(来自同一个脚本)之后第一次直接插入数据时，我得到了想要的结果，但是所有接下来的尝试都会失败，并显示下一个提示:'当前事务无法提交并且无法支持以下操作写入日志文件.回滚事务.'我很困惑这是什么意思.看看下面的触发器:

Can anybody help to resolve simple t-sql script's issue with triggers onboard? I use very simple trigger to copy data from one table to another(there are no relationship between those tables). When I try to insert data at first time directly after trigger creating (from the same script) I get desired result, but all next attempts get the fail with next prompt:'The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.'I'm confused what does that mean. Look at trigger below:

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 -//

没有理由张贴表格的图表，因为它非常原始(正如我在上面提到的那样，这些表格之间没有直接关系)显而易见.我的脚本有什么问题，那个奇怪的错误实际上是什么意思?

There is no reason to post tables' diagram because it's very primitive(as i bring up above there is no directly relationship between those tables) obvious. What wrong in my script and what does actyally that strange error mean?

推荐答案

我不确定 BEGIN TRY 块的重点是什么.很明显你想确保有一个名为 Author 的 Role - 但是你真的需要每次都在触发器中检查它吗?您现在不能填充它，而且永远不必再次检查吗?

I'm not sure what the point of the BEGIN TRY block is. It's clear you want to make sure there is a Role named Author - but do you really need to check this in the trigger every time? Can't you populate it now, and never have to check again?

• 请始终使用架构创建/更改/引用对象时的前缀.
• 我在模块主体周围添加了适当的 BEGIN/END 包装器，并添加了 SET NOCOUNT ON;
• 我将 COUNT(*) 从插入的 更改为 IF EXISTS.如果您真的不关心它是 1、10 还是 6000，则没有理由费心检索实际计数.
• 我从 inserted 中删除了对值的变量的使用.正如@marc_s 指出的那样，您不能依赖于此，因为触发器是按 语句 触发的，而不是按 行 触发的.因此，如果您有一个多行插入，您现有的触发器将只处理任意一行.
• 我删除了 TRY/CATCH 块.在大多数情况下，自己检查违规比检查违规更有效让 SQL Server 为您做这件事，尤其是在触发器中，因为它不仅对触发器代码有影响，而且对外部事务有影响(如您所见).特别是在您的情况下，在此触发器第一次运行后可能每次都会引发异常.
• 我不得不猜测Author 的列名.请始终包括您的SELECT 和 INSERT 语句 中的列列表.除了该博文中所述的原因之外，它还可以让其他人更轻松地帮助您重新编写代码.

• Please always use the schema prefix when creating/altering/referencing objects.
• I added proper BEGIN/END wrapper around module body, and added SET NOCOUNT ON;
• I changed COUNT(*) from inserted to IF EXISTS. There's no reason to bother retrieving the actual count when you really don't care if it's 1 or 10 or 6000.
• I removed the use of variables for values from inserted. As @marc_s pointed out, you can't rely on this because triggers are fired per statement, not per row. So if you have a multi-row insert, your existing trigger would have only ever processed one arbitrary row.
• I removed the TRY/CATCH block. In most cases it is more efficient to check for violations yourself than to have SQL Server do it for you, and in a trigger especially so because it has an effect not only on the trigger code but also the outer transaction (as you have seen). Especially in your case, where an exception would be raised probably every time after the first time this trigger runs.
• I had to guess at the column names for Author. Please always include your column list in SELECT and INSERT statements. In addition to the reasons stated in that post, it also makes it easier for others to help you re-write your code.

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

这篇关于简单的 t-sql 而不是触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！