sqlserver触发器实现INSERT update实例

CREATE or REPLACE TRIGGER trigger_name
AFTER INSERT
   ON table_name
   [ FOR EACH ROW ]

DECLARE
   -- variable declarations

BEGIN
   -- trigger code

EXCEPTION
   WHEN ...
   -- exception handling

END;

CREATE TABLE orders
( order_id number(5),
  quantity number(4),
  cost_per_item number(6,2),
  total_cost number(8,2)
);

CREATE OR REPLACE TRIGGER orders_after_insert
AFTER INSERT
   ON orders
   FOR EACH ROW
  
DECLARE
   v_username varchar2(10);
  
BEGIN
  
   -- Find username of person performing the INSERT into the table
   SELECT user INTO v_username
   FROM dual;
  
   -- Insert record into audit table
   INSERT INTO orders_audit
   ( order_id,
     quantity,
     cost_per_item,
     total_cost,
     username )
   VALUES
   ( :new.order_id,
     :new.quantity,
     :new.cost_per_item,
     :new.total_cost,
     v_username );
    
END;

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