mysql如何在触发器中声明变量? [英] how to declare variables in trigger with mysql?
本文介绍了mysql如何在触发器中声明变量?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
CREATE OR REPLACE TRIGGER ATTENDANCE_NOTIFY AFTER INSERT OR UPDATE ON ATTENDANCE
FOR EACH ROW
DECLARE
V_STUDENT_ID STUDENT.STUDENT_ID%TYPE := NULL;
V_HOD_ID HEAD_OF_DEPARTMENT.HOD_ID%TYPE := NULL;
V_SUBCODE STUDENT.SUBCODE%TYPE := NULL;
V_ATTENDANCE ATTENDENCE%TYPE := NULL;
BEGIN
SELECT SUB_CODE, SUB_NAME INTO V_SUB_CODE, FROM SUBJECT WHERE STUDENT_ID = :NEW.STUDENT_ID;
SELECT STUDENT_ID INTO V_STUDENT_ID FROM STUDENT WHERE SUBJECT_CODE = :NEW.SUBJECT_CODE;
SELECT HOD_ID INTO V_HOD_ID FROM STUDENT_HOD WHERE STUDENT_ID = :NEW.STUDENT_ID;
SELECT ATTENDENCE INTO V_ATTENDENCE FROM ATTENDENCE WHERE STUDENT_ID=:NEW_STUDENT_ID
IF (V_ATTENDENCE IS NOT NULL AND V_SUB_CODE IS NOT NULL AND V_STUDENT_ID IS NOT NULL AND V_HOD_ID IS NOT NULL) THEN
IF (:NEW.ATTENDENCE < (V_ATTENDENCE * 0.85)) THEN
INSERT INTO NOTIFY VALUES(V_HOD_ID, V_STUDENT_ID || ' (ID:- ' || :NEW.STUDENT_ID ||') HAS LESS THAN 85% ATTENDENCE IN SUBJECT ' || V_SUB_CODE);
END IF;
END IF;
EXCEPTION
WHEN OTHERS
THEN NULL;
END;
我在声明中遇到语法错误
i am getting a syntax error in declare
推荐答案
在MySQL中无法引用列的数据类型.DECLARE必须静态声明变量的类型和大小.
There is no way to refer datatype of column in MySQL. DECLARE must statically declare a variable's type and size.
类似
DECLARE myvar VARCHAR( 8 ) -- This is valid in Mysql
不是
DECLARE myvar mytable.myfield%TYPE --This is invalid in Mysql
希望这会有所帮助.
这篇关于mysql如何在触发器中声明变量?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文