SQL Server triggers implement an insert-time operation on another table

I used to have MySQL, and now I write SQL Server trigger, I feel the change is pretty big

1. Defining variables

#在mysql中变量直接这么定义就可以了SET @VALUE = "111", declare @count int in #在sql server, #并赋值set @count =0; #如果是查询, must be a select @count = Coun T (*) from wq_mninf_d_real where [email protected];

2. Judging

In MySQL, the IF-judging format
If condition then statement end if;

In SQL Server, the IF-judged format

if (conditional) BEGIN statement end;

Example

#mysqlIF @VALUE4 =1 then   INSERT into T_sca_history_data (meter_code,param_code,data_value,v_value,date_time) VALUES                        (NEW. Meter_code,new. Param_code,new. Data_value,new. V_value,new. Date_time); END if;sql Serverif (@count =0) begin   

3. New for the trigger

In MySQL, new.name can be used to get triggers to trigger the insertion of the value, and SQL Server is not the case, SQL Server is to put the punishment of the data in a temporary table, so it is the operation of this

#inserted代表插入数据的那张临时表, and deleted this temporary table used as the delete data select Stcd from inserted# if you just insert the inserted data into another table, the statement is insert into Wq_wwfinf_d_ REAL select prjcd,tm,infl,swwl,cwwl,otps,otf,qoec,pspps,nt from inserted; #如果还有加点别的数据, you can do this. Insert INTO Wq_mninf_d_ REAL Select Stcd,type,uppervalue,lowervalue,tm,nt, @smid, @stnm, @prjcd, @pipcd from inserted;

4. I make the trigger code, the role of the trigger is to insert the raw data table data into the real-time data table, if the real-time table does not have the data, insert, if any, delete and then insert

BEGIN declare @stcd varchar (30);  declare @count int;  declare @smid int;  DECLARE @stnm varchar (30);  DECLARE @prjcd varchar (30);  DECLARE @pipcd varchar (30);  Select @stcd = Stcd from inserted;  Select @count = count (*) from wq_mninf_d_real where [email protected];  Select @smid = Smid from wq_wqsinf_b where [email protected];  Select @stnm = stnm from wq_wqsinf_b where [email protected];  Select @prjcd = PRJCD from wq_wqsinf_b where [email protected];  Select @pipcd = pipcd from wq_wqsinf_b where [email protected]; if (@count =0) begin insert INTO Wq_mninf_d_real select Stcd,type,uppervalue,lowervalue,tm,nt, @smid, @stnm, @prjcd, @pi    PCD from inserted;      End ELSE begin delete Wq_mninf_d_real where [email protected];    Insert INTO Wq_mninf_d_real Select Stcd,type,uppervalue,lowervalue,tm,nt, @smid, @stnm, @prjcd, @pipcd from inserted; EndEnd

This blog post has a big effect on me.

http://blog.csdn.net/chenbin520/article/details/6026686

SQL Server triggers implement an insert-time operation on another table