Trigger vs procedure to update a calculated column-CodePudding

I coded a trigger that calculated the value of a column (expiryDate) whenever the value of another column (productionDate) changed from null, and my professor told me I should have used a stored procedure:

create or alter trigger tg_expiry_date
on product
after insert, update
as
begin
    if exists (select productionDate
               from inserted as i
               where productionDate is not null)
    begin
        begin try
            declare @orderId int = (    select max(orderId) -- avoids updating previously inserted rows
                                        from inserted ) 

            declare @date date =   (    select productionDate 
                                        from inserted
                                        where orderId = @orderId )

            begin tran
            update product
                set expiryDate = (      select dateadd(day, 90, @date) -- adds 90 days to the production date
                                        from inserted 
                                        where orderId = @orderId 
                                 )
                where orderId = @orderId 
            commit tran
        end try

        begin catch
            if @@trancount > 0
            begin
                rollback tran
                exec usp_showerrorinfo
            end
        end catch
    end
end
go

However, I don't really agree with her suggestion. Using a procedure every time a value changes can be too tedious and it should be automatically calculated. Why did she tell me that? I guess it can be a little spaghetti-code-ish, and the max in the set shouldn't exist, but I don't know how to implement it.

CodePudding user response：

Simply add a computed column, ideal for a scenario such as this.

alter table product drop column ExpiryDate;
go
alter table product add ExpiryDate as dateadd(day, 90, productionDate);