TR_PROPERTYDETAIL_EXT_VALIDATE_IU

CREATE trigger TR_PROPERTYDETAIL_EXT_VALIDATE_IU on dbo.PROPERTYDETAIL_EXT for insert, update not for replication
as 
begin
set nocount on


    if exists(select * from inserted a join dbo.FINANCIALTRANSACTION b on a.id = b.id where not (isnull(b.BASEAMOUNT,((0))) = 0 OR SALEDATE is not null) )
            raiserror ('CK_PROPERTYDETAIL_SALEAMOUNT', 16, 1);

     if exists(select * from inserted a join dbo.FINANCIALTRANSACTION b on a.id = b.id where not (cast(b.POSTDATE as datetime) IS NULL OR SALEDATE is not null) )
            raiserror ('CK_PROPERTYDETAIL_SALEPOSTDATE', 16, 1);

    if exists(select * from inserted  a join dbo.FINANCIALTRANSACTION b on a.id = b.id where not ((SALEDATE is null) or ((cast(b.POSTDATE as datetime) is not null) or (isnull(case b.POSTSTATUSCODE when 2 then 0 when 1 then 1 when 3 then 2 end,((1))) = 2))) )
            raiserror ('CK_PROPERTYDETAIL_SALEPOSTDATE_REQUIRED', 16, 1);

    if exists(select * from inserted  a join dbo.FINANCIALTRANSACTION b on a.id = b.id where ((SALEDATE is null and isnull(b.BASEAMOUNT,((0))) = 0 and isnull(BROKERFEE,((0))) = 0 and b.POSTDATE is null and isnull (b.POSTSTATUSCODE,((1))) = 1) and (not SALEDATE is null)) )
            raiserror ('CK_PROPERTYDETAIL_SOLDORUNSOLD', 16, 1);

/* 
<CheckConstraint Name="CK_PROPERTYDETAIL_VALIDFORPAYMENTTYPE" CheckExpression="dbo.UFN_PROPERTYDETAIL_VALIDFORPAYMENTTYPE(ID) = 1"/>
<CheckConstraint Name="CK_PROPERTYDETAIL_SALEAMOUNT" CheckExpression="SALEAMOUNT = 0 OR SALEDATE is not null"/>
<CheckConstraint Name="CK_PROPERTYDETAIL_BROKERFEE" CheckExpression="BROKERFEE = 0 OR SALEDATE is not null"/>
<CheckConstraint Name="CK_PROPERTYDETAIL_SALEPOSTDATE" CheckExpression="SALEPOSTDATE IS NULL OR SALEDATE is not null"/>
<CheckConstraint Name="CK_PROPERTYDETAIL_VALIDSALEPOSTSTATUSCODE" CheckExpression="SALEPOSTSTATUSCODE = 1 OR SALEDATE is not null"/>
<CheckConstraint Name="CK_PROPERTYDETAIL_SALEPOSTDATE_REQUIRED" CheckExpression="(SALEDATE is null) or ((SALEPOSTDATE is not null) or (SALEPOSTSTATUSCODE = 2))" />
<CheckConstraint Name="CK_PROPERTYDETAIL_SOLDORUNSOLD" CheckExpression="(SALEDATE is null and SALEAMOUNT = 0 and BROKERFEE = 0 and SALEPOSTDATE is null and SALEPOSTSTATUSCODE = 1) or (not SALEDATE is null)"/>    
*/
end