CREATE OR REPLACE TRIGGER TR_Delete_test1 AFTER DELETE ON test1
FOR EACH ROW
BEGIN
IF (TEST_CLASSSTYPE<>1) OR (TEST_CLASSTYPE=1 AND TEST_FINAL<>'Y') THEN
 INSERT INTO test2(
 TEST_LOINC_NUM,TEST_COMPONENT, TEST_PROPERTY, TEST_TIME_ASPCT,TEST_SYSTEM, TEST_TIME_ASPCT,TEST_SYSTEM, TEST_SCALE_TYP, TEST_METHOD_TYP,                                                                                                                                                                                              
   TEST_RELAT_NMS,TEST_CLASS, TEST_SOURCE, TEST_DT_LAST_CH, TEST_CHNG_TYPE, TEST_COMMENTS, TEST_ANSWERLIST, TEST_STATUS, TEST_MAP_TO,                                                                                                                                                                                                            
    TEST_SCOPE, TEST_NORM_RANGE, TEST_IPCC_UNITS, TEST_REFERENCE, TEST_EXACT_CMP_SY, TEST_MOLAR_MASS, TEST_CLASSTYPE, TEST_FORMULA, TEST_SPECIES,                                                                                                                                                                                          
     TEST_EXMPL_ANSWERS, TEST_ACSSYM, TEST_BASE_NAME, TEST_FINAL, TEST_NAACCR_ID, TEST_CODE_TABLE, TEST_SETROOT, TEST_PANELELEMENTS,                                                                                                                                                                                                         
      TEST_SURVEY_QUEST_TEXT, TEST_SURVEY_QUEST_SRC, TEST_SURVEY_QUEST_SRC, TEST_UNITSREQUIRED, TEST_SUBMITTED_UNITS, TEST_RELATEDNAMES2,                                                                                                                                                                                                  
       TEST_SHORTNAME, TEST_ORDER_OBS, TEST_CDISC_COMMON_TESTS, TEST_HL7_FIELD_SUBFIELD_ID, TEST_EXTERNAL_COPYRIGHT_NOTICE, TEST_EXAMPLE_UNITS,
       TEST_INPC_PERCENTAGE, TEST_LONG_COMMON_NAME)
  VALUES
  (:old.TEST_LOINC_NUM, :old.TEST_COMPONENT, :old.TEST_PROPERTY, :old.TEST_TIME_ASPCT, :old.TEST_SYSTEM, :old.TEST_TIME_ASPCT, :old.TEST_SYSTEM, :old.TEST_SCALE_TYP, :old.TEST_METHOD_TYP,                                                                                                                                                                                              
   :old.TEST_RELAT_NMS, :old.TEST_CLASS, :old.TEST_SOURCE, :old.TEST_DT_LAST_CH, :old.TEST_CHNG_TYPE, :old.TEST_COMMENTS, :old.TEST_ANSWERLIST, :old.TEST_STATUS, :old.TEST_MAP_TO,                                                                                                                                                                                                            
    :old.TEST_SCOPE, :old.TEST_NORM_RANGE, :old.TEST_IPCC_UNITS, :old.TEST_REFERENCE, :old.TEST_EXACT_CMP_SY, :old.TEST_MOLAR_MASS, :old.TEST_CLASSTYPE, :old.TEST_FORMULA ,:old.TEST_SPECIES,                                                                                                                                                                                          
     :old.TEST_EXMPL_ANSWERS, :old.TEST_ACSSYM, :old.TEST_BASE_NAME, :old.TEST_FINAL, :old.TEST_NAACCR_ID, :old.TEST_CODE_TABLE, :old.TEST_SETROOT, :old.TEST_PANELELEMENTS,                                                                                                                                                                                                         
      :old.TEST_SURVEY_QUEST_TEXT, :old.TEST_SURVEY_QUEST_SRC, :old.TEST_SURVEY_QUEST_SRC, :old.TEST_UNITSREQUIRED, :old.TEST_SUBMITTED_UNITS, :old.TEST_RELATEDNAMES2,                                                                                                                                                                                                  
       :old.TEST_SHORTNAME, :old.TEST_ORDER_OBS, :old.TEST_CDISC_COMMON_TESTS, :old.TEST_HL7_FIELD_SUBFIELD_ID, :old.TEST_EXTERNAL_COPYRIGHT_NOTICE, :old.TEST_EXAMPLE_UNITS,
       :old.TEST_INPC_PERCENTAGE, :old.TEST_LONG_COMMON_NAME);
END IF;
 END TR_DELETE_test1;
 /



 FOR MSSQL:

CREATE TRIGGER [dbo].TR_DELETE_TEST1
ON TEST1
FOR DELETE
AS
IF (TEST_CLASSSTYPE<>1) OR (TEST_CLASSTYPE=1 AND TEST_FINAL<>'Y') THEN
BEGIN
INSERT TEST2
SELECT
	TEST_LOINC_NUM,TEST_COMPONENT, TEST_PROPERTY, TEST_TIME_ASPCT,TEST_SYSTEM, TEST_TIME_ASPCT,TEST_SYSTEM, TEST_SCALE_TYP, TEST_METHOD_TYP,                                                                                                                                                                                              
        TEST_RELAT_NMS,TEST_CLASS, TEST_SOURCE, TEST_DT_LAST_CH, TEST_CHNG_TYPE, TEST_COMMENTS, TEST_ANSWERLIST, TEST_STATUS, TEST_MAP_TO,                                                                                                                                                                                                            
        TEST_SCOPE, TEST_NORM_RANGE, TEST_IPCC_UNITS, TEST_REFERENCE, TEST_EXACT_CMP_SY, TEST_MOLAR_MASS, TEST_CLASSTYPE, TEST_FORMULA, TEST_SPECIES,                                                                                                                                                                                          
        TEST_EXMPL_ANSWERS, TEST_ACSSYM, TEST_BASE_NAME, TEST_FINAL, TEST_NAACCR_ID, TEST_CODE_TABLE, TEST_SETROOT, TEST_PANELELEMENTS,                                                                                                                                                                                                         
        TEST_SURVEY_QUEST_TEXT, TEST_SURVEY_QUEST_SRC, TEST_SURVEY_QUEST_SRC, TEST_UNITSREQUIRED, TEST_SUBMITTED_UNITS, TEST_RELATEDNAMES2,                                                                                                                                                                                                  
        TEST_SHORTNAME, TEST_ORDER_OBS, TEST_CDISC_COMMON_TESTS, TEST_HL7_FIELD_SUBFIELD_ID, TEST_EXTERNAL_COPYRIGHT_NOTICE, TEST_EXAMPLE_UNITS,
        TEST_INPC_PERCENTAGE, TEST_LONG_COMMON_NAME
FROM Deleted
END
GO