Explain when it would be best to use a trigger and when best

Explain when it would be best to use a trigger and when best to use an event. Give examples.

Solution

>Triggers:
1.The MySQL trigger is a database object that is associated with a table. It will be activated when a defined action is executed for the table. The trigger can be executed when you run one of the following MySQL statements on the table: INSERT, UPDATE and DELETE. It can be invoked before or after the event.
Triggers are available in MySQL 5.0.2 and later.

2.Triggers are a requirement for any complex data integrity rules. These cannot be enforced anywhere except the database or you will have data integrity problems

Best way to use Triggers:

>To drive column values automatically.
>To enforce complex integrity constraints.
>To enforce complex business rules.
>To customize complex security authorizations.
>To maintain replicate tables.
>To audit data modification.

Example of a MySQL trigger:

>First we will create the table for which the trigger will be set:

mysql> CREATE TABLE people (age INT, name varchar(150));

Next we will define the trigger. It will be executed before every INSERT statement for the people table:

mysql> delimiter //
mysql> CREATE TRIGGER agecheck BEFORE INSERT ON people FOR EACH ROW IF NEW.age < 0 THEN SET NEW.age = 0; END IF;//

Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;

We will insert two records to check the trigger functionality.

mysql> INSERT INTO people VALUES (-20, ‘ram’), (30, ‘John’);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

At the end we will check the result.

Age Name
0 ram
30 josh

Events:

>Events can be used to create backups, delete stale records, aggregate data for reports, and so on. Unlike standard triggers which execute given a certain condition, an event is an object that is triggered by the passage of time and is sometimes referred to as a temporal trigger. You can schedule events to run either once or at a recurring interval when you know your server traffic will be low

>Creating Events
The following example creates an event:

DELIMITER |

CREATE EVENT myevent
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
BEGIN
UPDATE mytable SET mycol = mycol + 1;
END |
DELIMITER ;

>This event will run once, one hour from the time it was created. The BEGIN and END statements surround one or multiple queries which will be executed at the specified time. Because the semicolon is needed to terminate the UPDATE statement, you’ll need to switch delimiters before you issue the CREATE EVENT statement and then switch back afterwards if you’re working through a client.
>You can view a list of all existing events with SHOW EVENTS