create or replace trigger Tig_CNOOC_ZJDC_MAXIMO_ONOFF
after insert or update on CNOOC_ZJDC_MAXIMO_ONOFF
for each row

declare _assetNo varchar2(100);

declare _dateTime date;

declare _tagName varchar2(400);

declare _dataValue number(15,3);

declare _type varchar2(10);

declare _tableName varchar2(50);

declare _tableColumn varchar2(50);

declare _sql varchar2(2000);

if inserting or updating then
   _tagName:=:new.TAGID;
   _dataValue:=:new.DATAVALUE;
   _type:=:new.TYPE;
   _assetNo:=:new.EQNUM;
   _dateTime:=:new.DATETIME;
   
   declare _cnt number;
   _sql:='select count(TagName) into '||_cnt||',TagName into '||_tableName||',TableName into '||_tableColumn||' from DC_RALATIONSHIP_TAG where TagName='||_tagName||' and Type='||_type||'';
   execute immediate _sql;
   
   if _cnt>0 then  
      declare _num number;
      _sql:='select count(*) into '||_num||' from '||_tableName||' where EQNUM='||_assetNo||' and DATETIME=todate('||_dateTime||','yyyy:MM:dd hh24:mi:ss')';
      execute immediate _sql;
      if _num=0 then       
         _sql:='insert into _tableName(EQNUM,DATETIME,'||_tableColumn||') values('||_assetNo||',todate('||_dateTime||','yyyy:MM:dd hh24:mi:ss'),'||_dataValue||')';
         execute immediate _sql;
      else      
         _sql:='update '||_tableName||' set '||_tableColumn||'='||_dataValue||' where EQNUM='||_assetNo||' and DATETIME=todate('||_dateTime||','yyyy:MM:dd hh24:mi:ss')';
         execute immediate _sql;
      end if
   end if
   
end if