SQL Server triggers INSERT update instances

Syntax

The code is as follows:	Copy code
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;

View instances

For Example

Create a data table with the following structure:

The code is as follows:	Copy code
Create table orders (Order_id number (5 ), Quantity number (4 ), Cost_per_item number (6, 2 ), Total_cost number (8, 2) );

Then, we can create an after insert trigger as follows:

The code is as follows:

Copy code

Create or replace trigger orders_after_insert AFTER INSERT ON orders FOR EACH ROW    DECLARE V_username varchar2 (10 );    BEGIN    -- Find username of person inserting Ming 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;

I don't know if you understand it. I wrote it myself.

The code is as follows:

Copy code

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