Time
7 hours 36 minutes
Difficulty
Beginner
CEU/CPE
3

Video Transcription

00:00
I welcome the Module four sequel programming, too.
00:03
This is less than five sequel events for moving into sub lesson. 5.3 events won the previous lesson. We define what an event was. We in Stan? She aided in the event and we watched it run. It was an event that ran every second, and we could see that it was correctly running and inserting data into a counter table every second.
00:23
And this lesson murders solved a problem we previously solved
00:27
using
00:28
procedures and triggers. Now there's gonna be a trade off. The event is going to be
00:33
more clearly defined in the easier to read, but it does a little more work than you know. It should have to dio Where is a? The procedure and triggers
00:43
was a little more complicated, but
00:45
it was more closely confined to the data it specifically needed to change, and the changes were live. So if a trigger executed on an update, we would see the derived change immediately. Now, in this instance, this is going to be a drive table that's created once a day, every day,
01:03
so it would be one of those instances where you end up telling the customer or the client that
01:08
hey, this table or this report will be available once a day. And if you make changes, you won't see the updates until the next day. I think if you've been in a nightie field, you've seen situations like that.
01:19
So let's take a look at what we're doing.
01:23
So at the top here I am deleting the count table, which is not necessary for this one.
01:33
So I'm making sure the global event scheduler is on. I know it's on from the previous assignment, but I just have that on there just in case I drop the event at the beginning so that I could just re Insan. She ate it and run the script as I'm working through it.
01:47
So we create an event maintained manager table.
01:51
I'm schedule
01:52
every one day,
01:53
and this starts today
01:57
and
01:57
it it we, uh, you know, we followed the keywords or the commands do begin. First thing we do is we delete from managers
02:06
so that statement by itself will empty out the manager's table,
02:10
and then we insert into managers
02:15
the selection statement. Select reports, too,
02:17
from classic models dot employees where reports to is not know and group by reports, too.
02:23
So essentially, this event empties the table and then re populates the table, and this kind of statement is not very heavy. So even if we had, you know, 10,000 managers it would complete, and probably a second wouldn't be, Ah to she wouldn't put too much strain on the server. And it's only running once a day.
02:42
And you could run that at Ah, you know,
02:44
midnight if you wanted to,
02:46
uh, when users aren't using the system and it would be a pretty lightweight run pretty fast.
02:52
So
02:53
that completes this lesson. This was a quick lesson on all we did, is we? Instead, she ate in an event that runs once a day that dumps the table a derive table and then re populates it with current data.
03:06
Anyways, this script is
03:08
available for download, so feel free to download it and running against your own database and, uh, you know, test it out and see how well it works for you
03:16
and ah, that completes this lesson. I hope to see you in the next one. Thank you.

Up Next

Introduction to SQL

This introductory SQL training teaches SQL core concepts that can be applied in professional environments. Once students complete this course, they 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