mardi 13 septembre 2016

IF condition not working in trigger

I have a trigger that is manage bulk loads from another table. When a bulk load of rows are inserted into the Projects table. The trigger is fired, however the conditions don't work for when the rows already exist in the inserted table.

The purpose of this trigger is to insert new rows that are inserted into the projects table to the vat_matrix table. If rows exist in the vat_matrix table then update them with the new values from the projects table.

I have tried a few options and found that the cursor is the only solution to iterate through the inserted rows. This works, however I can't get the if condition to work to update existing rows.

Are there any suggestions?

 SET ANSI_NULLS ON

GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[update_vat] ON [dbo].[projects]
FOR UPDATE, INSERT
AS

DECLARE @project AS VARCHAR(25)
DECLARE @client AS VARCHAR(25) 
DECLARE @dim2 AS VARCHAR(25)
DECLARE @period_from AS INT
DECLARE @period_to AS INT 
DECLARE @status AS CHAR(1)
DECLARE @user_id AS VARCHAR(25)
DECLARE @unit_id AS VARCHAR(25)
DECLARE @sequence_no AS INT 

SELECT @project = project,
@client = client,
@dim2 = dim2,
@period_from = period_from,
@period_to = period_to,
@status = status,
@user_id = user_id,
@unit_id = unit_id
FROM inserted

--FIND MAXIMUM VALUE FOR SEQUENCE NUMBER
SET @sequence_no = (SELECT MAX(v) FROM (SELECT sequence_no FROM agldefmatdet WHERE matrix_id = 11) AS value(v));

BEGIN

DECLARE tbl_cursor CURSOR LOCAL FOR

      SELECT project, client, dim2, period_from, period_to, status, user_id, unit_id FROM inserted


    SET NOCOUNT ON;

  IF EXISTS (SELECT * FROM inserted WHERE inserted.project = @project) AND NOT EXISTS (SELECT * FROM deleted WHERE deleted.project = @project)
  BEGIN

   OPEN tbl_cursor
    FETCH NEXT FROM tbl_cursor INTO @project, @client, @dim2, @period_from, @period_to, @status, @user_id, @unit_id

    WHILE @@FETCH_STATUS = 0
    BEGIN

    INSERT INTO [dbo].[vat_matrix] 
        (att_val_from_1, 
            att_val_from_2,
            att_val_from_3,
            att_val_from_4,
            att_val_to_1,
            att_val_to_2,
            att_val_to_3,
            att_val_to_4, 
            att_value_1,
            att_value_2,
            att_value_3,
            att_value_4, 
            client, 
            dim_value, 
            last_update, 
            matrix_id, 
            period_from,
            period_to,
            sequence_no,
            status, 
            user_id)
    SELECT @project, 
            '', 
            '',
            '', 
            @project, 
            '', 
            '',
            '',
            @project, 
            '', 
            '',
            '',
            @client, 
            @dim2, 
            GETDATE(), 
            '11',
            '0',
            '0',
            @sequence_no + 1, 
            @status, 
            @user_id

      FETCH NEXT FROM tbl_cursor INTO @project, @client, @dim2, @period_from, @period_to, @status, @user_id, @unit_id

    --RETURN;

    END

    CLOSE tbl_cursor
    DEALLOCATE tbl_cursor

END

  IF EXISTS (SELECT * FROM inserted WHERE inserted.project = @project) AND EXISTS (SELECT * FROM deleted WHERE deleted.project = @project)
  BEGIN

  OPEN tbl_cursor
    FETCH NEXT FROM tbl_cursor INTO @project, @client, @dim2, @period_from, @period_to, @status, @user_id, @unit_id

    WHILE @@FETCH_STATUS = 0
    BEGIN

    UPDATE vat_matrix
      SET dim_value = @dim2,
                last_update = GETDATE(),
                period_from = @period_from, 
                period_to = @period_to,
                status = @status,
                user_id = @user_id
                WHERE att_val_from_1 = @project 
                AND matrix_id = '11' 
                AND client = 'LU'

      FETCH NEXT FROM tbl_cursor INTO @project, @client, @dim2, @period_from, @period_to, @status, @user_id, @unit_id
    END

    CLOSE tbl_cursor
    DEALLOCATE tbl_cursor

    --RETURN;
END



END;

Aucun commentaire:

Enregistrer un commentaire