4.14 Events

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