Row-Level Trigger VS Statement-Level Trigger

row-level trigger vs statement-level trigger

The main difference is not what can be modified by the trigger, that depends on the DBMS. A trigger (row or statement level) may modify one or many rows*, of the same or other tables as well and may have cascading effects (trigger other actions/triggers) but all these depend on the DBMS of course.

The main difference is how many times the trigger is activated. Imagine you have a 1M rows table and you run:

UPDATE t
SET columnX = columnX + 1

A statement-level trigger will be activated once (and even if no rows are updated). A row-level trigger will be activated a million times, once for every updated row.


Another difference is the order or activation. For example in Oracle the 4 different types of triggers will be activated in the following order:

Before the triggering statement executes
Before each row that the triggering statement affects
After each row that the triggering statement affects
After the triggering statement executes

In the previous example, we'd have something like:

Before statement-level trigger executes

Before row-level trigger executes
One row is updated
After row-level trigger executes

Before row-level trigger executes
Second row is updated
After row-level trigger executes

...

Before row-level trigger executes
Millionth row is updated
After row-level trigger executes

After statement-level trigger executes

Addendum

* Regarding what rows can be modified by a trigger: Different DBMS have different limitations on this, depending on the specific implementation or triggers in the DBMS. For example, Oracle may show a "mutating table" errors for some cases, e.g. when a row-level trigger selects from the whole table (SELECT MAX(col) FROM tablename) or if it modifies other rows or the whole table and not only the row that is related to / triggered from.

It is perfectly valid of course for a row-level trigger (in Oracle or other) to modify the row that its change has triggered it and that is a very common use. Example in dbfiddle.uk.

Other DBMS may have different limitations on what any type of trigger can do and even what type of triggers are offered (some do not have BEFORE triggers for example, some do not have statement level triggers at all, etc).

Precise difference between statement on Row and on Table

If you perform an

INSERT INTO EMPLOYEE
SELECT ...

and that SELECT returns 100 rows so that the INSERT inserts 100 rows, your first trigger will execute 100 times, once for each row. In the same situation, your second trigger will execute only once.

You can use a BEFORE INSERT...FOR EACH ROW trigger to change the values that are being inserted by accessing them via the :NEW variable. E.g.,

:new.column_1 := 'a different value';

You cannot do that in a statement level trigger (which is what your 2nd trigger is).

There are also limitations in row level triggers (which is what your 1st trigger is). In particular, you may not SELECT from the trigger's base table (EMPLOYEES in this case), because that table is said to be "mutating". The exact reasons, as I understand them, go back to the core principles of relational databases -- specifically that the results of a statement (like INSERT INTO...SELECT) should not depend on the order in which the rows are processed. There are workarounds to this limitation, however, which are beyond the scope of your original question, I think.

Statement-level trigger in Oracle

A statement level trigger will fire once after the triggering statement has run, unlike a row level trigger which fires for each affected row.

After statement triggers are generally used to do processing of the set of data - e.g. logging into a table, or running some post-statement processing (usually a procedure).

If you're wanting to update a value in every affected row, then I would advise using a before row level trigger. The update statement in your question would affect all rows where the COLUMN column is null.

Whether a trigger is actually the right thing to use is debatable. However, I would recommend you look at the documentation and also this Oracle-base article to gain a better understanding of how triggers work and when you might use them.

Before insert (statement level trigger) isn't working

The doc says

NEW

Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers.
This variable is null in statement-level triggers and for DELETE operations.

so the update can't work properly since the NEW value is null. You really need a row level trigger.

Is there any way we can emulate the way of using :old and :new of row triggers in statement triggers in PL/SQL

You could use a compound trigger.

Create the types:

CREATE TYPE client_master_obj IS OBJECT(
id NUMBER,
name VARCHAR2(20),
budget NUMBER(10,2)
);

CREATE TYPE client_master_table IS TABLE OF client_master_obj;

Then the trigger:

CREATE TRIGGER client_master_cmp_trigger
FOR DELETE OR UPDATE ON client_master
COMPOUND TRIGGER
data client_master_table := client_master_table();
AFTER EACH ROW
IS
BEGIN
data.EXTEND(1);
data(data.COUNT) := client_master_obj(
:OLD.client_id,
:OLD.client_name,
:OLD.client_budget
);
END AFTER EACH ROW;
AFTER STATEMENT
IS
BEGIN
INSERT INTO audit_table (client_id, client_name, client_budget, trg_type)
SELECT id,
name,
budget,
'C'
FROM TABLE(data);
END AFTER STATEMENT;
END;
/

Which, for the sample data:

CREATE TABLE client_master (client_id, client_name, client_budget) AS
SELECT 1, 'Alice', 100 FROM DUAL UNION ALL
SELECT 2, 'Beryl', 200 FROM DUAL UNION ALL
SELECT 3, 'Carol', 300 FROM DUAL UNION ALL
SELECT 4, 'Debra', 400 FROM DUAL UNION ALL
SELECT 5, 'Emily', 500 FROM DUAL;

CREATE TABLE audit_table (client_id, client_name, client_budget, trg_type) AS
SELECT cm.*, 'X' FROM client_master cm WHERE 1 = 0;

Then after:

UPDATE client_master
SET client_budget = client_budget + 600
WHERE client_id IN (1, 2);

DELETE FROM client_master WHERE client_id IN (1, 3);

Then the audit table contains (with the row trigger also firing for the same changes):

SELECT * FROM audit_table;


Leave a reply



Submit