现在有一张表Table1,其中包含一个INT型字段ColInt和外键字段ColFK。现在需要编写一个触发器,当子表执行INSERT操作后,在子表中查找所有外键字段的值等于新增记录的外键字段的值的记录,将这些记录中ColInt字段中值为最小的那条记录更新到另外一张表Table2中。我自己写的UPDATE语句为:
UPDATE TABLE2 SET (Col1,Col2) =
(SELECT Col1,Col2 FROM
(SELECT ColFK,Col1,Col2,
ROW_NUMBER() OVER (PARTITION BY ColFK ORDER BY ColInt) RN
FROM Table1
WHERE ColFK = 33709) TB
WHERE RN = 1)
WHERE ColFK = 33709
这个UPDATE语句在执行的时候能获得所希望的结果。现在我计划把这个UPDATE语句写成一个触发器,如下:CREATE OR REPLACE TRIGGER TIG1 AFTER UPDATE ON TABLE1 FOR EACH ROW
BEGIN
UPDATE TABLE2 SET (Col1,Col2) =
(SELECT Col1,Col2 FROM
(SELECT ColFK,COL1,Col2,
ROW_NUMBER() OVER (PARTITION BY ColFK ORDER BY ColInt) RN
FROM Table1
WHERE ColFK = :NEW.ColFK) TB
WHERE RN = 1)
WHERE ColFK = :NEW.ColFK
END;
/这个触发器中存在语法错误。小弟第一次尝试写触发器,完全找不到门道,因此想请教各位高手问题出在哪里,还请各位不吝赐教,小弟先行谢过
UPDATE TABLE2 SET (Col1,Col2) =
(SELECT Col1,Col2 FROM
(SELECT ColFK,Col1,Col2,
ROW_NUMBER() OVER (PARTITION BY ColFK ORDER BY ColInt) RN
FROM Table1
WHERE ColFK = 33709) TB
WHERE RN = 1)
WHERE ColFK = 33709
这个UPDATE语句在执行的时候能获得所希望的结果。现在我计划把这个UPDATE语句写成一个触发器,如下:CREATE OR REPLACE TRIGGER TIG1 AFTER UPDATE ON TABLE1 FOR EACH ROW
BEGIN
UPDATE TABLE2 SET (Col1,Col2) =
(SELECT Col1,Col2 FROM
(SELECT ColFK,COL1,Col2,
ROW_NUMBER() OVER (PARTITION BY ColFK ORDER BY ColInt) RN
FROM Table1
WHERE ColFK = :NEW.ColFK) TB
WHERE RN = 1)
WHERE ColFK = :NEW.ColFK
END;
/这个触发器中存在语法错误。小弟第一次尝试写触发器,完全找不到门道,因此想请教各位高手问题出在哪里,还请各位不吝赐教,小弟先行谢过
CREATE OR REPLACE TRIGGER TIG1
AFTER insert ON TABLE1
FOR EACH ROW
BEGIN
UPDATE TABLE2 SET (Col1,Col2) =
(SELECT Col1,Col2 FROM
(SELECT ColFK,COL1,Col2,
ROW_NUMBER() OVER (PARTITION BY ColFK ORDER BY ColInt) RN
FROM Table1
WHERE ColFK = :NEW.ColFK) TB
WHERE RN = 1)
WHERE ColFK = :NEW.ColFK
END;
BEGIN
UPDATE TABLE2 SET (Col1,Col2) =
(SELECT Col1,Col2 FROM
(SELECT ColFK,COL1,Col2,
ROW_NUMBER() OVER (PARTITION BY ColFK ORDER BY ColInt) RN
FROM Table1
WHERE ColFK = :NEW.ColFK) TB
WHERE RN = 1)
WHERE ColFK = :NEW.ColFK;
END;
/
CREATE TABLE ZY2_S_SPECIAL_CERT
(
SPECIAL_CERT_ID INTEGER NOT NULL,
VLD_CONTACT_ID INTEGER NOT NULL,
CERTIFICATION_TYPE VARCHAR2(90 BYTE),
CERTIFICATION_NUM VARCHAR2(90 BYTE),
AUDIT_PERIOD VARCHAR2(90 BYTE),
TAKEN_DATE DATE,
AWARD_UNIT VARCHAR2(180 BYTE),
REMARK VARCHAR2(270 BYTE),
SYS_UPDT_USR VARCHAR2(90 BYTE),
SYS_UPDT_TIME DATE,
CERTIFICATION_PROJ VARCHAR2(270 BYTE),
LAST_DATE DATE
)要求在增和改操作执行后,查找新增的VLD_CONTACT_ID所对应的所有记录中最小的CERTIFICATION_TYPE所对应的记录,从中提取CERTIFICATION_TYPE,CERTIFICATION_NUM ,AUDIT_PERIOD,TAKEN_DATE和AWARD_UNIT字段更新到VLD_CONTACT表中的对应字段去。我的触发器是这样写的:CREATE OR REPLACE TRIGGER PWSYS.TIAR_VLD_CONTACT AFTER UPDATE ON ZY2_S_SPECIAL_CERT FOR EACH ROW
BEGIN
UPDATE VLD_CONTACT SET (CERTIFICATION_PROJ,CERTIFICATION_NUM,AUDIT_PERIOD,TAKEN_DATE,AWARD_UNIT,CERTIFICATION_TYPE) =
(SELECT ZC1.CERTIFICATION_PROJ,ZC1.CERTIFICATION_NUM,ZC1.AUDIT_PERIOD,ZC1.TAKEN_DATE,AWARD_UNIT,ZC1.CERTIFICATION_TYPE
FROM ZY2_S_SPECIAL_CERT ZC1,
(SELECT VLD_CONTACT_ID,MIN(CERTIFICATION_TYPE) CERTIFICATION_TYPE FROM ZY2_S_SPECIAL_CERT GROUP BY VLD_CONTACT_ID) ZC2
WHERE ZC1.VLD_CONTACT_ID = ZC2.VLD_CONTACT_ID
AND ZC1.CERTIFICATION_TYPE = ZC2.CERTIFICATION_TYPE
AND ZC1.VLD_CONTACT_ID = :NEW.VLD_CONTACT_ID)
WHERE VLD_CONTACT_ID = :NEW.VLD_CONTACT_ID
END;
/错误提示如下:
9/32 PL/SQL: ORA-00933: SQL 命令未正确结束
2/4 PL/SQL: SQL Statement ignored
10/4 PLS-00103: 出现符号 "end-of-file"在需要下列之一时:
begin case declare
end exception exit for goto if loop mod null pragma raise
return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge
<a single-quoted SQL string> pipe谢谢各位了