DROP FUNCTION IF EXISTS ies.t_insured_policy_insert CASCADE;
CREATE  FUNCTION ies.t_insured_policy_insert()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO IES.T_POLICY_SUMMARY
		(PolicySummaryID,Series,Number,LastName,FirstName,Patronymic,Birthday,DateBegin,DateEnd,Sex,SMO, InsuredID)
	SELECT 
		NEW.InsuredPolicyID , t2.Series, t2.Number, NEW.LastName, NEW.FirstName, COALESCE(NEW.Patronymic, 'НЕТ'), NEW.Birhday, NEW.DateBegin, case when t2.OmsType = 2 then NEW.DateStop else COALESCE(NEW.DateStop, NEW.DateEnd) end, NEW.Sex, NEW.SMO, NEW.Insured
	FROM  IES.T_POLICY t2
	WHERE t2.PolicyID = NEW.Policy and t2.Number is not null;
	
    RETURN NEW; 
END;
$$ LANGUAGE plpgsql;


DROP TRIGGER IF EXISTS t_insured_policy_after_insert ON ies.t_insured_policy;
CREATE TRIGGER t_insured_policy_after_insert
AFTER INSERT ON ies.t_insured_policy
FOR EACH ROW EXECUTE FUNCTION ies.t_insured_policy_insert();