Sqlserver trigger to operate another table during insert

I used to have mysql too. Now I am writing SQL Server triggers and I feel that the changes are quite large. define the variable # SET @ VALUE111; # declare @ countint in sqlserver; # assign a value to set @ count0; # if it is a query, must be so select @ countcount (*) fromWQ_MNI

I used to have mysql too. Now I am writing SQL Server triggers and I feel that the changes are quite large. define the variable # SET @ VALUE = 111 If the variable is defined in mysql; # declare @ count int in SQL server; # assign the VALUE set @ count = 0; # For queries, select @ count = count (*) from WQ_MNI is required.

I used to have mysql too. Now I am writing SQL Server triggers, and I feel that the changes are quite large.

1. Define Variables

# SET @ VALUE = "111"; # declare @ count int; # in SQL server, assign the VALUE set @ count = 0; # For queries, select @ count = count (*) from WQ_MNINF_D_REAL where STCD = @ stcd;

2. Judgment

In mysql, if determines the format
If condition then statement end if;

In SQL server, if determines the format

If (condition) 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   insert into WQ_MNINF_D_REAL select STCD,TYPE,UPPERVALUE,LOWERVALUE,TM,NT,@smid,@stnm,@prjcd,@pipcd from inserted;end 

3. new of the trigger

In mysql, use new. NAME can obtain the trigger-triggered insert value, but SQL server does not. SQL server places the penalized data in a temporary table, so its operation is as follows:

# Inserted indicates the temporary table for data insertion, and also the temporary table "select STCD from inserted" deleted "for data deletion. # If you only want to insert the inserted data into another table, the statement is as follows: insert into WQ_WWFINF_D_REAL select PRJCD, TM, INFL, SWWL, CWWL, OTPS, OTF, QOEC, PSPPS, nt from inserted; # if there is more data, insert into WQ_MNINF_D_REAL select STCD, TYPE, UPPERVALUE, LOWERVALUE, TM, NT, @ smid, @ stnm, @ prjcd, @ pipcd from inserted;

4. code of the trigger I created. The trigger is used to insert data from the original data table to the real-time data table. If the real-time table does not have the data, insert the data. If yes, delete the data and insert it again.

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 STCD=@stcd;  select @smid = SMID from WQ_WQSINF_B where STCD=@stcd;  select @stnm = STNM from WQ_WQSINF_B where STCD=@stcd;  select @prjcd = PRJCD from WQ_WQSINF_B where STCD=@stcd;  select @pipcd = PIPCD from WQ_WQSINF_B where STCD=@stcd;  if(@count=0)    begin      insert into WQ_MNINF_D_REAL select STCD,TYPE,UPPERVALUE,LOWERVALUE,TM,NT,@smid,@stnm,@prjcd,@pipcd from inserted;    end   else    begin      delete WQ_MNINF_D_REAL where STCD=@stcd;      insert into WQ_MNINF_D_REAL select STCD,TYPE,UPPERVALUE,LOWERVALUE,TM,NT,@smid,@stnm,@prjcd,@pipcd from inserted;    endEND

This blog post is very helpful to me and posted together

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