CREATE OR REPLACE TRIGGER TEST_ADD
BEFORE INSERT ON TEST.TEST_TRIGGER
FOR EACH ROW
declare
tname varchar(21);
i integer;
begin
i:=0;
tname:=:NEW.test_trigger.jh || :NEW.test_trigger.sjdwbh || i
UPDATE SET test_trigger.sj_id='+' || &tname& || '+';
i:=i+1;
end test_add;
BEFORE INSERT ON TEST.TEST_TRIGGER
FOR EACH ROW
declare
tname varchar(21);
i integer;
begin
i:=0;
tname:=:NEW.test_trigger.jh || :NEW.test_trigger.sjdwbh || i
UPDATE SET test_trigger.sj_id='+' || &tname& || '+';
i:=i+1;
end test_add;
INSERT ON "D_PROJECT" FOR EACH ROW DECLARE
PROID VARCHAR(16);
PROINT INTEGER;
BEGIN PROID := SUBSTR(:NEW.PROJECTID,1,16);
SELECT NVL(MAX(SUBSTR(PROJECTID,17,8)),0) + 1 INTO PROINT
FROM D_PROJECT WHERE SUBSTR(PROJECTID,1,16) = PROID;
:NEW.PROJECTID := PROID || LTRIM(TO_CHAR(PROINT,'0999'));
END;
MAXVALUE 99 MINVALUE 1 CYCLE
NOCACHE NOORDER;CREATE OR REPLACE TRIGGER "TEST_ADD"
AFTER INSERT ON "TEST"."TEST_TRIGGER"
FOR EACH ROW
begin
select :new.jh||:new.sjdwbh||lpad(i.nextval,2,'0') into :new.sj_id from dual;
end test_add;
Line: 10
Text: tname:=:NEW.test_trigger.jh || :NEW.test_trigger.sjdwbh || iError: PLS-00049: 错误的赋值变量 'NEW.TEST_TRIGGER'
Line: 10
Text: tname:=:NEW.test_trigger.jh || :NEW.test_trigger.sjdwbh || iError: PLS-00103: 出现符号 "UPDATE"在需要下列之一时:
.(*@%&=-+;</>atinmodnot
rem<an exponent (**)><> or != or ~=>=<=<>andorlikebetween
is nullis not||is dangling
Line: 11
Text: UPDATE SET test_trigger.sj_id='+' || &tname& || '+';Error: Hint: Value assigned to 'tname' never used in ''
Line: 11
Text: UPDATE SET test_trigger.sj_id='+' || &tname& || '+';Error: Hint: Value assigned to 'i' never used in ''
Line: 17
然后是下面的错误:(我用的是ORACLE8.1.6)Compilation errors for TRIGGER TEST.TEST_ADDError: PLS-00487: 对变量 'I' 的引用无效
Line: 15Error: PL/SQL: SQL Statement ignored
Line: 15Error: Hint: Variable 'tname' is declared but never used in ''
Line: 7
alter table test_trigger
modify(sj_id varchar2(21),jh varchar2(16),sjdwbh varchar2(3));
2、创建一个序列
create sequence test_seq
increment by 1
start with 1
maxvalue 9999
3、创建触发器
CREATE OR REPLACE TRIGGER TEST_ADD
BEFORE INSERT ON TEST_TRIGGER
FOR EACH ROW
declare
tname varchar2(21);
i number;
begin
select test_seq.nextval into i from dual;
tname:=concat(:NEW.jh,:NEW.sjdwbh);
tname:=concat(tname,to_char(i));
:new.sj_id:=tname;
end test_add;
ORA-04084:无法更改此触发器类型的NEW值
Compilation errors for TRIGGER TEST.TEST_ADDError: PLS-00487: 对变量 'I' 的引用无效
Line: 15Error: PL/SQL: SQL Statement ignored
Line: 15
Line: 15-----------------------------------------------
你创建序列了吗?
我是测试成功了。
另外你是操作哪一步导致上面的错误的?是修改表编译触发器?还是插入值出现的?