CREATE TRIGGER tr_PCA_WorkOrder_Completion ON WORK_ORDER
AFTER UPDATE
AS

declare @tos_status nvarchar(50)
declare @i_closed_time datetime
declare @d_closed_time datetime

-- Query Closed Time before and after update
select @i_closed_time = closed_time from inserted
select @d_closed_time = closed_time from deleted

-- if closed time of inserted is not null but closed time of deleted is then we're interested
if @i_closed_time is not null and @d_closed_time is null
begin
        -- Find current status of associated tracked object
        select @tos_status = status from tracked_object_status tos, inserted i where tos.tobj_status_key = i.tobj_status_key

        -- If tracked object status record set to close, add record to PCA_Events table
        if @tos_status = 'Closed'
        begin
                INSERT INTO PCA_EVENTS ( connector_id, object_key,
                [object_name], object_verb,
                event_priority, event_status, event_description, other_object_key)
                SELECT 'ProductionCentreAdapter', order_key, 'WORK_ORDER', 'Update', 1, 0, 'WorkOrder Completion', tobj_status_key
                FROM INSERTED
        end
end