语句级，行级触发器区别

[billlyh]

行级触发器对DML语句影响的每个行执行一次。
语句级触发器对每个DML语句执行一次，

如果一条INSERT语句在TABLE表中插入500行，那么这个表上的语句级触发器只执行一次，而行级的触发器就要执行500次了。
对这句是怎么理解的呢？

drop trigger tr_insert_employee
CREATE OR REPLACE TRIGGER tr_insert_employee
after insert
ON scott.emp
FOR EACH ROW --说明创建的是行级触发器
BEGIN
for i in 1..10 loop
INSERT INTO employee(id,name,salary )
VALUES( 100+i,'trigger_test',100+i);
end loop;
commit;
END;

以这个为例，
如果是语句级触发器，那就只执行一次，会向 employee表插入10条数据，
如果是行级触发器，要执行10次，每次只插入一行数据，
是这样吗??????

[小灰狼W]

如果是行级触发器，emp表上插入几行就会执行多少次，每次插入10条记录 P.S. 触发器中不能有commit A row trigger fires each time the table is affected by the triggering statement. For example, if a statement updates multiple rows, then a row trigger fires once for each row affected by the UPDATE. If a triggering statement affects no rows, then a row trigger is not run. A statement trigger is fired once on behalf of the triggering statement, regardless of the number of rows affected by the triggering statement.

[billlyh]

引用 1 楼 wildwave 的回复:

如果是行级触发器，emp表上插入几行就会执行多少次，每次插入10条记录 P.S. 触发器中不能有commit A row trigger fires each time the table is affected by the triggering statement. For example, if a statement updates multiple rows, then a row trigger fires once for each row affected by the UPDATE. If a triggering statement affects no rows, then a row trigger is not run. A statement trigger is fired once on behalf of the triggering statement, regardless of the number of rows affected by the triggering statement.

我写这个trigger怎么没生效呀，我向emp新插入一数据，再查employee，没有插入10条记录呀，一条都没有

[小灰狼W]

SQL> create table employee(id number,name varchar2(100),salary number);
 
Table created
 
SQL> 
SQL> CREATE OR REPLACE TRIGGER tr_insert_employee
  2     after insert
  3     ON scott.emp
  4     FOR EACH ROW
  5  BEGIN
  6     for i in 1..10 loop
  7     INSERT INTO employee(id,name,salary )
  8         VALUES( 100+i,'trigger_test',100+i);
  9     end loop;
 10  END;
 11  /
 
Trigger created
 
SQL> insert into scott.emp(empno)values(999);
 
1 row inserted
 
SQL> select * from employee;
 
        ID NAME                                                                                 SALARY
---------- -------------------------------------------------------------------------------- ----------
       101 trigger_test                                                                            101
       102 trigger_test                                                                            102
       103 trigger_test                                                                            103
       104 trigger_test                                                                            104
       105 trigger_test                                                                            105
       106 trigger_test                                                                            106
       107 trigger_test                                                                            107
       108 trigger_test                                                                            108
       109 trigger_test                                                                            109
       110 trigger_test                                                                            110
 
10 rows selected
 
SQL> 

如果触发器成功创建，看看是不是往emp中插入数据后没有commit，并且在另一个会话中查看employee表

[sych888]

引用 2 楼 billlyh 的回复:

[quote=引用 1 楼 wildwave 的回复:] 如果是行级触发器，emp表上插入几行就会执行多少次，每次插入10条记录 P.S. 触发器中不能有commit A row trigger fires each time the table is affected by the triggering statement. For example, if a statement updates multiple rows, then a row trigger fires once for each row affected by the UPDATE. If a triggering statement affects no rows, then a row trigger is not run. A statement trigger is fired once on behalf of the triggering statement, regardless of the number of rows affected by the triggering statement.

我写这个trigger怎么没生效呀，我向emp新插入一数据，再查employee，没有插入10条记录呀，一条都没有[/quote] 先看看触发器生效了没？ 触发器的“COMMIT”是有外面的DML操作后的COMMIT触发的 行级触发器你可以简单的理解为 你的外面的DML操作涉及到了多少行，触发器执行多少次 语句触发器是不管你外面的DML语句操作涉及多少行，如10行或100行，仅仅触发触发器一次