Database changes 1.9.11 SqlServer

--

-- SQL SERVER

--

--Derivative access type in users table

ALTER TABLE [Kodiak].[dbo].[USERS] ADD DERIVATIVES_ACCESS_TYPE INT NULL;

-- Added Properties to accomadate Fund Orders
ALTER TABLE PREALLOCATIONS ADD TRADE_DATE DATETIME NULL;
ALTER TABLE PREALLOCATIONS ADD SETTLEMENT_DATE DATETIME NULL;
ALTER TABLE PREALLOCATIONS ADD TAX_AMOUNT DOUBLE PRECISION NULL;

-- Add CashQuantity columns
ALTER TABLE ALLOCATIONS ADD CASH_QUANTITY DOUBLE PRECISION null;
ALTER TABLE ALLOCATION_EVENTS ADD CASH_QUANTITY DOUBLE PRECISION null;
ALTER TABLE EXECUTIONS ADD ORDER_CASH_QUANTITY DOUBLE PRECISION null;
ALTER TABLE ORDERS ADD CASH_QUANTITY DOUBLE PRECISION null;
ALTER TABLE ORDER_EVENTS ADD CASH_QUANTITY DOUBLE PRECISION null;
ALTER TABLE INSTRUCTIONS ADD CASH_QUANTITY DOUBLE PRECISION null;
ALTER TABLE PREALLOCATIONS ADD CASH_QUANTITY DOUBLE PRECISION null;

--Update instruments table with Fund Specific columns
ALTER TABLE INSTRUMENTS ADD CUT_OFF_TIME VARCHAR(20) NULL;
ALTER TABLE INSTRUMENTS ADD NUMBER_OF_SETTLEMENT_DAYS_SELL DOUBLE PRECISION null;
ALTER TABLE INSTRUMENTS ADD COMMISSION_BUY DOUBLE PRECISION null;
ALTER TABLE INSTRUMENTS ADD COMMISSION_SELL DOUBLE PRECISION null;

--Update Allocations table with Fund Specific columns
ALTER TABLE ALLOCATIONS ADD EXTERNAL_ACCOUNT_NUMBER VARCHAR(100) NULL;
ALTER TABLE ALLOCATIONS ADD EXTERNAL_ACCOUNT_SSN VARCHAR(20) NULL;

--Update Allocations table with Fund Specific columns
ALTER TABLE PREALLOCATIONS ADD EXTERNAL_ACCOUNT_NUMBER VARCHAR(100) NULL;
ALTER TABLE PREALLOCATIONS ADD EXTERNAL_ACCOUNT_SSN VARCHAR(20) NULL;

--Added Timestamp to Account and Portfolio tables
-- Run this first
ALTER TABLE PORTFOLIOS ADD LAST_UPDATED DATETIME NULL DEFAULT GETDATE();
ALTER TABLE ACCOUNTS ADD LAST_UPDATED DATETIME NULL DEFAULT GETDATE();
ALTER TABLE ACCOUNT_PORTFOLIO ADD LAST_UPDATED DATETIME NULL DEFAULT GETDATE();

-- Then this
CREATE TRIGGER dbo.SetLastUpdatedPortfolios
ON dbo.Portfolios
AFTER UPDATE
AS
BEGIN
IF NOT UPDATE(LAST_UPDATED)
BEGIN
UPDATE t
SET t.LAST_UPDATED = CURRENT_TIMESTAMP
FROM dbo.Portfolios AS t -- not b!
INNER JOIN inserted AS i
ON t.ID_PORTFOLIOS = i.ID_PORTFOLIOS;
END
END
GO

CREATE TRIGGER dbo.SetLastUpdatedAccounts
ON dbo.Accounts
AFTER UPDATE
AS
BEGIN
IF NOT UPDATE(LAST_UPDATED)
BEGIN
UPDATE t
SET t.LAST_UPDATED = CURRENT_TIMESTAMP
FROM dbo.Accounts AS t
INNER JOIN inserted AS i
ON t.ID_ACCOUNTS = i.ID_ACCOUNTS;
END
END
GO

CREATE TRIGGER dbo.SetLastUpdatedAccount_Portfolio
ON dbo.Account_Portfolio
AFTER UPDATE
AS
BEGIN
IF NOT UPDATE(LAST_UPDATED)
BEGIN
UPDATE t
SET t.LAST_UPDATED = CURRENT_TIMESTAMP
FROM dbo.Account_Portfolio AS t
INNER JOIN inserted AS i
ON (t.ACCOUNT_ID = i.ACCOUNT_ID AND t.PORTFOLIO_ID = i.PORTFOLIO_ID);
END
END
GO

-- Add tax amount to Allocation table
ALTER TABLE ALLOCATIONS ADD TAX_AMOUNT DOUBLE PRECISION null;

-- Added discount percent to instructions table
ALTER TABLE INSTRUCTIONS ADD DISCOUNT_PERCENT DOUBLE PRECISION null;

-- Add an instrument to the allocations table.
ALTER TABLE ALLOCATIONS ADD INSTRUMENT_ID INT null;
 
ALTER TABLE ALLOCATIONS 
	add constraint ALLOC_INSTRUMENT_FK 
	foreign key (INSTRUMENT_ID) 
	references INSTRUMENTS