1 files changed, 21 insertions, 11 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql
index fecd9445..9ba4d0c9 100644
--- a/sql/dawn.sql
+++ b/sql/dawn.sql
@@ -150,7 +150,7 @@ RETURNS trigger
 AS $$
 BEGIN
 PERFORM pg_notify(TG_TABLE_NAME, NEW.id::text);
-RETURN NEW;
+RETURN NULL;
 END
 $$ LANGUAGE plpgsql;
 
@@ -158,25 +158,37 @@ $$ LANGUAGE plpgsql;
 CREATE TRIGGER bond_notify AFTER
 INSERT OR UPDATE OF
     identifier,
+    cusip,
+    isin,
 	faceamount,
     price,
+    trade_date,
 	settle_date
 ON
 	bonds
 FOR EACH ROW EXECUTE PROCEDURE notify_id();
 
-CREATE OR REPLACE FUNCTION update_identifier()
+CREATE OR REPLACE FUNCTION set_identifier()
 RETURNS trigger
 AS $$
 BEGIN
-EXECUTE 'UPDATE bonds SET identifier = COALESCE(identifier, cusip, isin) WHERE id=$1' USING NEW.id;
+NEW.identifier =  COALESCE(NEW.identifier, NEW.cusip, NEW.isin);
 RETURN NEW;
 END
 $$ LANGUAGE plpgsql;
 
-CREATE TABLE allocation(
+CREATE TRIGGER update_identifier BEFORE
+INSERT OR UPDATE OF
+    identifier,
+    cusip,
+    isin
+ON
+	bonds
+FOR EACH ROW EXECUTE PROCEDURE set_identifier();
+
+CREATE TABLE bond_allocation(
        id integer generated always as identity primary key,
-       tradeid integer not null references bonds,
+       tradeid integer not null references bonds ON DELETE CASCADE,
        notional float not null,
        code text not null references accounts,
        submitted boolean default False,
@@ -184,7 +196,7 @@ CREATE TABLE allocation(
 
 CREATE TYPE status AS ENUM('Pending', 'Processed', 'Submitted', 'Aknowledged');
 
-CREATE TABLE submission(
+CREATE TABLE bond_submission(
        id integer generated always as identity primary key,
        allocation_id integer not null references allocation on delete cascade,
        "action" action not null,
@@ -270,16 +282,14 @@ RETURN NEW;
 ELSE
 SELECT indexfactor, cumulativeloss INTO factor, cum_loss
   FROM index_version WHERE redindexcode=NEW.security_id;
-UPDATE cds
-       SET attach=factor*LEAST(GREATEST((NEW.orig_attach - cum_loss)/factor, 0), 1),
-           detach=factor*LEAST(GREATEST((NEW.orig_detach - cum_loss)/factor, 0), 1)
-WHERE id=NEW.id;
+NEW.attach = factor*LEAST(GREATEST((NEW.orig_attach - cum_loss)/factor, 0), 1);
+NEW.detach = factor*LEAST(GREATEST((NEW.orig_detach - cum_loss)/factor, 0), 1);
 RETURN NEW;
 END IF;
 END
 $$ LANGUAGE plpgsql;
 
-CREATE TRIGGER cds_attach AFTER
+CREATE TRIGGER cds_attach BEFORE
 INSERT OR UPDATE OF orig_attach, orig_detach, security_id
 ON cds
 FOR EACH ROW EXECUTE PROCEDURE update_attach();