Time
9 hours 41 minutes
Difficulty
Intermediate
CEU/CPE
10

Video Transcription

00:00
Welcome back to Module seven. This is less than 7.5 events, and this lesson. We're going to review the scope and implementation of events in the my sequel database. Engine events are blocks of code that X accused on a schedule.
00:13
Sometimes they were referred to as temporal triggers because they're triggered by time as opposed to regular triggers, which are triggered by a database table events
00:22
Events are useful for items such as database table optimization, log rotations, data archives or the generation of complicated report tables.
00:32
Now, something to consider when implementing events is that the more often the event fires,
00:38
the more likely the concept is best served in another database object. For example, an event that fires every second is probably too often, and the frequency suggests that a trigger might be appropriate
00:51
Now. With that said,
00:52
let's go ahead and take a look at implementing a log event that rotates our logs so that our log table does not get too large.
01:00
Welcome back to the development environment. I've already laid out the code for an event that controls the size of a log table,
01:08
so let's go ahead and walk through this code and discuss what it's doing. So at the very top we have the similar syntax that we see in the other object creates, which is that we drop if exists, and we also do the limiter switch.
01:22
But after that, we start the syntax that is unique to an event. So we create event. We named the event, and then we put it on a schedule. So the schedule is at current time stop plus interval one hour. The biggest obstacles on these types of objects is getting that syntax right. For example, if I forget that keyword at
01:41
and between the word schedule and current timestamp, the object creation will fail
01:45
because it's missing that keyword.
01:48
So this creates an event that runs on a one hour interval. So what everyone? Our it runs
01:53
and next we have the do and begin keywords,
01:57
and after that, we start the real meat of the event.
02:01
First we declare two variables. First, we declare two variables a max underscore variable. I've fixed that underscore to the word Max so that I don't activate the keyword Max that my sequel knows, and that's a manager with a default value of 10 so that is gonna control the size of the log table,
02:20
and I've said it at a rather small value of 10 normal log records in a real production database would probably be 600,000
02:28
records.
02:30
The next variable declared his test variable, and after that we set the test variable equal to the count of the log record table.
02:38
And then the next day. Mint is an if statement that tests if the test variable is larger than the max variable. If it is, we then set the test variable to a floor function call off test divided by two, which is effectively dividing that table in half.
02:55
We then make a table expression. That is the log record I DS from the log record
03:01
in ascending order, limited by the value of test.
03:07
So we will get the first end records from that table based on the size of the energy that test is, and that will effectively cut our log table in half when we run the next statement, which is delete from law record, where log record I D is in
03:23
a selection of star from the delete targets table expression
03:28
above
03:29
and then we end the event. So it's a fairly simple event that has some powerful implementation
03:37
because it is common for database log records or log records in general to grow out of control and eventually eat all the memory space that a computer has and what events such as this. We can help prevent that from happening.
03:51
So that completes this portion of lesson. Let's go ahead and head over to the summary and finish out the lesson, and that brings us to the 7.5 summary and this lesson. We reviewed the event, scope and purpose.
04:01
We implemented a logging event and the schematic IT database. Two key things to remember about events in my sequel is that events are blocks of code that execute on a schedule and also remember that the event scheduler must be turned on in my sequel
04:16
without the event schedule er being turned on, you can define events, but they will not execute.
04:23
So that completes this lesson, and I hope to see you in the next Thank you

Up Next

Intermediate SQL

This free course introduces the student to intermediate concepts found in the implementation and application of Structured Query Language (SQL) within professional business environments.

Instructed By

Instructor Profile Image
Kitt Parker
Instructor