Trigger

MariaDB/MySQL Stored Language is called SQL/PSM.
There are 4 different types of Stored Language: Stored Procedures, Stored Functions, Triggers and Events.

Stored Procedures

Stored Procedure with a Cursor:

DELIMITER //
CREATE PROCEDURE cleanup(IN pData VARCHAR(48))
BEGIN
  DECLARE vId INTEGER;
  DECLARE vNotFound INTEGER;

  DECLARE cCleanUp CURSOR FOR
    SELECT id FROM test WHERE data = pData;

  DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET vNotFound = 1;
  
  OPEN cCleanUp;
  lGetRecord: LOOP …

CREATE TABLE `order` (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
, name VARCHAR(64) NOT NULL
) ENGINE = InnoDB;

INSERT INTO `order` VALUES
  (NULL, 'Test order 1')
, (NULL, 'Test order 2')
, (NULL, 'Test order 3');


CREATE TABLE pos (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
, order_id INT UNSIGNED NOT NULL
, name VARCHAR(64) NOT NULL
, amount SMALLINT NOT NULL
, price DECIMAL (6,2) NOT NULL
, status TINYINT NULL
) ENGINE = InnoDB;

INSERT INTO pos VALUES
  (null, 1, 'Schrauben', …

Abstract: In this article we discuss how big is the performance impact of MySQL TRIGGERs compared to application side logging (with INSERT) into a MySQL table.

What was in my mind from the past

A while ago when MySQL released its Stored Language features in v5.0 I have seen a book
[1
] about this topic. In this book was a performance comparison between different implementations of computational tasks, one was done in MySQL Stored Language. The result was, that MySQL Stored Language feature sucks also …

A while ago I did some research about MySQL audit functionality and logon a and logoff triggers. MySQL and MariaDB provide a logon trigger in the form of the init_connect variable but no logoff trigger where most of the work for auditing would be done. When we would have a logoff trigger we could track the login and possibility some activity of a user and implement auditing functionality.

Yesterday when I was looking into the code for an answer to the question of one of my customers this research came into …

1400 - 1449 1500 - 1549

• Error: 1450 SQLSTATE: HY000 (ER_FORBID_SCHEMA_CHANGE)

  Message: Changing schema from ‘%s’ to ‘%s’ is not allowed.

• Error: 1451 SQLSTATE: 23000 (ER_ROW_IS_REFERENCED_2)

  Message: Cannot delete or update a parent row: a foreign key constraint fails (%s)

• Error: 1452 SQLSTATE: 23000 (ER_NO_REFERENCED_ROW_2)

  Message: Cannot add or update a child row: a foreign key constraint fails (%s)

• Error: 1453 SQLSTATE: 42000 (ER_SP_BAD_VAR_SHADOW)

  Message: Variable …

1350 - 1399 1450 - 1499

• Error: 1400 SQLSTATE: XAE09 (ER_XAER_OUTSIDE)

  Message: XAER_OUTSIDE: Some work is done outside global transaction

• Error: 1401 SQLSTATE: XAE03 (ER_XAER_RMERR)

  Message: XAER_RMERR: Fatal error occurred in the transaction branch - check your data for consistency

• Error: 1402 SQLSTATE: XA100 (ER_XA_RBROLLBACK)

  Message: XA_RBROLLBACK: Transaction branch was rolled back

• Error: 1403 SQLSTATE: 42000 (ER_NONEXISTING_PROC_GRANT)

  Message: There is no such grant defined for user …

1300 - 1349 1400 - 1449

• Error: 1350 SQLSTATE: HY000 (ER_VIEW_SELECT_CLAUSE)

  Message: View’s SELECT contains a ‘%s’ clause

• Error: 1351 SQLSTATE: HY000 (ER_VIEW_SELECT_VARIABLE)

  Message: View’s SELECT contains a variable or parameter

• Error: 1352 SQLSTATE: HY000 (ER_VIEW_SELECT_TMPTABLE)

  Message: View’s SELECT refers to a temporary table ‘%s’

• Error: 1353 SQLSTATE: HY000 (ER_VIEW_WRONG_LIST)

  Message: View’s SELECT and view’s field list have …

Table of Contents

• What is a Materialized View?
• Implement your own Materialized Views
• Refreshing materialized views
• Hands on
• Create your own Materialized View:
• Refresh Materialized View on demand
• Refresh Materialized View immediate
• Materialized Views with snapshotting functionality
• Some performance benchmarks for our Materialized Views:
• Outlook
• Conclusion
• Literature

What is a Materialized View?

A Materialized View (MV) is the pre-calculated (materialized) result of a query. Unlike a simple VIEW the result …

With MySQL 5.0 the database provides trigger functionality on INSERT, REPLACE, UPDATE and DELETE.

Those of you who know some other RDBMS know, that there are also some system events where one would like to have triggers.

Unfortunately MySQL does not (yet) provide such functionality. This is sad because as database administrator this would be sometimes very helpful.

But you can build your own LOGON and STARTUP trigger.

MySQL provides some hooks for these events…

Complete Story (PDF 160 kbyte).