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