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;
CLIENT_ID CLIENT_NAME CLIENT_BUDGET TRG_TYPE 1 Alice 100 R 2 Beryl 200 R 1 Alice 100 C 2 Beryl 200 C 1 Alice 700 R 3 Carol 300 R 1 Alice 700 C 3 Carol 300 C
Related Topics
How to Copy a Record in a SQL Table But Swap Out the Unique Id of the New Row
Sql: Using Null Values VS. Default Values
Union All VS or Condition in SQL Server Query
Using Ssis to Extract a Xml Representation of Table Data to a File
How to Retrieve the Current Value of an Oracle Sequence Without Increment It
Use Variable with Top in Select Statement in SQL Server Without Making It Dynamic
When Would You Use a Table-Valued Function
How to Select Only the First Rows for Each Unique Value of a Column
Retrieve Column Names and Types of a Stored Procedure
Get Avg Ignoring Null or Zero Values
Prepared Statement on Postgresql in Rails
How to Handle Optional Parameters in SQL Query