[Oracle] What are the differences between after and before in Oracle triggers? What are the differences between statement-level triggering and row-level triggering?

Difference between after and before:
One is triggered before the record operation, and the other is triggered after the record operation.

For example, a foreign key is defined between tables. When deleting a primary key, you must first Delete the foreign key table. In this case, there are different orders.

 

Difference between statement-level triggering and row-level triggering:

The statement-Level Trigger is triggered only once, and the row-Level Trigger is the number of rows triggered.
If it is defined as statement-level, the trigger only runs once when the delete from T is executed,

If it is defined as a row-level operation, the above delete operation will run the trigger multiple times (several records will be run several times ).

 

 

The above is the simplest difference. Of course there are other differences.

The trigger sequence is different.
Statement-level trigger before
Row-level trigger before
Row-Level Trigger After
Statement-Level Trigger After

[Test @ ora10gr1 #2009-12-18/21: 08: 09] SQL> Create or replace trigger t1_triger_before
2 before insert on T1
3 begin
4 dbms_output.put_line ('Statement-Level Trigger before ');
5 end;
6/

Trigger created.

[Test @ ora10gr1 #2009-12-18/21: 08: 09] SQL> Create or replace trigger t1_triger_after
2 after insert on T1
3 begin
4 dbms_output.put_line ('Statement-Level Trigger after ');
5 end;
6/

Trigger created.

[Test @ ora10gr1 #2009-12-18/21: 08: 09] SQL> Create or replace trigger t1_triger_before_each_row
2 before insert on T1 for each row
3 begin
4 dbms_output.put_line ('row-Level Trigger before ');
5 end;
6/

Trigger created.

[Test @ ora10gr1 #2009-12-18/21: 08: 09] SQL> Create or replace trigger t1_triger_after_each_row
2 after insert on T1 for each row
3 begin
4 dbms_output.put_line ('row-Level Trigger after ');
5 end;
6/

Trigger created.

[Test @ ora10gr1 #2009-12-18/21: 08: 09] SQL>
[Test @ ora10gr1 #2009-12-18/21: 08: 09] SQL> set serveroutput on
[Test @ ora10gr1 #2009-12-18/21: 08: 09] SQL> -- pay attention to the trigger sequence
[Test @ ora10gr1 #2009-12-18/21: 08: 09] SQL> insert into T1 values (1, 'A ');
Statement-level trigger before
Row-level trigger before
Row-Level Trigger After
Statement-Level Trigger After

1 row created.

[Test @ ora10gr1 #2009-12-18/21: 08: 09] SQL> -- note the number of trigger times
[Test @ ora10gr1 #2009-12-18/21: 08: 09] SQL> insert into T1
2 select 1, 'A' from dual Union all
3 select 2, 'B' from dual Union all
4 select 3, 'c' from dual;
Statement-level trigger before
Row-level trigger before
Row-Level Trigger After
Row-level trigger before
Row-Level Trigger After
Row-level trigger before
Row-Level Trigger After
Statement-Level Trigger After

3 rows created.