SQL 在同一个表上插入后触发器更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6925651/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update with after insert trigger on same table
提问by Navin
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
我有一个在 tableA 上写一个插入触发器。这将使用相同的表但不同的列执行更新。执行此操作时出现错误。我的触发器是
create or replace trigger trigger_A
after insert on table_A
begin
update table_A set col1=1 where col1 is null;
end;
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.
我有一个应用程序将单独执行 col2 将被插入并且 col1 将保持为空。所以我的触发器会在插入行后为 col1 赋值。但是当插入一行时，我收到错误消息“触发器失败且无效”。这该怎么做。TIA。
回答by APC
If you want to assign a simple default value, the easiest way is to declare it on the table, using the DEFAULT clause.
如果您想分配一个简单的默认值，最简单的方法是使用 DEFAULT 子句在表上声明它。
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>
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.
这适用于文字或伪列，例如 SYSDATE 或 USER。如果要使用用户定义的函数或序列导出更复杂的值，则需要使用触发器。
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>
... with a trigger:
...带有触发器：
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>
Note that although every column on the table is defaultable, I have to populate at least one column to make the SQL valid:
请注意，尽管表中的每一列都是默认值，但我必须至少填充一列以使 SQL 有效：
SQL> insert into t42 values ()
2 /
insert into t42 values ()
*
ERROR at line 1:
ORA-00936: missing expression
SQL>
But I can pass in NULL to COL4 to get a completely defaulted record:
但是我可以将 NULL 传递给 COL4 以获得完全默认的记录：
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: my trigger uses the new 11g syntax. In previous versions we have to assign the sequence value using a SELECT statement:
注意事项：我的触发器使用新的 11g 语法。在以前的版本中，我们必须使用 SELECT 语句分配序列值：
select my_seq.nextval
into :new.col4
from dual;
回答by Nabeel Ahmed
You cannot update a table where the trigger is invoked:
您不能更新调用触发器的表：
Within a stored function or trigger, it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.
在存储的函数或触发器中，不允许修改已被调用函数或触发器的语句使用（用于读取或写入）的表。
Doing so will generate Error 1442:
这样做会产生错误 1442：
Error Code: 1442
Can't update table 'MyTable' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
In short, we are not allowed to update the table in use - but your case is simple, only want to update the field if it's NULL, for this choose BEFORE INSERT ON trigger, this way you can update all the fields of the new/current entry/row (as it has not been entered yet):
简而言之，我们不允许更新正在使用的表 - 但你的情况很简单，只想更新字段，如果它是 NULL，为此选择 BEFORE INSERT ON 触发器，这样你就可以更新新/当前条目/行（因为尚未输入）：
DELIMITER //
DROP TRIGGER IF EXISTS trigger_A//
CREATE TRIGGER trigger_A BEFORE INSERT ON table_A
FOR EACH ROW BEGIN
IF NEW.col1 IS NULL THEN
set NEW.col1 = <some-value>;
ENF IF;
END;
//
DELIMITER ;
