MySQL- Check whether the event scheduler is turned on. If it isn\'t, code a stat

Question

MySQL- Check whether the event scheduler is turned on. If it isn't, code a statement that turns it on. Then create an event that inserts a test row that contains a test values into the Invoices_audit table every minute. To make sure that this event has been created, code a SHOW EVENTS   statement that views this event and a select statement that views the data that's inserted into the Invoices_Audit table. Once you're sure this event is working correctly, code a DROP EVENT statement that drops the event.

Explanation / Answer

Hi, I don't currently have MySQL on my computer so can't show you any output screenshots.
But you can use the statements mentioned below to complete the stated tasks.

To check if the event scheduler is on you can use either

SHOW VARIABLES WHERE VARIABLE_NAME = 'event_scheduler';

or

SELECT @@GLOBAL.event_scheduler;

This will show you the status of the event scheduler, whether it is ON or OFF.

If it is OFF, you can switch it on using:-

SET GLOBAL event_scheduler = ON;

Since I don't have the structure of the Invoices_audit table, I can't give you the INSERT statement for the next one in detail.
However here is the statement to create an event that will insert some data into the said table every minute; you will need to complete the INSERT statement.

CREATE EVENT MyInsertEvent ON SCHEDULE EVERY 1 MINUTE
DO INSERT <complete the INSERT statement here>;

To view this event use,

SHOW CREATE EVENT MyInsertEventG

or

SHOW EVENT MyInsertEventG

The first SHOW statement will give you all details about the CREATE EVENT statement used to create the event(including the INSERT statemnt)
while the second statement will show you information about the created event (excluding the INSERT statement)
The G is just to make the displayed results more readable by showing them vertically rather than in the default horizontal fashion.

To show all data in the Invoices_audit table use,

SELECT * FROM Invoices_audit;

If the table has a field that holds a time-stamp value, you can show the last inserted row in the table using a query similar to :-

SELECT * FROM Invoices_audit ORDER BY <time-stamp field> DESC
LIMIT 1;

To drop the event use,

DROP EVENT MyInsertEvent;

Hope this helps!

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.