Time
7 hours 36 minutes
Difficulty
Beginner
CEU/CPE
3

Video Transcription

00:00
I welcome to model for sequel programming to this is less than five sequel events were in the first sub lesson five point to intro to events.
00:10
So what is a my sequel event or an event? All the database engines support this kind of concept.
00:18
Well, an event is a scheduled action
00:21
that you indicate you want the database to take at some
00:26
date or time or some interval.
00:29
So in this example,
00:31
we're gonna show you how to run the schedule, event command and what you need to do to get one going.
00:37
So again, I have a sequel script, and at the top, I'm, ah, leading from employees count table. I'm using the count table from the employees that a base that we worked with in previous examples again,
00:50
one thing that you need to do is make sure that your global event scheduler is on, um, in my sequel by default, it is off.
01:00
Of course, if you're not using events at all, you will want to leave it off as it consumes. Resource is, that could be used for ah, running your application.
01:10
So only turn it on if you're running events. But don't be scared to turn it on, either. Um, it's not going to consume too many. Resource is just a good habit to ah, you know, turn on what you're gonna use and turn off what you're not going to use.
01:23
So at the top, I was working with another test events. So I'm going to get rid of that,
01:30
and the script again will be available for download
01:33
to the top. I dropped the event if exists, that so I can run this script as I work with it. I said to the limiter, So it doesn't clash with the limiter in the definition
01:42
on DDE
01:44
and the definition we run the create event
01:49
of the event name.
01:51
I called this second insert count.
01:53
It's on a schedule
01:56
every one second,
01:57
and it starts.
02:00
This is the start date, so I've put it in for a past date and time.
02:06
Well, it's currently today, but
02:08
the past time will make sure that it starts immediately
02:12
and the event does something simple again. I set the number to the max
02:19
counter from the count table, plus one.
02:22
So I am implementing it
02:23
now. The coal lease function just as a refresher. What it does is if
02:29
this comes back, is no
02:31
is if this results in a knoll or nothing,
02:36
we instead replace that knoll with zero. So zero plus one would be one. Now, why would we do that? Well, if the tables completely empty as it will be when there's no records, I will get an old back because there's nothing there.
02:47
I didn't take that value, and I inserted into the employees count table.
02:53
I then end the statement using the DL emitter to find above.
02:58
I then need to switch this limiter back to a semicolon.
03:01
And then I run the show Events Command, which will show all the events on the database as well as their settings. So if I go ahead and run this,
03:13
see? Okay, so at the top, I'm deleting from the table, so I'm resetting its values
03:20
right now. You can see I have values in there,
03:23
and I'm gonna go ahead and run this.
03:28
You can see that it's showing the details of the event just in Stan. She ated
03:35
It's got a interval field of second and the interval value is one. So everyone second it runs is enabled.
03:42
It started already because that's a past value
03:46
but place that in the future in the future, it obviously would not run
03:51
until that date and time had passed.
03:54
Now, if we take a look at the count table
03:57
and refresh it,
04:00
okay, goes up to 32
04:01
33 34 35 36 I keep hitting Refresh. We can see that it is indeed inserting data every second, and that is events. So what is an event? It's a scheduled action that the database takes
04:16
set on your schedule
04:18
at your interval. Starting on whatever date you choose, you don't have to run it every second or every month or every week. You could have it run one time in the future two months from now as well,
04:30
so there's a lot of flexibility. Their events are good for different types of database cleanup. For example, if you want to make sure a table never gets above a certain size,
04:41
you can have a monthly
04:43
event that runs and basically cleans up the table for you
04:46
or does something similar Now you may be thinking to yourself, Well, you know,
04:51
procedures and triggers and all of that stuff I can kind of
04:56
do either or
04:57
in both. And that's true. Sometimes. Sometimes you could, uh, do what you're doing in a trigger, an event,
05:03
and vice versa.
05:05
It kind of comes down to what you're trying to achieve
05:09
and how you're trying to implement it.
05:11
But there is some flexibility and that you could end up using one or the other
05:15
to solve the same situation.
05:18
So that closes this lesson for events. If you down with this script and were and worked with it, I hope you that you got it working and we're able to see how the event was declared and how it functions.
05:30
And that's really the best way to learn is to practice
05:33
with that said, That finishes this lesson, and I hope to see you in the next thank you.

Up Next

Introduction to SQL

This introduction SQL training course teaches SQL core concepts that are applied in professional environments. Once completed with the course, students will be able to query and interact with an SQL database, and know how to design database schemas.

Instructed By

Instructor Profile Image
Kitt Parker
Instructor