FiveTech Support Forums

    declare @is_email LOGICAL;
    declare @email CHAR(100);

    declare @is_hosp_email LOGICAL;
    declare @hosp_email CHAR(100);
    
    declare @old cursor as select * from __old; 
    declare @new cursor as select * from __new;

    open @old; 
    fetch @old;
    open @new; 
    fetch @new;
         
    if @new.signed = TRUE AND @old.signed = FALSE then
 
        @is_email = ( SELECT is_email from doctors where doctors.id = @new.refer_id ); 
        @email = ( SELECT email from doctors where doctors.id = @new.refer_id );

        if @is_email = TRUE AND @email IS NOT NULL then
         
            INSERT INTO plmail ( pathno, doc_id, send_to ) 
                   VALUES ( @new.pathno, @new.refer_id,
                          ( SELECT email FROM doctors 
                            WHERE doctors.id = @new.refer_id 
                            AND doctors.is_email = TRUE 
                            AND doctors.email IS NOT NULL) ); 
        
            INSERT INTO plmail ( pathno, doc_id, send_to ) 
                   SELECT c.pathno, c.copyto, doc.email FROM copyto c 
                      LEFT JOIN doctors doc ON doc.id = c.copyto
                      WHERE c.pathno = @new.pathno 
                      AND doc.is_email = TRUE 
                      AND doc.email IS NOT NULL ;
  
         endif ;

        if @new.facility IS NOT NULL then 
            @is_hosp_email = ( SELECT TOP 1 is_email from hospital where hospital.name = @new.facility ); 
            @hosp_email = ( SELECT TOP 1 e_mail from hospital where hospital.name = @new.facility );

            if @is_hosp_email = TRUE AND @hosp_email IS NOT NULL then  
                INSERT INTO plmail ( pathno, doc_id, send_to )
                    SELECT @new.pathno, h.passwd, h.e_mail from hospital h
                        WHERE h.name = @new.facility ;
            endif;
        endif;
    endif;

    if @new.signed = FALSE AND @old.signed = TRUE then
         DELETE FROM plmail 
                WHERE pathno = @new.pathno 
                AND DateTime_Sent IS NULL ;

         DELETE FROM pthreps 
                WHERE pathno = @new.pathno;

     endif ;