/* You can run this trigger from ISQL to load it into your database.  It will create
   a Certificate ID similar to the old CTAG IDs prior to the Version 7.3 Database.
   On Time Support is not responsible for the operation of this SQL script or any
   undesirable modifications to the customer database.  Customer accepts all
   responsibility for the use of this script.  If there are issues, you can contact
   On Time Support for assistance.  You should back up your database first before
   executing this script. */

Create TRIGGER "ots_calcert_trig" before insert order 92 on
mt.calibration
referencing new as indata
for each row
begin
  declare xmtag char(80);
  declare xasset char(80);
  declare xtime char(30);
  set xasset = (select i4201 from mt.inventory where mt.inventory.mtag = indata.mtag);
  set xtime = seconds('1970-01-01',indata.C2301)+seconds(current date,indata.C2333);
  set xmtag = string(xasset,':');
  set xmtag = string(xmtag,xtime);

  /* the following line contains the field number that is going to be stuffed with the
     cal cert number. The field length must be large enough to contain the asset#+11 chars
     or it will get truncated.
  */     
  set indata.c2354 = xmtag
end


/* On time Support is available for custom SQL work.  
    Feel free to contact us at support@ontimesupport.com
*/