！！在线急等：新增一条数据时，触发器，同时修改该表别一字段

[zhaoyuup]

问题：触发器修改同一表里的字段
场景：表cbdselect有两字段：empid,fileno，在系统前台插入一条数据时（只有empid值，没有fileno字段的值），我希望后台通过触发器，实现修改该条数据的fileno字段的值。
麻烦大家不要在网上ctr+c,ctr+v网上的帖子，我也看了不少，貌似不太适合我这种操作同一表的场景。
最好能够真实运行的实例。谢谢大家

[zhu2547cs]

create or replace trigger tr_demo before insert on cbdselect for each row begin :new.fileno :=想修改的内容 end tr_demo;

[zhaoyuup]

谢谢大侠，但是还是不行 :new.fileno :=想修改的内容 貌似直接这样写不行， create or replace trigger insertFileNo before INSERT ON dfylbx_cbdselect for each row begin update dfylbx_cbdselect set fileno=111 where pkid=:old.pkid; end; 我是这样写的，不报错，但是改不了值

[zhaoyuup]

可以了，忘了加分号了

[zhaoyuup]

再麻烦问一下：如果 :new.fileno :=想修改的内容 修改的内容是通过SQL查询的该怎么写？ 比如： :new.fileno :=（select salary+1 from dfylbx_cbdselect where empid=old.empid） 我这样写，编译报错，应该是书写格式有问题，子句单独运行是没问题的。 报错如下 TRIGGER SDSB20141113.INSERTFILENO 编译错误 错误：PLS-00103: 出现符号 "SELECT"在需要下列之一时： ( - + case mod new not null others <an identifier> <a double-quoted delimited-identifier> <a bind variable> avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date <a string literal with character set specification> <a number> <a single-quoted SQL string> pipe <一个带有字符集说明的可带引号的字符串文字> <一个可带引号的 SQL 字符串> 行：5 文本：:new.fileno := (select fileno from (select a.empid,(a.orgid)||decode((length(row_number() over (partition by a.orgid order by a.pkid))),1,('000'||row_number() over (partition by a.orgid order by a.pkid)),2,('00'||row_number() over (partition by a.orgid order by a.pkid)),3,('0'||row_number() over (partition by a.orgid order by a.pkid)),row_number() over (partition by a.orgid order by a.pkid) )as fileno from (select d.pkid,d.empid,(select o.parentorgid from om_organization o where o.orgid=(select e.orgid from om_employee e where e.empid=d.empid))orgid from dfylbx_cbdselect d)a) where empid=11); 错误：PLS-00103: 出现符号 "OVER"在需要下列之一时： . ( ) , * % & | = - + < / > at in is mod remainder not range rem => .. <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_ LIKE4_ LIKEC_ as between from using || multiset member SUBMULTISET_ 行：5 文本：:new.fileno := (select fileno from (select a.empid,(a.orgid)||decode((length(row_number() over (partition by a.orgid order by a.pkid))),1,('000'||row_number() over (partition by a.orgid order by a.pkid)),2,('00'||row_number() over (partition by a.orgid order by a.pkid)),3,('0'||row_number() over (partition by a.orgid order by a.pkid)),row_number() over (partition by a.orgid order by a.pkid) )as fileno from (select d.pkid,d.empid,(select o.parentorgid from om_organization o where o.orgid=(select e.orgid from om_employee e where e.empid=d.empid))orgid from dfylbx_cbdselect d)a) where empid=11);

[binsweet]

触发器中不能用自己表的数据更新自己，只能通过 ：new=....来赋值。 如果确需通过子表自己来更新，就不能放到触发器中了，只能放到应用层逻辑中