MySQL Forums
Forum List  »  Triggers

how to use declare in trigger
Posted by: SANDEEP ELEMANE
Date: November 19, 2015 03:28AM

I'm trying to execute a trigger its getting a syntax error

CREATE TRIGGER ATTENDANCE_NOTIFY AFTER INSERT 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;


ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'DECLA
RE
V_STUDENT_ID STUDENT.STUDENT_ID%TYPE := NULL;
V_HOD_ID ' at line 3

Options: ReplyQuote


Subject
Views
Written By
Posted
how to use declare in trigger
3822
November 19, 2015 03:28AM
1040
December 17, 2015 12:50PM
1011
December 17, 2015 03:41PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.