Set ANSI_NULLS ON Set QUOTED_IDENTIFIER ON GO ALTER trigger [dbo]. [toplist] on [dbo]. [TB_TRADE_0170] AFTER insert, update As Begin D eclare @ Ydlx varchar (50 ), @ Company [varchar] (200 ), @ Code [varchar] (6 ), @ Stock_name [varchar] (20 ), @ Date [varchar] (10 ), @ Daily [float], -@ Price [float], @ Inje [float], @ Outje [float], @ Je [float], @ Gpje [float], @ Injeb [float], @ Outjeb [float], -@ Gpjeb [float], @ StartDate varchar (10) If EXISTS (select * from inserted) Select @ ydlx = f002v_0170 , @ Company = f004v_0170 , @ Code = ob_seccode_0170 , @ Stock_name = ob_secname_0170 , @ Date = convert (varchar (10), ob_tradedate_0170, 120) , @ Inje = f005n_0170/10000 , @ Outje = f006n_0170/10000 , @ Je = @ inje + @ outje from inserted where ob_isvalid_0170 = 1 Else if exists (select * from new) Select @ ydlx = f002v_0170 , @ Company = f004v_0170 , @ Code = ob_seccode_0170 , @ Stock_name = ob_secname_0170 , @ Date = convert (varchar (10), ob_tradedate_0170, 120) , @ Inje = f005n_0170/10000 , @ Outje = f006n_0170/10000 , @ Je = @ inje + @ outje from new where ob_isvalid_0170 = 1 Select @ daily = f015n_0160, @ gpje = f016n_0160/10000, @ Injeb = case when f016n_0160 is not null and f016n_0160> 0.001 then @ Inje/f016n_0160 * 100 Else 0.0 end, @ Outjeb = case when f016n_0160 is not null and f016n_0160> 0.001 then @ Outje/f016n_0160 * 100 Else 0.0 end From tb_trade_0160 where tb_trade_0160.ob_seccode_0160 = @ code And convert (varchar (10), ob_tradedate_0160, 120) = @ date and ob_isvalid_0160 = 1 Insert into [tccj_toplist] ([ydlx], [company], [code], [stock_name] , [Date], [daily], [inje], [outje], [gpje], [injeb], [outjeb] ) Values ( @ Ydlx, @ company, @ code, @ stock_name, @ date, @ daily , @ Inje, @ outje, @ gpje, @ injeb, @ outjeb) End |