Showing posts with label after insert. Show all posts
Showing posts with label after insert. Show all posts

Friday, 19 July 2013

Create Trigger after insert into table in SQL Server 2008

/****** Example of trigger on table after insert ******/

CEATE TRIGGER [dbo].[MarginVal]
   ON   [dbo].[Trade_file]
   AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON ADDED TO PREVENT EXTRA RESULT SETS FROM
-- INTERFERING WITH SELECT STATEMENTS.
SET NOCOUNT ON;
DECLARE @SYSMBOL VARCHAR(200)
DECLARE @EXPIRY VARCHAR(200)
DECLARE @TRADENO VARCHAR(200)
DECLARE @BUYSELL VARCHAR(10)
DECLARE @PRICE DECIMAL(18,2)
DECLARE @LOT VARCHAR(18)
DECLARE @USERID VARCHAR(200)
declare @Trade_Number varchar(200)
------------------------------------------LAST INSETED DATA
SELECT @Trade_Number=Trade_Number,@USERID=USER_ID,@SYSMBOL=CONTRACT_DESCRIPTION,@EXPIRY=EXPIRY_DATE,@TRADENO=TRADE_NUMBER,@BUYSELL=BUY_SELL_IND,@PRICE=PRICE,@LOT=TRADE_QTY FROM INSERTED
-------------------------------------------------------------
------------------------------------ MARGIN FILE  FIND DAILY MARGIN
DECLARE @TMARGIN VARCHAR(200)
SELECT @TMARGIN=TOTALMARGIN FROM MARGIN_FILE WHERE SYMBOL=@SYSMBOL AND CONVERT(DATE,EXPIRY,107)=CONVERT(DATE,@EXPIRY,107)
------------------------------------------------------------------------
------------------------------------FIND LOT SIZE ACCORING TO SYSBMOL AND EXP
DECLARE @PRICEQTN VARCHAR(50)
DECLARE @LOTSIZE NUMERIC(18,2)
SELECT @PRICEQTN=PRICEQTN, @LOTSIZE=LOTSIZE FROM COMMLOTSIZE WHERE COMMODITY=@SYSMBOL

-------------------------------------------------------------------------------SELECT DATA AFTER THEN INSERT IN TO MARGIN TABEL
INSERT INTO CAL_MARGIN (USER_ID, Trade_Number,COMMODITY, EXPIRY, LTP, PRICEQTN, LOTSIZE, MARGIN, LOTVALUE, APPXMARGIN)
SELECT @USERID AS USERID,@Trade_Number,@SYSMBOL AS SYMBOL,@EXPIRY AS EXPIRY,@PRICE AS LTP,@PRICEQTN AS PRICEQTN,@LOTSIZE AS LOTSIZE,@TMARGIN AS MARGIN,
CONVERT(NUMERIC(18,2),@PRICE*@LOTSIZE) AS  LOTVALUE, CONVERT(NUMERIC(18,2),@PRICE*@LOTSIZE*@TMARGIN*CONVERT(INT,RTRIM(@LOT))/100) AS APPX_MARGIN

--SELECT * FROM TRADE_FILE
   
END