14,503 questions
The error is originating from the trigger, specifically the part where you are assigning values to the variables from the inserted
and deleted
tables. These tables might have more than one row, especially if the UPDATE operation in the main procedure affects multiple rows. Since the subquery is directly assigning values to the scalar variables, it's expecting only one row, but multiple rows are causing the error.
CREATE TRIGGER [dbo].[TRG_TRIGGER_NAME]
ON [dbo].[table_a]
AFTER UPDATE, INSERT
AS
BEGIN
-- Inserting new rows into prod_sinc for rows that don't exist
INSERT INTO prod_sinc (cod_emp, cod_filial, cod_prod, data_ult_alt)
SELECT i.cod_emp,
i.cod_filial,
i.cod_prod,
GETDATE()
FROM inserted i
LEFT JOIN deleted d ON i.cod_prod = d.cod_prod AND i.cod_filial = d.cod_filial
LEFT JOIN prod_sinc ps ON i.cod_prod = ps.cod_prod AND i.cod_filial = ps.cod_filial
WHERE (i.status_prod <> d.status_prod OR d.status_prod IS NULL)
AND ps.cod_prod IS NULL;
-- Updating existing rows in prod_sinc
UPDATE ps
SET data_ult_alt = GETDATE()
FROM prod_sinc ps
JOIN inserted i ON i.cod_prod = ps.cod_prod AND i.cod_filial = ps.cod_filial
LEFT JOIN deleted d ON i.cod_prod = d.cod_prod AND i.cod_filial = d.cod_filial
WHERE (i.status_prod <> d.status_prod OR d.status_prod IS NULL);
END