CODEWITHSUNDEEP
sqlsql-serverloopsselecttriggers
Arif YILMAZ
Arif YILMAZ

Reputation: 5866

loop through data in sql server trigger

I have a sql-server trigger which does a select at the begining. That select returns hundreds of rows, and I want to loop those rows. How can I loop the returned data?

here is my trigger, I want to loop @ProfId

Create TRIGGER EmailQueueRaw_Control_Trigger ON EmailQueueRaw FOR INSERT
AS
BEGIN
   SELECT @ProfId= ProfessionalID,@Email= Email from Professionals 
END

How can I loop @ProfId?

Upvotes: 2

Views: 5126

Answers (1)

Devart
Devart

Reputation: 121912

Try this solution -

ALTER TRIGGER [LabourAPI].[trg_IOIU_Employee]

   ON [LabourAPI].[Employee]
   INSTEAD OF INSERT, UPDATE

AS BEGIN

    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE
          @EmployeeID INT
        , @PersonID INT
        , @TabelNumber VARCHAR(6)
        , @DepartmentUID UNIQUEIDENTIFIER
        , @PositionID INT
        , @DateIn DATETIME
        , @DateOut DATETIME
        , @IsMainWorkPlace BIT
        , @DatePension DATETIME
        , @IsLabourUnionMember BIT
        , @FiringReasonID INT

    DECLARE cur CURSOR LOCAL READ_ONLY FAST_FORWARD FOR
    SELECT
          EmployeeID
        , PersonID
        , TabelNumber
        , DepartmentUID
        , PositionID
        , DateIn
        , DateOut
        , IsMainWorkPlace
        , DatePension
        , IsLabourUnionMember
        , FiringReasonID
    FROM INSERTED

    OPEN cur

    FETCH NEXT FROM cur INTO
          @EmployeeID
        , @PersonID
        , @TabelNumber
        , @DepartmentUID
        , @PositionID
        , @DateIn
        , @DateOut
        , @IsMainWorkPlace
        , @DatePension
        , @IsLabourUnionMember
        , @FiringReasonID

    WHILE @@FETCH_STATUS = 0 BEGIN

        SELECT
              @PersonID 
            , @TabelNumber 
            , @DepartmentUID 
            , @PositionID 
            , @DateIn 
            , @DateOut 
            , @IsMainWorkPlace 
            , @DatePension 
            , @IsLabourUnionMember 
            , @FiringReasonID

        FETCH NEXT FROM employee INTO
              @EmployeeID
            , @PersonID
            , @TabelNumber
            , @DepartmentUID
            , @PositionID
            , @DateIn
            , @DateOut
            , @IsMainWorkPlace
            , @DatePension
            , @IsLabourUnionMember
            , @FiringReasonID

    END

    CLOSE cur
    DEALLOCATE cur

END

Upvotes: 4

Related Questions