Триггер SQL - преобразование курсора с помощью операции на основе набора

#sql #triggers #cursor

#sql #триггеры #курсор

Вопрос:

Я пытаюсь понять основы работы на основе набора. Я прочитал, что операция на основе набора намного лучше по производительности, чем цикл в курсоре. Какие шаги мне предпринять, чтобы преобразовать курсоры в операции набора? Вот пара примеров, с которыми я работаю

 CREATE Trigger DataUpdate ON [Data]
    FOR UPDATE
    AS
      SET NOCOUNT ON
    BEGIN
       declare @Id int
       declare cur cursor for select DataId from Deleted
       open cur
       fetch next from c into @Id
       while @@FETCH_STATUS = 0
          begin
            if 
               UPDATE(ID) and
               UPDATE(Title) or
               UPDATE(Description)             
             BEGIN
                update Data 
                set ModDate = getdate()
                where ID = @Id
             END
            fetch next from c into @Id
          end
       close cur
       deallocate cur
    end

И еще один триггер

 CREATE  TRIGGER DataAudit
      ON [Data] FOR UPDATE
      AS

      SET NOCOUNT ON

    IF UPDATE(ModDate)
         BEGIN
           SET NOCOUNT ON
           DECLARE @ParentId INT
           DECLARE @ChildId INT
           DECLARE @Export BIT

           DECLARE cursorInserted CURSOR FOR
              select ParentID, ChildID, Export from INSERTED

           OPEN  cursorInserted 

           Fetch next from cursorInserted into @ParentId, @ChildId, @Export

           WHILE @@FETCH_STATUS = 0
           BEGIN             
               DECLARE @brief BIT

               DECLARE cursorBriefcase CURSOR FOR
                select ShowExport
                  from Sites 
              where SiteID in (select ds.siteid from DataSiteIDs as ds where ChildID = @ChildId)   

               OPEN cursorBriefcase
                 -- Perform the first fetch.
                 FETCH NEXT FROM cursorBriefcase INTO @brief

                 -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
                 WHILE @@FETCH_STATUS = 0 and @brief = 0
                   BEGIN
                      FETCH NEXT FROM cursorBriefcase INTO @brief
                   END
                 CLOSE cursorBriefcase
                 DEALLOCATE cursorBriefcase

             IF @brief = 0 and 
                @Export = 1   and 
                ((SELECT Distinct ParentID FROM Sites Where ParentID = @ParentId AND (TemplateID = 5 OR TemplateParams = 1) ) > 0) and 
                ((Select Distinct ParentID From SubUserGroupIDs Where ParentID = @ParentId) > 0)
             BEGIN
                -- Populate the SubAuditItems table with rows on hold
                INSERT INTO SubAuditItems (ChildID, ParentID, RecordDate, Type, FromTable)
                VALUES (@ChildId, @ParentId,  GETDATE(), 'MOD', 'Data')
             END

           Fetch next from cursorInserted into @ParentId, @ChildId, @Export
           END
           CLOSE cursorInserted
           DEALLOCATE cursorInserted    

        END

Ответ №1:

@Yusuf: Не могли бы вы рассказать нам немного подробнее? чего вы хотите достичь? если вы хотите сохранить записи, удаленные из таблицы, вы можете сделать это следующим образом:

 CREATETRIGGER [dbo].[UpdateCustomerHistory]
    ON [dbo].[tblCustomers]
AFTER DELETE
AS 

---------------------------------------------------
-- Insert the deleted Record in History
---------------------------------------------------
INSERT INTO tblCustomersHistory
(
    CustomerId, Name, DateCreated
)
------ Get deleted row
SELECT  CustomerId, Name, GETDATE()
FROM    DELETED

1. Спасибо за идею. Ну, в принципе, я хочу пару вещей, в первом случае, когда когда-либо обновляется определенное поле, я хочу автоматически обновлять измененную дату. Это можно делать по одному, но мне нужно обрабатывать для массового обновления, когда несколько элементов будут обновляться одновременно с помощью процедуры. Во втором случае я хочу обновить другую таблицу на основе некоторого условия, которое может исходить из нескольких таблиц.

2. Можете ли вы подробно описать структуру таблиц и некоторые примеры данных?

Ответ №2:

Это должно быть сделано для тела первого триггера:

 UPDATE d                 
 SET ModDate = getdate()
 FROM inserted i
 JOIN data d ON i.id = d.ID
 JOIN deleted del ON i.id = del.id
 WHERE 
   (i.Id <> del.id)
 OR
   (i.title <> del.title)
 OR
   (i.description <> del.description)

Если я правильно интерпретировал ваш второй пример, тело триггера будет выглядеть примерно так:

  INSERT INTO SubAuditItems (ChildID, ParentID, RecordDate, Type, FromTable)  
 SELECT i.ChildID,i.ParentID, GETDATE(), 'MOD', 'Data' 
 FROM INSERTED 
 JOIN DataSiteIDs s ON ds.childid = i.childid
 JOIN Sites s ON ds.siteid = s.siteid
 WHERE i.Export = 1