Cursor on a trigger

Question

I'm using SQL Server 2008.

I have an after trigger for INSERT, UPDATE and DELETE action defined in the table. My problem is that currently my trigger inserts one record at a time and I need multiple records as for one

SELECT TOP 1 @ParentID FROM ... WHERE ID = @ID 

returns multiple unique records.

(See this comment below "-- this subquery returns more than 1 value, so I need to insert in the search Audit table as many ParentIDs as it returns")

I believe I need to use cursor, but I'm not sure where exactly to declare and open cursor.

--CREATE PROCEDURE [dbo].[SP_Auditing]
--      @ID INT, @Code VARCHAR(3), @AuditType VARCHAR(10), @ParentCode VARCHAR(3) = NULL, @ParentID INT = NULL
--AS
--BEGIN
--          INSERT INTO myDB.dbo.Table1 (ID, Code, AuditType, ParentCode, ParentID)

--          VALUES(@ID, @Code, @AuditType, @ParentCode, @ParentID)
--END

GO

CREATE TRIGGER [dbo].[Tr_MyFavouriteTable_UPD_INSERT_DEL]    ON [dbo].[MyFavouriteTable] AFTER INSERT, DELETE, UPDATE    NOT FOR REPLICATION 

AS   

BEGIN
            DECLARE @ID INT,            @Code VARCHAR(3),           @AuditType VARCHAR(10),             @ParentCode VARCHAR(3),             @ParentID INT       SET     @Code = 'DOC'

    IF EXISTS (SELECT 1 FROM inserted)          AND 
       NOT EXISTS (SELECT 1 FROM deleted) 
                BEGIN 
                        SELECT TOP 1  
                @ID = ins.ID, 
                @ParentID = (
                    SELECT TOP 1 CAST(RIGHT(parentId,LEN(parentId) - LEN(LEFT(parentId,3))) AS INT) 
                    FROM [MyDB].[dbo].[MyFavouriteTable] t WITH (NOLOCK)
                    INNER JOIN [MyDB2].[dbo].[MyView] v WITH (NOLOCK)
                        ON t.Id = v.ID
                    WHERE v.ID = @ID --284 
                ), **-- this subquery returns more than 1 value, so I need to insert in the search Audit table as many ParentIDs as it returns**
                @AuditType = 'INSERT'           FROM inserted ins
                        IF @ID IS NOT NULL 
               AND 
               @ParentID IS NOT NULL
               AND 
               @ParentCode IS NOT NULL    

            EXEC [MyDB].[dbo].SP_Auditing] @ID, @Code, @AuditType, @ParentCode, @ParentID 
                END

--  below is the same logic for UPDATE and DELETE actions...

The stored procedure above simply inserts data into the Audit table.

Answer 1

See Alex Kudryashev's answer. I needed to tweak a little his logic to sort out duplicate records with the same ParentIDs for the insertion into the Audit table. I added one more cte just below Alex's cte_Audit as follows

CREATE TRIGGER [dbo].[Tr_MyFavouriteTable_UPD_INSERT_DEL]
    ON [dbo].[MyFavouriteTable] AFTER INSERT, DELETE, UPDATE
        NOT FOR REPLICATION 
AS   

BEGIN
    ;with act as (
    select isnull(i.id,d.id) id, --either deleted or inserted is not null
    case when i.id is not null and d.id is not null then 'update'
         when i.id is not null then 'insert'
         else 'delete' end auditType
    from inserted i full outer join deleted d on i.id = d.id
    ),
    audit_cte as (
    SELECT act.id, 'DOC' Code,
           CAST(RIGHT(parentId,LEN(parentId) - LEN(LEFT(parentId,3))) AS INT) parentid,
           act.auditType, 'parentcode' parentCode
    FROM [MyDB].[dbo].[MyFavouriteTable] t WITH (NOLOCK)
    INNER JOIN [MyDB2].[dbo].[MyView] v WITH (NOLOCK)  ON t.Id = v.ID
    inner join act on act.id = t.id
    )
    insert myDB.dbo.Table1 (ID, Code, AuditType, ParentCode, ParentID)
    select id,code,AuditType, ParentCode, ParentID
    from audit_cte
    where parentCode is not null and parentid is not null
         ,CTE_dupsCleanup AS (
                SELECT DISTINCT
                Code,
                Id, 
                AuditType,
                ParentCode,
                ParentId,

  --   ROW_NUMBER() OVER(PARTITION BY ParentId, ParentCode, AuditType ORDER BY ParentId) AS Rn

        FROM AUDIT_CTE 
                WHERE ParentCode IS NOT NULL 
                    AND ParentId IS NOT NULL )


Then using Rn = 1 inserted only unique records into the Auidt table. Like this: 

                    INSERT [ISSearch].[dbo].[SearchAudit] (Code, ID, AuditType, ParentCode, ParentID)
                SELECT
                Code,
                ID, 
                AuditType,
                ParentCode,
                ParentId
                FROM CTE_dupsCleanup 
                --  WHERE Rn = 1
END

Answer 2

Why do you need to get the records one by one? From my understanding you want to keep the log.

IF EXISTS (SELECT 1 FROM inserted)          AND 
   NOT EXISTS (SELECT 1 FROM deleted) 
            BEGIN 
            INSERT INTO [Your_Log_Table] 
            SELECT
            ins.ID, [Code],'INSERT',[PrentCode],
            (SELECT TOP 1 CAST(RIGHT(parentId,LEN(parentId) - 
                LEN(LEFT(parentId,3))) AS INT) 
                FROM [MyDB].[dbo].[MyFavouriteTable] t WITH (NOLOCK)
                INNER JOIN [MyDB2].[dbo].[MyView] v WITH (NOLOCK)
                    ON t.Id = v.ID
                WHERE v.ID = ins.ID --284 
            )
                FROM inserted ins
            END      

Answer 3

Never use scalar variables in triggers because insert, update, and delete may affect multiple rows. As to your trigger, try something like this.

CREATE TRIGGER [dbo].[Tr_MyFavouriteTable_UPD_INSERT_DEL]
    ON [dbo].[MyFavouriteTable] AFTER INSERT, DELETE, UPDATE
        NOT FOR REPLICATION 
AS   

BEGIN
    ;with act as (
    select isnull(i.id,d.id) id, --either deleted or inserted is not null
    case when i.id is not null and d.id is not null then 'update'
         when i.id is not null then 'insert'
         else 'delete' end auditType
    from inserted i full outer join deleted d on i.id = d.id
    ),
    audit_cte as (
    SELECT act.id, 'DOC' Code,
           CAST(RIGHT(parentId,LEN(parentId) - LEN(LEFT(parentId,3))) AS INT) parentid,
           act.auditType, 'parentcode' parentCode
    FROM [MyDB].[dbo].[MyFavouriteTable] t WITH (NOLOCK)
    INNER JOIN [MyDB2].[dbo].[MyView] v WITH (NOLOCK)  ON t.Id = v.ID
    inner join act on act.id = t.id
    )
    insert myDB.dbo.Table1 (ID, Code, AuditType, ParentCode, ParentID)
    select id,code,AuditType, ParentCode, ParentID
    from audit_cte
    where parentCode is not null and parentid is not null
end