Oracle“语句级”触发器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9574946/
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
Oracle 'statement level' Trigger
提问by user547453
I want to create a Statement level Trigger which means I want to insert only one record into table EMP_AUDIT when 1 or more rows are inserted into table EMP. For example: if I have 10 records inserted into EMP, then only 1 record should be inserted into EMP_AUDIT table.
我想创建一个语句级触发器，这意味着当 1 行或多行插入到表 EMP 中时，我只想将一条记录插入到表 EMP_AUDIT 中。例如：如果我有 10 条记录插入到 EMP 中，那么只有 1 条记录应该插入到 EMP_AUDIT 表中。
There are no constraints on columns. (i.e. can be NULL)
对列没有限制。（即可以为NULL）
I tried to use the following trigger but it gives me the Error(2,2): PL/SQL: SQL Statement ignored Error(2,14): PL/SQL: ORA-00947: not enough values
我尝试使用以下触发器，但它给了我错误（2,2）：PL/SQL：SQL 语句被忽略错误（2,14）：PL/SQL：ORA-00947：没有足够的值
CREATE OR REPLACE
TRIGGER TRIG_EMP AFTER INSERT ON EMP
BEGIN
INSERT INTO EMP_AUDIT
VALUES (TRANID,EMPNUM,SYSDATE);
END;
CREATE TABLE EMP
(TRANID NUMBER,
EMPNUM VARCHAR2(100),
EMPLOC VARCHAR2(100));
CREATE TABLE EMP_AUDIT
(EVENTID NUMBER,
EMPNUM VARCHAR2(100),
ENTRDATE DATE);
回答by Jeffrey Kemp
The statement-level trigger (which you have) cannot see the data that was inserted. After all, if there were 10 rows inserted, what values should the columns be for your audit table?
语句级触发器（您拥有的）无法看到插入的数据。毕竟，如果插入了 10 行，那么审计表的列应该是什么值？
You need a row-level trigger for this to work, e.g.:
您需要一个行级触发器才能工作，例如：
CREATE OR REPLACE
TRIGGER TRIG_EMP
AFTER INSERT ON EMP
FOR EACH ROW
BEGIN
INSERT INTO EMP_AUDIT
VALUES (:NEW.TRANID,:NEW.EMPNUM,:NEW.SYSDATE);
END;
回答by Manoranja
Use this piece of code:
使用这段代码：
CREATE OR REPLACE TRIGGER
TRIG_EMP
AFTER INSERT ON EMP
FOR EACH ROW
BEGIN
INSERT INTO EMP_AUDIT
VALUES (:NEW.TRANID,:NEW.EMPNUM,:NEW.SYSDATE);
END;
