谁帮我把这个mysql触发器转成sqlserver的

无道loading 2017-02-15 03:50:43

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

...全文

106 2 打赏收藏转发到动态举报

写回复

用AI写文章

2 条回复

切换为时间正序

请发表友善的回复…

发表回复

无道loading 2017-02-16

打赏
举报

引用 1 楼 roy_88 的回复:

新增前判断参照以下改，按自己的表结构调整本栏位 e.g.

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],[STATUS])
SELECT  STCD ,
        TM ,
        DRP ,
        [LEVEL]= CASE WHEN DRP >= 0
                             AND DRP < 30 THEN 'II'
                        WHEN DRP >= 40
                             AND DRP <= 100 THEN 'I'
						ELSE 
							'DRP'
						END ,
                   
        [STATUS] = CASE WHEN DRP >= 0
                             AND DRP < 30 THEN '正常'
                        WHEN DRP >= 30
                             AND DRP <= 100 THEN '报警'
						ELSE 
							'异常'
						END 
                   
FROM    inserted

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
                             AND DRP <= 100 THEN '报警'
						END 
                   
FROM    inserted
WHERE DRP BETWEEN 0 AND 100

END

可以了，谢了

中国风 2017-02-15

打赏
举报

新增前判断参照以下改，按自己的表结构调整本栏位 e.g.

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],[STATUS])
SELECT  STCD ,
        TM ,
        DRP ,
        [LEVEL]= CASE WHEN DRP >= 0
                             AND DRP < 30 THEN 'II'
                        WHEN DRP >= 40
                             AND DRP <= 100 THEN 'I'
						ELSE 
							'DRP'
						END ,
                   
        [STATUS] = CASE WHEN DRP >= 0
                             AND DRP < 30 THEN '正常'
                        WHEN DRP >= 30
                             AND DRP <= 100 THEN '报警'
						ELSE 
							'异常'
						END 
                   
FROM    inserted

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
                             AND DRP <= 100 THEN '报警'
						END 
                   
FROM    inserted
WHERE DRP BETWEEN 0 AND 100

END