mysql触发器 转 sqlsever触发器

怎么把这段mysql触发器 转成 sqlsever触发器的

 DROP TRIGGER IF EXISTS t_beforeinsert_on_st_pptn_r;
CREATE TRIGGER t_beforeinsert_on_st_pptn_r BEFORE INSERT
ON st_pptn_r
FOR EACH ROW
BEGIN
 CASE 
 WHEN 0<=new.DRP AND new.DRP<30 
 THEN SET new.STATUS="正常";
 WHEN 30<=new.DRP AND new.DRP<40
 THEN SET new.STATUS="报警";
 INSERT INTO st_alert_b(STCD,TM,VAL,LEVEL,STATUS) VALUES(new.STCD,new.TM,new.DRP,"II","报警");
 WHEN 40<=new.DRP AND new.DRP<=100
 THEN SET new.STATUS="报警";
 INSERT INTO st_alert_b(STCD,TM,VAL,LEVEL,STATUS) VALUES(new.STCD,new.TM,new.DRP,"I","报警");
 ELSE SET new.STATUS="异常";
 INSERT INTO st_alert_b(STCD,TM,VAL,STATUS) VALUES(new.STCD,new.TM,new.DRP,"异常");
 DELETE FROM st_pptn_r WHERE st_pptn_r.STATUS="异常";
END CASE;
END

1个回答

java交流群 431591216

 IF OBJECT_ID('t_beforeinsert_on_st_pptn_r','tr') IS NOT NULL
    DROP TRIGGER t_beforeinsert_on_st_pptn_r;
GO
CREATE TRIGGER t_beforeinsert_on_st_pptn_r ON st_pptn_r
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO st_alert_b(STCD,TM,VAL,[LEVEL],[INFO],[STATUS],[VTYPE])
SELECT  STCD ,
        TM ,
        DRP ,
        [LEVEL]= CASE WHEN DRP >= 30
                             AND DRP < 50 THEN 'II'
                        WHEN DRP >= 50
                             THEN 'I'
                        WHEN DRP < 0
                             THEN '异常'
                        END ,
        [INFO]= CASE WHEN DRP >= 30
                             AND DRP < 50 THEN '降雨量大于30mm小于50mm'
                        WHEN DRP >= 50
                             THEN '降雨量小于大于等于50mm'
                        WHEN DRP < 0
                             THEN '数据小于0'
                        END ,

        [STATUS] = CASE WHEN DRP < 0 OR DRP >= 30
                              THEN '报警'
                        END ,
        '降雨量'


FROM    inserted
WHERE DRP<0 OR DRP >=30

 INSERT INTO st_pptn_r(STCD,TM,DRP,[STATUS])
 SELECT  STCD ,
         TM ,
         DRP ,
         [STATUS] = CASE 
                         WHEN DRP >= 0
                              AND DRP < 30 
                              THEN '正常'
                         WHEN DRP >= 30
                              THEN '报警'
                         WHEN DRP <0 
                              THEN '异常'
                         END 

 FROM    inserted


END

DROP TRIGGER IF EXISTS t_beforeinsert_on_st_pptn_r;
CREATE TRIGGER t_beforeinsert_on_st_pptn_r BEFORE INSERT
ON st_pptn_r
FOR EACH ROW
BEGIN
CASE
WHEN 0<=new.DRP AND new.DRP<30
THEN SET new.STATUS="正常";
WHEN 30<=new.DRP AND new.DRP<40
THEN SET new.STATUS="报警";
INSERT INTO st_alert_b(STCD,TM,VAL,LEVEL,STATUS) VALUES(new.STCD,new.TM,new.DRP,"II","报警");
WHEN 40<=new.DRP AND new.DRP<=100
THEN SET new.STATUS="报警";
INSERT INTO st_alert_b(STCD,TM,VAL,LEVEL,STATUS) VALUES(new.STCD,new.TM,new.DRP,"I","报警");
ELSE SET new.STATUS="异常";
INSERT INTO st_alert_b(STCD,TM,VAL,STATUS) VALUES(new.STCD,new.TM,new.DRP,"异常");
DELETE FROM st_pptn_r WHERE st_pptn_r.STATUS="异常";
END CASE;
END

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问