Stored Procedures (Routines) / Triggers / Events in MySQL

Stored Procedure (Routines) DELIMITER // DROP PROCEDURE IF EXISTS Debug; // CREATE PROCEDURE Debug(Message TEXT) BEGIN CREATE TABLE IF NOT EXISTS _debug ( `id` int(10) unsigned NOT NULL auto_incremen

Stored Procedure (Routines)

DELIMITER //
DROP PROCEDURE IF EXISTS Debug; //
CREATE PROCEDURE Debug(Message TEXT)
BEGIN
CREATE TABLE IF NOT EXISTS _debug (
`id` int(10) unsigned NOT NULL auto_increment,
`msg` TEXT DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
);
INSERT INTO _debug(`msg`) VALUES(Message);
END; //

DROP PROCEDURE IF EXISTS ClearDebugMessages; //
CREATE PROCEDURE ClearDebugMessages()
BEGIN
TRUNCATE TABLE _debug;
END; //


 

Use this to log a message: CALL Debug('My message');And this to clear all messages logged: CALL ClearDebugMessages;

z.B. in PHP:

if (!$mysqli->query("CALL Debug('My message')")) {
    echo "CALL failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

Vor/Nachteile:

Stored routines can be particularly useful in certain situations:

• When multiple client applications are written in different languages or work on different platforms, but need to perform the same database operations.

• When security is paramount. Banks, for example, use stored procedures and functions for all common operations. This provides a consistent and secure environment, and routines can ensure that each operation is properly logged. In such a setup, applications and users would have no access to the database tables directly, but can only execute specific stored routines.

Stored routines can provide improved performance because less information needs to be sent between the server and the client. The tradeoff is that this does increase the load on the database server because more of the work is done on the server side and less is done on the client (application) side. Consider this if many client machines (such as Web servers) are serviced by only one or a few database servers.

Stored routines also enable you to have libraries of functions in the database server. This is a feature shared by modern application languages that enable such design internally (for example, by using classes). Using these client application language features is beneficial for the programmer even outside the scope of database use.

Trigger

Trigger sind Sonderformen von Stored Procedures, die bei einer speziellen Datenbankoperation ausgeführt werden.

• BEFORE INSERT – activated before data is inserted into the table.

• AFTER INSERT- activated after data is inserted into the table.

• BEFORE UPDATE – activated before data in the table is updated.

• AFTER UPDATE - activated after data in the table is updated.

• BEFORE DELETE – activated before data is removed from the table.

• AFTER DELETE – activated after data is removed from the table.

DELIMITER $$
CREATE TRIGGER before_marketingmaterial_address_update 
    BEFORE UPDATE ON marketingmaterial_address
    FOR EACH ROW BEGIN
 
    INSERT INTO marketingmaterial_address_log
    SET action = 'update',
        shop_id = OLD.shop_id,
        firmenname1 = OLD.firmenname1,
        firmenname2 = OLD.firmenname2,
        adresszusatz = OLD.adresszusatz,
        strasse = OLD.strasse,
        hausnummer = OLD.hausnummer,
        plz = OLD.plz,
        ort = OLD.ort,
        ansprechpartner = OLD.ansprechpartner,
        email = OLD.email,
        telefonnummer = OLD.telefonnummer,
        changedon = NOW(); 
END$$
DELIMITER ;

Eignet sich besonders, um Log der Änderungen mitzuschreiben, denn:

• The OLD and NEW keywords are very handy. The OLD keyword refers to the existing record before you change the data and the NEW keyword refers to the new row after you change the data.

To find all triggers associated with a particular table, you use the following query:

SELECT * FROM Information_Schema.Triggers
WHERE Trigger_schema = 'database_name' 
AND  Event_object_table = 'table_name';

Zum Löschen:

DROP TRIGGER table_name.trigger_name

Es gibt KEIN Alter Trigger, also müssen Trigger zum Ändern immer erst gelöscht und neu angelegt werden.

Scheduled Event

The following statement creates a recurring event that executes every minute and is expired in 1 hour from its creation time:

CREATE EVENT test_event
ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
ON COMPLETION PRESERVE
DO  
INSERT INTO messages(message,created_at)   
VALUES('Test MySQL recurring Event',NOW());

Auf vorhandene Events checken:

SHOW EVENTS FROM classicmodels;

Aber Achtung: abgelaufene Events werden sofort gelöscht!Um die Events abzuspeichern, muß explizit ON COMPLETION PRESERVE gesetzt sein (s.o.)!Zum manuellen Löschen dann einfach

DROP EVENT event_name.

Weitere Info:

http://dev.mysql.com/doc/refman/5.0/en/stored-routines.html

http://de3.php.net/manual/en/mysqli.quickstart.stored-procedures.php

Komplexere Beispiele:

http://www.mysqltutorial.org/stored-procedures-loop.aspx