Bug #102638 improvements for TRIGGER
Submitted: 18 Feb 9:36 Modified: 15 Apr 12:53
Reporter: Александр Ммммммм Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S4 (Feature request)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[18 Feb 9:36] Александр Ммммммм
Description:
Feature request:

1. Trigger tables. During execution trigger code, 2 special tables are created: deleted, inserted.
This is implemented in MSSQL:
https://docs.microsoft.com/en-us/sql/relational-databases/triggers/use-the-inserted-and-de...

old values get into "deleted" table during UPDATE, DELETE
new values get into the table "inserted" during UPDATE, INSERT

Perhaps it would be better to name the new tables <table_name>_deleted, <table_name>_inserted, this will allow using these tables not only in the trigger code, but also to move the logic into procedures

2. "FOR STATEMENT" clause in trigger syntax (instead of "FOR EACH ROW")
will populate new trigger tables (deleted, inserted) and raise trigger code just once for multirow DML or onerow DML statements
This will allow to perform bulk DML operations extremely efficiently

3. Make trigger_event clause optional
trigger_event: [INSERT] [,] [UPDATE] [,] [DELETE]
this will allow to write your business logic code in one place

All the above changes will allow to write very short, highly efficient and extremely performant code.

How to repeat:
Example:
We have 2 tables: documents, document_positions. And want denormalize sum

CREATE TABLE documents (
    id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT
    , sum INT NOT NULL DEFAULT 0
)
;
CREATE TABLE document_positions (
    id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT
    , document_id INT UNSIGNED NOT NULL
    , price INT
    , quantity INT
    , CONSTRAINT document_positions_documnent_id_fk FOREIGN KEY (document_id) REFERENCES documents (id)
)
;

Current TRIGGERs code should look something like this:
CREATE TRIGGER document_positions_a_i_trg AFTER INSERT
ON document_positions
FOR EACH ROW
BEGIN
    UPDATE documents
    SET sum = sum + IFNULL(NEW.price * NEW.quantity, 0)
    WHERE id = NEW.document_id
    ;
END

CREATE TRIGGER document_positions_a_u_trg AFTER UPDATE
ON document_positions
FOR EACH ROW
BEGIN
    UPDATE documents
    SET sum = sum + IFNULL(NEW.price * NEW.quantity, 0)
    WHERE id = NEW.document_id
    ;
    UPDATE documents
    SET sum = sum - IFNULL(OLD.price * OLD.quantity, 0)
    WHERE id = OLD.document_id
    ;
END

CREATE TRIGGER document_positions_a_d_trg AFTER DELETE
ON document_positions
FOR EACH ROW
BEGIN
    UPDATE documents
    SET sum = sum - IFNULL(OLD.price * OLD.quantity, 0)
    WHERE id = OLD.document_id
    ;
END

New syntax TRIGGER code should look something like this:
CREATE TRIGGER document_positions_a_trg AFTER
ON document_positions
FOR STATEMENT
BEGIN
    WITH NEW AS (
        SELECT document_id, IFNULL(SUM(price * quantity), 0) sum
        FROM inserted
        GROUP BY document_id
    )
    UPDATE documents d
    INNER JOIN NEW ON d.id = NEW.document_id
    SET d.sum = d.sum + NEW.sum
    ;
    WITH NEW AS (
        SELECT document_id, IFNULL(SUM(price * quantity), 0) sum
        FROM deleted
        GROUP BY document_id
    )
    UPDATE documents d
    INNER JOIN OLD ON d.id = OLD.document_id
    SET d.sum = d.sum - OLD.sum
    ;
END

Now make bulk INSERT into document_positions
INSERT documents VALUES ()
;
SET @document_id = LAST_INSERT_ID()
;
INSERT document_positions (document_id, price, quantity)
WITH RECURSIVE num(n) AS (
    SELECT 1 n
    UNION ALL
    SELECT n + 1
    FROM num
    WHERE n + 1 <= 1000
)
SELECT @document_id, n, 1 FROM num
;

Compare the results:
1. In the current version, I have 3 similar pieces of business logic code scattered across 3 triggers.
In the new version, 1 piece of business logic code in one place.
2. In the current version, when doing INSERT with 1000 rows, the trigger code will be called 1000 times, UPDATE documents will be executed 1000 times. In the new version, the trigger code will be called 1 time and only 2 UPDATEs will be executed and update documents data only 1 time!
[15 Apr 12:53] MySQL Verification Team
Hi Mr.  Mmmmmmm,

Thank you for your feature request.

We find it very useful and it is, henceforth, verified.

This is, however, a major undertaking, so it will take time to decide on its implementation.

Verified as reported.