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!
