1 files changed, 32 insertions, 3 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql
index e3bd7640..161c734a 100644
--- a/sql/dawn.sql
+++ b/sql/dawn.sql
@@ -133,6 +133,8 @@ CREATE TABLE cds(id serial primary key,
        upfront float NOT NULL,
        upfront_settle_date date NOT NULL,
        swap_type swap_type NOT NULL,
+       orig_attach smallint,
+       orig_detach smallint,
        attach float,
        detach float,
        clearing_facility varchar(12) DEFAULT NULL,
@@ -145,9 +147,11 @@ CREATE TABLE cds(id serial primary key,
        corr_attach float DEFAULT NULL,
        corr_detach float DEFAULT NULL,
        account_code varchar(5) NOT NULL,
-       CONSTRAINT tranche_check CHECK (swap_type != 'CD_INDEX_TRANCHE' OR
-                                                (attach IS NOT NULL AND detach IS NOT NULL AND
-                                                 clearing_facility IS NULL))
+       CONSTRAINT tranche_check CHECK ((
+       swap_type IN ('CD_INDEX_TRANCHE',  'BESPOKE') AND
+       (orig_attach IS NOT NULL AND orig_detach IS NOT NULL AND clearing_facility IS NULL))
+       OR (swap_type='CD_INDEX' AND orig_attach IS NULL AND orig_detach IS NULL AND clearing_facility='ICE-CREDIT')
+       OR (swap_type='ABS_CDS' AND orig_attach IS NULL AND orig_detach IS NULL AND clearing_faciliy IS NULL))
        );
 
 ALTER TABLE cds OWNER TO dawn_user;
@@ -290,11 +294,35 @@ identifier = COALESCE(identifier, cusip, isin)', NEW.id);
     END;
     $$ language plpgsql;
 
+CREATE OR REPLACE function update_attach()
+RETURNS TRIGGER AS $$
+DECLARE
+  factor float;
+  cum_loss float;
+BEGIN
+IF NEW.orig_attach IS NULL AND NEW.orig_detach IS NULL THEN
+RETURN NEW;
+ELSE
+SELECT indexfactor, cumulativeloss INTO factor, cum_loss
+  FROM index_version WHERE redindexcode=NEW.security_id;
+UPDATE cds
+       SET attach=100*LEAST(GREATEST((NEW.orig_attach - cum_loss)/factor, 0), 1),
+           detach=100*LEAST(GREATEST((NEW.orig_detach - cum_loss)/factor, 0), 1)
+WHERE id=NEW.id;
+RETURN NEW;
+END IF;
+END
+$$ LANGUAGE plpgsql;
+
+
 create
     trigger dealid after insert
         on
         capfloors for each row execute procedure auto_dealid() ;
 
+CREATE TRIGGER cds_attach AFTER INSERT OR UPDATE OF orig_attach, orig_detach ON cds
+FOR EACH ROW EXECUTE PROCEDURE update_attach();
+
 CREATE TRIGGER swaptions_dealid AFTER INSERT ON swaptions
 FOR EACH ROW EXECUTE PROCEDURE auto_dealid();
 
@@ -903,6 +931,7 @@ RETURN (newdetach-newattach)/(detach-attach)*index_factor;
 END;
 $$ LANGUAGE plpgsql;
 
+
 CREATE OR REPLACE function fx_rate(p_date date) RETURNS TABLE(currency currency, fxrate float) AS $$
 BEGIN
   RETURN QUERY SELECT unnest(Array['USD', 'EUR', 'CAD'])::currency,