--help you to format the statement:CREATE OR REPLACE TRIGGER trigger_resident_batchid
AFTER INSERT ON bd_resident
FOR EACH ROW
BEGIN
DECLARE
maxbatchid NUMBER := 0;
SELECT MAX(to_number(substr(strdatabatchid, 8 - length(strdatabatchid)))) INTO maxbatchid FROM bd_resident WHERE substr(strdatabatchid, 0, 8) = to_char(SYSDATE, 'YYYYMMDD') AND substr(strdatabatchid, 8 - length(strdatabatchid)) IS NOT NULL AND id = :new.id; maxbatchid + +; UPDATE bd_resident SET strdatabatchid = to_char(SYSDATE,
'YYYYMMDD') + to_char(maxbatchid) WHERE id = :new.id; END trigger_resident_batchid;
AFTER INSERT ON bd_resident
FOR EACH ROW
BEGIN
DECLARE
maxbatchid NUMBER := 0;
SELECT MAX(to_number(substr(strdatabatchid, 8 - length(strdatabatchid)))) INTO maxbatchid FROM bd_resident WHERE substr(strdatabatchid, 0, 8) = to_char(SYSDATE, 'YYYYMMDD') AND substr(strdatabatchid, 8 - length(strdatabatchid)) IS NOT NULL AND id = :new.id; maxbatchid + +; UPDATE bd_resident SET strdatabatchid = to_char(SYSDATE,
'YYYYMMDD') + to_char(maxbatchid) WHERE id = :new.id; END trigger_resident_batchid;
AFTER INSERT ON bd_resident
FOR EACH ROW
AS
maxbatchid NUMBER := 0;BEGIN
-- DECLARE
-- maxbatchid NUMBER := 0;
SELECT MAX(to_number(substr(strdatabatchid, 8 - length(strdatabatchid)))) INTO maxbatchid FROM bd_resident WHERE substr(strdatabatchid, 0, 8) = to_char(SYSDATE, 'YYYYMMDD') AND substr(strdatabatchid, 8 - length(strdatabatchid)) IS NOT NULL AND id = :new.id; maxbatchid + +; UPDATE bd_resident SET strdatabatchid = to_char(SYSDATE,
'YYYYMMDD') + to_char(maxbatchid) WHERE id = :new.id; END trigger_resident_batchid;
beginfunctionpackage
pragmaproceduresubtypetypeuse<an identifier>
<a double-quoted delimited-identifier>cursorformcurrent
符号 "begin" 被替换为 "SELECT" 后继续。
Line: 9
Text: SELECT MAX(TO_NUMBER(SUBSTR(STRDATABATCHID,8-LENGTH(STRDATABATCHID)))) INTO MAXBATCHID FROM BD_RESIDENT WHERE SUBSTR(STRDATABATCHID,0,8)=TO_CHAR(SYSDATE,'YYYYMMDD') AND SUBSTR(STRDATABATCHID,8-LENGTH(STRDATABATCHID)) IS NOT NULL AND ID=:NEW.ID;Error: PLS-00103: 出现符号 "/"在需要下列之一时:
begindeclareendexceptionexit
forgotoifloopmodnullpragmaraisereturnselectupdatewhile
<an identifier><a double-quoted delimited-identifier>
<a bind variable><<closecurrentdeletefetchlockinsertopen
rollbacksavepointsetsqlexecutecommitforall
<a single-quoted SQL string>
Line: 13
Text: /
create table BD_RESIDENT
(
ID NUMBER not null,
STRPACKAGEID VARCHAR2(50),
STRDATABATCHID VARCHAR2(50) not null,
STRPROJNAME VARCHAR2(100),
STREREACODE VARCHAR2(30),
STRREMOVECOMPANY VARCHAR2(100),
INTADDRESSL NUMBER,
STRADDRESSN VARCHAR2(20),
STRADDRESSZN VARCHAR2(20),
STRADDRESSH VARCHAR2(20),
STRADDRESSR VARCHAR2(20),
INTLICENSECOUNT NUMBER,
INTRENTCOUNT NUMBER,
INTDOMICILECOUNT NUMBER,
STRRESIDENTMARK VARCHAR2(20),
DATRESIDENT DATE,
STRMANAGERMARK VARCHAR2(20),
DATMANAGER DATE,
STRAUDITMARK VARCHAR2(20),
DATAUDIT DATE,
STRREAUDITMARK VARCHAR2(20),
DATREAUDIT DATE,
STRREMARK VARCHAR2(1000)
)触发器如下
CREATE OR REPLACE TRIGGER TRIGGER_RESIDENT_BATCHID
AFTER INSERT
ON BD_RESIDENT
FOR EACH ROW
BEGIN
DECLARE
MAXBATCHID NUMBER:=0;
SELECT MAX(TO_NUMBER(SUBSTR(STRDATABATCHID,8-LENGTH(STRDATABATCHID)))) INTO MAXBATCHID FROM BD_RESIDENT WHERE SUBSTR(STRDATABATCHID,0,8)=TO_CHAR(SYSDATE,'YYYYMMDD') AND SUBSTR(STRDATABATCHID,8-LENGTH(STRDATABATCHID)) IS NOT NULL AND ID=:NEW.ID;
MAXBATCHID:=MAXBATCHID+1;
UPDATE BD_RESIDENT SET STRDATABATCHID=TO_CHAR(SYSDATE,'YYYYMMDD')+TO_CHAR(MAXBATCHID) WHERE ID=:NEW.ID;
END TRIGGER_RESIDENT_BATCHID;
/
需要将该触发器拆分为两个触发器来实现。一个是行级触发器(带有each row 关键字),
修改行的关键字保存到一个包变量中;在语句级的触发器中,获得修改行(包中的变量
值),进行相应的处理
虽然没报错,但是你的触发器还是有不足的地方,如果取最大值的时候取不到,那变量就是NULL了,直接加1会有问题,应该判断如果是NULL就=0以后再加1
AFTER INSERT
ON BD_RESIDENT
FOR EACH ROW
DECLARE MAXBATCHID number ;
BEGIN
SELECT max(to_number(substr(STRDATABATCHID,8-length(STRDATABATCHID))))
INTO MAXBATCHID
FROM BD_RESIDENT
WHERE SUBSTR(STRDATABATCHID,0,8)=TO_CHAR(SYSDATE,'YYYYMMDD')
AND SUBSTR(STRDATABATCHID,8-LENGTH(STRDATABATCHID)) IS NOT NULL AND ID=:NEW.ID;
MAXBATCHID:=MAXBATCHID+1;
UPDATE BD_RESIDENT
SET STRDATABATCHID=TO_CHAR(SYSDATE,'YYYYMMDD')+TO_CHAR(MAXBATCHID)
WHERE ID=:NEW.ID;
END;这样写就没问题了
AFTER INSERT
ON BD_RESIDENT
FOR EACH ROW
DECLARE MAXBATCHID number ;
BEGIN
MAXBATCHID :=0;
SELECT max(to_number(substr(STRDATABATCHID,8-length(STRDATABATCHID))))
INTO MAXBATCHID
FROM BD_RESIDENT
WHERE SUBSTR(STRDATABATCHID,0,8)=TO_CHAR(SYSDATE,'YYYYMMDD')
AND SUBSTR(STRDATABATCHID,8-LENGTH(STRDATABATCHID)) IS NOT NULL AND ID=:NEW.ID;
MAXBATCHID:=MAXBATCHID+1;
UPDATE BD_RESIDENT
SET STRDATABATCHID=TO_CHAR(SYSDATE,'YYYYMMDD')+TO_CHAR(MAXBATCHID)
WHERE ID=:NEW.ID;
END;
定义变量的时候不要直接赋值
又学到知识了