안녕하세요...
답변주셔서 감사합니다. 이해가 잘 갑니다. 그런데 치영님께서 말씀하신 것을 기간으로 제 상황에 맞게 TRIGGER하고 FUNCTION을 만들었는데 ERROR가 생기고 있습니다.
제가 reservation table을 update 하면 function을 불러 실행을 시키는데
다음과 같은 error가 나오는 데요,
"parse: parse error near at 'radd1'""
무슨 문제지 도통....모르겠습니다.
밑에는 제가 작성한 trigger과 function이고 시간이 나시면 한번 봐주셨으면 감사하겠습니다. 죄송합니다. DB를 다루는 것은 이번이 처음이라서요...
행복한 하루되십시요, 그럼 이만.
====================================================================
CREATE TRIGGER create_placement_record
BEFORE INSERT OR UPDATE
ON reservation
FOR Each ROW
EXECUTE PROCEDURE fnc_create_placementRecord();
CREATE OR REPLACE FUNCTION fnc_create_placementRecord() RETURNS OPAQUEAS '
DECLARE
registration_id text; -- X_rrecnum
R_Address_1 text;
R_Address_2 text;
R_Age text;
R_City text;
R_district text;
R_Email text;
R_ESL text;
R_Ethnicity text;
R_Free_Lunch text;
R_FullName text;
R_Gender text;
R_location text;
R_Member_Type text;
R_PF_ID text;
R_Phone text;
R_Regis_Create_Date DATE;
R_Regis_Edit_Date DATE;
R_School text;
R_Special_Needs text;
R_SSchCode text;
R_State text;
R_Talented_Gifted text;
R_URL text;
R_ZipCode text;
user_code text;
BEGIN
reserv_status := new.resrvation_status;
IF (reserv_status = ''Congratulations!'') OR (reserv_status = ''Place
Applicant'') OR (reserv_status = ''Registration Complete'') THEN
registration_id := new.xrrecnum;
SELECT INTO user_code rusercode, R_Address_1 RAdd1, R_Address_2 RAdd2, R_Age RAge, R_City RCity, R_district RExtra2,
R_Email REmail, R_ESL RESL, R_Ethnicity REthnicity, R_Free_Lunch RFreeLunch, R_FullName RFullName, R_Gender RGender, R_location RExtra3, R_Member_Type RMembType, R_PF_ID RYPathID, R_Phone RPhHome, R_Regis_Create_Date RCreation, R_Regis_Edit_Date
REdit, R_School RSchool, R_Special_Needs RSpecialNeeds, R_State
RState, R_Talented_Gifted RTalentedG, R_URL RURL, R_ZipCode RZip FROM registration WHERE rrecnum = registration_id; INSERT INTO web_evaluation (X_related_usercode, R_Address_1, R_Address_2, R_Age, R_City, R_district, R_Email, R_ESL,
R_Ethnicity, R_Free_Lunch, R_FullName, R_Gender, R_location, R_Member_Type, R_PF_ID, R_Phone, R_Regis_Create_Date, R_Regis_Edit_Date, R_School, R_Special_Needs, R_State, R_Talented_Gifted, R_URL RURL, R_ZipCode, R_SSchCode) VALUES (user_code, R_Address_1, R_Address_2, R_Age, R_City, R_district, R_Email, R_ESL, R_Ethnicity, R_Free_Lunch, R_FullName, R_Gender, R_location, R_Member_Type, R_PF_ID, R_Phone, R_Regis_Create_Date, R_Regis_Edit_Date,
R_School, R_Special_Needs, R_State, R_Talented_Gifted, R_URL RURL,
R_ZipCode, R_SSchCode);
END IF;
END;LANGUAGE 'plpgsql';
|