I have a table with a primarykey ID and a unique key for ID_EMP and OSID
When I insert I want to populate the OSID column whth the next int value related to id_emp
My trigger works but is returning the error message:
Warning: Null value is eliminated by an aggregate or other SET operation.
CREATE TRIGGER os_osid
ON ordemdeservico
FOR INSERT
AS
BEGIN
DECLARE @osid int, @id int
SELECT @osid = CASE WHEN MAX(ordemdeservico.osid) IS NULL THEN 1 ELSE MAX(ordemdeservico.osid) + 1 END
FROM inserted
LEFT JOIN ordemdeservico ON ordemdeservico.id_emp = inserted.id_emp
WHERE ordemdeservico.id_emp = inserted.id_emp
SELECT @id = inserted.id
FROM inserted
UPDATE ordemdeservico
SET ordemdeservico.osid = @osid
WHERE ordemdeservico.id = @id
END