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

No comments:

Post a Comment