Simple t-sql instead of trigger

Question

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?

Answer 1

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?

• 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