create table mt.ots_status_updates(
asset char(40),
status_before char(8),
status_after char(80),
Userid char(30),
Site char(10),
Record_Date timestamp,
Check_Flag timestamp);
commit;

------------------------------------------------------------------

alter TRIGGER "ots_LI_Status_Update" after update of i4225 order 415 on inventory referencing old as olddata new as newdata for each row
begin
insert into mt.ots_status_updates (asset, status_before, status_after, Userid, Site, Record_date)
select i4201, olddata.i4225, newdata.i4225, I4237, I4240, now (*) from mt.inventory where i4201 = newdata.i4201 and i4240 = 'CCAFS'

end

------------------------------------------------------------------
-- This lookup is the status is changed to LI

select * from mt.ots_status_updates where (status_after = 'LI' or status_before = 'LI') and check_flag is null;
update  mt.ots_status_updates set check_Flag = now(*) where (status_after = 'LI' or status_before = 'LI') and check_flag is null;

commit;