AFTER insert,update

前几天搞了个触发器,今天检查一下,又学习了点东西

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