实在找不到报错

[heng_M]

create or replace TRIGGER FORMSON_0021_I 
BEFORE INSERT ON seeyon.formson_0021
for each row
    declare row_1 int; row_2 int; ins int; sub int; dal int; oldFm_id VARCHAR(99); newFm_id VARCHAR(99);
BEGIN
    select :new.FIELD0012 into row_1 from formson_0021;
    select (:new.FIELD0012 - 1) into row_2 from formson_0021; 
    select :new.field0009 into ins from formson_0021;
    select :new.field0013 into sub from formson_0021;
    select :new.field0010 into dal from formson_0021 where FIELD0012 = row_2;
    select :new.FORMMAIN_ID into oldFm_id from formson_0021;
    select :new.FORMMAIN_ID into newFm_id from formson_0021 WHERE FIELD0012 = row_2;
    
    IF row_2 > 0 AND oldFm_id = newFm_id THEN
        IF ins = 0 THEN
            UPDATE formson_0021 SET FIELD0010 = (dal - sub) WHERE FIELD0012 = row_1 AND FORMMAIN_ID = newFm_id;
        ELSIF sub = 0 THEN
            UPDATE formson_0021 SET FIELD0010 = (dal + ins) WHERE FIELD0012 = row_1 AND FORMMAIN_ID = newFm_id;
            END IF;
    ELSIF row_2 <= 0 THEN
        UPDATE formson_0021 SET FIELD0010 = dal WHERE FIELD0012 = row_1 AND FORMMAIN_ID = newFm_id;
    END IF;
    
END;

[nayi_224]

create or replace TRIGGER FORMSON_0021_I 
BEFORE INSERT ON seeyon.formson_0021
for each row
    declare 
    row_1 int := :new.FIELD0012; 
    row_2 int := (:new.FIELD0012 - 1); 
    ins int := :new.field0009; 

[heng_M]

引用 1 楼 nayi_224 的回复:

create or replace TRIGGER FORMSON_0021_I 
BEFORE INSERT ON seeyon.formson_0021
for each row
    declare 
    row_1 int := :new.FIELD0012; 
    row_2 int := (:new.FIELD0012 - 1); 
    ins int := :new.field0009; 

这只是换了种变量赋值的方式吗

[nayi_224]

引用 2 楼 heng_M 的回复:

[quote=引用 1 楼 nayi_224 的回复:]

create or replace TRIGGER FORMSON_0021_I 
BEFORE INSERT ON seeyon.formson_0021
for each row
    declare 
    row_1 int := :new.FIELD0012; 
    row_2 int := (:new.FIELD0012 - 1); 
    ins int := :new.field0009; 

这只是换了种变量赋值的方式吗[/quote]

select :new.field0009 into ins from formson_0021;

查到多条数据报错。 就算不报错也没有这么写的。

[heng_M]

引用 3 楼 nayi_224 的回复:

[quote=引用 2 楼 heng_M 的回复:] [quote=引用 1 楼 nayi_224 的回复:]

create or replace TRIGGER FORMSON_0021_I 
BEFORE INSERT ON seeyon.formson_0021
for each row
    declare 
    row_1 int := :new.FIELD0012; 
    row_2 int := (:new.FIELD0012 - 1); 
    ins int := :new.field0009; 

这只是换了种变量赋值的方式吗[/quote]

select :new.field0009 into ins from formson_0021;

查到多条数据报错。 就算不报错也没有这么写的。[/quote]

create or replace TRIGGER FORMSON_0021_I 
AFTER INSERT ON seeyon.formson_0021
for each row
declare     
    row_1 INT := :new.FIELD0012; 
    row_2 INT := (:new.FIELD0012 - 1); 
    ins DECIMAL(20) := :new.field0009;
    sub DECIMAL(20) := :new.field0013;
    oldFm_id NUMBER(19,0) := :new.FORMMAIN_ID;
    vid NUMBER(19,0) := :new.ID;
    newFm_id NUMBER(19,0) := 'select :NEW.FORMMAIN_ID into newFm_id from formson_0021 f WHERE f.SORT = row_2 AND f.FORMMAIN_ID = oldFm_id';
    dals DECIMAL(20) := 'select :new.field0010 into dals from formson_0021 f where f.SORT = row_2 AND f.FORMMAIN_ID = oldFm_id';
    
PRAGMA AUTONOMOUS_TRANSACTION; 
BEGIN
    DBMS_OUTPUT.PUT_LINE(row_1);

    IF oldFm_id = newFm_id THEN
        IF ins is null THEN
          --  UPDATE formson_0021 SET FIELD0010 = cast(((dals - sub)) as decimal(20)) WHERE ID = vid;
          UPDATE formson_0021 SET FIELD0010 = 3  WHERE ID = vid;
        ELSIF sub is null THEN
          --  UPDATE formson_0021 SET FIELD0010 = cast(((dals + ins)) as decimal(20)) WHERE ID = vid;
            UPDATE formson_0021 SET FIELD0010 = 1  WHERE ID = vid;
            END IF;
    ELSIF oldFm_id <> newFm_id THEN
       -- UPDATE formson_0021 SET FIELD0010 = dals WHERE ID = id;
        UPDATE formson_0021 SET FIELD0010 = 2 WHERE ID = id;
    END IF;
    commit;
END;

[heng_M]

目前改成这样之后 报错

[nayi_224]

newFm_id NUMBER(19,0) := 'select :NEW.FORMMAIN_ID into newFm_id from formson_0021 f WHERE f.SORT = row_2 AND f.FORMMAIN_ID = oldFm_id';

这句话的意思是，将'select :NEW.FORMMAIN_ID into newFm_id from formson_0021 f WHERE f.SORT = row_2 AND f.FORMMAIN_ID = oldFm_id'这个字符串赋值于newFm_id ，但是newFm_id 是number类型，肯定报错。 在此之外，你应该先了解一下oracle的基本语法。即使没有报错，select :NEW.FORMMAIN_ID into newFm_id from formson_0021 f WHERE f.SORT = row_2 AND f.FORMMAIN_ID = oldFm_id本身也是一句废话，除非你是想用奇怪的方式抛异常。 还有，我就搞不懂了，怎么总有那么多新手喜欢在触发器里写自治事务，到底从哪传出来的。《oracle编程艺术》这么经典的书不看，非要看那些地摊货？

[heng_M]

引用 6 楼 nayi_224 的回复:

newFm_id NUMBER(19,0) := 'select :NEW.FORMMAIN_ID into newFm_id from formson_0021 f WHERE f.SORT = row_2 AND f.FORMMAIN_ID = oldFm_id';

这句话的意思是，将'select :NEW.FORMMAIN_ID into newFm_id from formson_0021 f WHERE f.SORT = row_2 AND f.FORMMAIN_ID = oldFm_id'这个字符串赋值于newFm_id ，但是newFm_id 是number类型，肯定报错。 在此之外，你应该先了解一下oracle的基本语法。即使没有报错，select :NEW.FORMMAIN_ID into newFm_id from formson_0021 f WHERE f.SORT = row_2 AND f.FORMMAIN_ID = oldFm_id本身也是一句废话，除非你是想用奇怪的方式抛异常。 还有，我就搞不懂了，怎么总有那么多新手喜欢在触发器里写自治事务，到底从哪传出来的。《oracle编程艺术》这么经典的书不看，非要看那些地摊货？

非常感谢您的指导及推荐的书，之前没有学过Oracle，一直是写SQL Server的东西，没有对Oracle的具体认知，单靠百度还是太过苍白无力。