在同一张表上插入触发器后更新 [英] Update with after insert trigger on same table
问题描述
我有一个要在tableA上编写插入触发器的操作.它将使用相同的表但不同的列执行更新.执行此操作时出现错误.我的触发器是
I have a to write a insert trigger on a tableA. which will perform update with same table but different column. I am getting error while doing this. My trigger is
create or replace trigger trigger_A
after insert on table_A
begin
update table_A set col1=1 where col1 is null;
end;
我有一个应用程序,它将单独执行col2并将col1保持为空.因此,一旦插入行,我的触发器将为col1提供值.但是插入行时出现错误消息,提示触发器失败且无效". 这该怎么做. TIA.
I have an application will perform col2 alone will be inserted and col1 will be kept null. so my trigger will give value for col1 once the row is inserted. But i am getting error saying "Trigger is failed and invalid" when a row is inserted. How to do this. TIA.
推荐答案
如果要分配简单的默认值,最简单的方法是使用DEFAULT子句在表上声明它.
If you want to assign a simple default value, the easiest way is to declare it on the table, using the DEFAULT clause.
SQL> create table t42
2 ( col1 number default 1 not null
3 , col2 date)
4 /
Table created.
SQL> insert into t42 (col2) values (sysdate)
2 /
1 row created.
SQL> select * from t42
2 /
COL1 COL2
---------- ---------
1 03-AUG-11
SQL>
这适用于文字或伪列,例如SYSDATE或USER.如果要使用用户定义的函数或序列派生更复杂的值,则需要使用 触发器.
This works with literals or pseudocolumns such as SYSDATE or USER. If you want to derive a more complicated value with a user-defined function or a sequence, you will need to use a trigger.
这是表格的新版本...
Here is a new version of the table...
SQL> create table t42
2 ( col1 number default 1 not null
3 , col2 date default sysdate
4 , col3 varchar2(30) default user
5 , col4 number )
6 /
Table created.
SQL>
...带有触发器:
SQL> create or replace trigger t42_trg
2 before insert or update
3 on t42
4 for each row
5 begin
6 if :new.col4 is null
7 then
8 :new.col4 := my_seq.nextval;
9 end if;
10 end;
11 /
Trigger created.
SQL> insert into t42 (col1, col2, col3)
2 values (99, sysdate, 'MR KNOX')
3 /
1 row created.
SQL> select * from t42
2 /
COL1 COL2 COL3 COL4
---------- --------- ------------------------------ ----------
99 03-AUG-11 MR KNOX 161
SQL>
请注意,尽管表上的每一列都是默认值,但我必须至少填充一列以使SQL有效:
Note that although every column on the table is defaultable, I have to populate at least one column to make the SQL valid:
SQL> insert into t42 values ()
2 /
insert into t42 values ()
*
ERROR at line 1:
ORA-00936: missing expression
SQL>
但是我可以将NULL传递给COL4以获得完全默认的记录:
But I can pass in NULL to COL4 to get a completely defaulted record:
SQL> insert into t42 (col4) values (null)
2 /
1 row created.
SQL> select * from t42
2 /
COL1 COL2 COL3 COL4
---------- --------- ------------------------------ ----------
99 03-AUG-11 MR KNOX 161
1 03-AUG-11 APC 162
SQL>
Caveat Lector:我的触发器使用了新的11g语法.在以前的版本中,我们必须使用SELECT语句分配序列值:
Caveat lector: my trigger uses the new 11g syntax. In previous versions we have to assign the sequence value using a SELECT statement:
select my_seq.nextval
into :new.col4
from dual;
这篇关于在同一张表上插入触发器后更新的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!