当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - trigger help on db2

问题描述:

Hi guys I have written a trigger that I hope will generate a message saying

"SAT ON TRAILER IS DEAD, PROCEED?"

after a user updates the table trip.

In order to qualify as a dead satellite we use a table called trailer that shows the last sat date. If the sat hasn't pinged in for over 8 days then it is considered dead.

active in disp means the trailer is still a company asset.

Here is where i'm at, can you guys provide me feedback please? and maybe some recommendations? Thanks in advance!

CREATE TRIGGER MISSING_TRAILERS

AFTER UPDATE OF TRAILER ON TRIP

REFERENCING NEW AS N OLD AS O

FOR EACH ROW

BEGIN ATOMIC

DECLARE DEAD_SAT VARCHAR(10);

DECLARE MSG VARCHAR(70);

SET DEAD_SAT = COALESCE((SELECT TRAILER_ID FROM TRAILER WHERE LAST_SAT_DATE < CURRENT TIMESTAMP - 8 DAYS AND ACTIVE_IN_DISP = 'True'),'OK')

IF DEAD_SAT <> 'OK' THEN

SET MSG = 'SAT ON TRAILER IS DEAD, PROCEED?'

END IF;

END

网友答案:
CREATE TRIGGER DEAD_TRAILERS_NOTIFY
AFTER UPDATE OF TRAILER ON TRIP 
REFERENCING NEW AS N OLD AS O 
FOR EACH ROW
BEGIN ATOMIC

DECLARE DEAD_SAT VARCHAR(10); 
DECLARE MSG VARCHAR(70); 

SET DEAD_SAT = COALESCE((SELECT TRAILER_ID FROM TRAILER WHERE LAST_SAT_DATE < CURRENT TIMESTAMP - 8 DAYS AND N.TRAILER = TRAILER_ID),'OK');



    IF DEAD_SAT <> 'OK' AND USER NOT IN ('VISTAR','TM4WIN') THEN
    SET MSG = 'SAT ON TRAILER IS DEAD. PLEASE GET IT REPLACED'; 
    SIGNAL SQLSTATE '75010' SET MESSAGE_TEXT = MSG;
    END IF;
END
分享给朋友:
您可能感兴趣的文章:
随机阅读: