Select data, setting a calculated value into a column

Question

Hello all,

I'm selecting data from a table within a trigger (FOR UPDATE). Some rows I'm selecting have been updated by a transaction, that initiated the trigger, and some rows are not. I'd like to write somewhere in the dataset a flag for every row, which value would be calculated as "id IN (SELECT [id] FROM INSERTED)". This flag would show, is a row updated with the last transaction or not.

Is it possible in SQL?

Sure, I can do 2 separate queries, with 2 different conditions, but the trigger perfomance is real bottleneck...

Answer 1

Here's an example for SQL Server:

if object_id('TriggerTest') is not null
    drop table TriggerTest

create table TriggerTest (id int identity, name varchar(50), inLastUpdate bit)

insert TriggerTest (name) values ('joe'), ('barrack'), ('george'), ('dick')
go
create trigger dbo.TriggerTestDelete
on TriggerTest
after update
as begin
    declare @date datetime
    set @date = GETDATE()

    update  dbo.TriggerTest
    set     inLastUpdate = 
                 case when id in (select id from inserted) then 1 
                       else 0 
                 end
end
go
update TriggerTest set name = name where id in (1,2)
update TriggerTest set name = name where id in (1,3)
select * from TriggerTest

This prints:

id    name        inLastUpdate
1     joe         1
2     barrack     0
3     george      1
4     dick        0