4.10 Save Points

00:00

okay.

00:00

I welcome back to Model four sequel programming to we're currently in less than three concurrency and transactions were in sub lesson 3.4 save points and to go over save points, I decided to go ahead and write up a mock procedure that we can apply to the employees database.

00:19

We're gonna walk through that in a moment.

00:21

First, we're gonna describe what save points are safe. Points are away for transactions to roll back on lee

00:28

some of the commits that they've made, or some of the inserts and updates that they've made.

00:35

So you would use this typically in large procedures where you have a lot of transactions going on and it doesn't make sense to roll them all back.

00:44

So with that said, let's walk through this procedure. It'll be available for download as well, so that you can apply it against your own database and we'll talk through it.

00:53

So we create the procedure we call established employees.

00:57

It accepts four parameters. The department I d. The from date, the title I D and the title,

01:03

and I put under scores at the end of the names so that they don't clash with the names and the sequel queries.

01:11

They come from the table.

01:12

This is new. We labeled our procedure

01:17

by putting a name with a colon right before they began. And ah, I named it any name you want just a show that you don't have to name it. Anything specific can literally name it Anything you want. We end up using that down here. When we leave, Um, when you use the leave command, you have to provide the label that you're leaving,

01:36

which is why we defined it up here.

01:38

So next we declare three variables, we declare the amp I d.

01:42

We declare the sequel air and we said, It's the fault valued of false.

01:47

We declare a handler

01:49

for sequel airs. And we said that the true if they occur

01:53

so

01:55

online 15 We then set the impiety to the next employee number.

02:00

Now we have to do that because the auto increment is not on the in this table. So if we open up the employees properties, Tab and D B will see that auto increment is not checked, which means we have to provide it,

02:13

which means we have to logically decide what the next number is. And the way we do that

02:19

as we select the max number that exists

02:22

already

02:23

and then we add one to it, and that will give us our next number.

02:28

Next we start the transaction,

02:30

we create our save point. This is the new thing in this procedure that we haven't seen before. So we're creating a save point.

02:38

We're inserting into the employees table the data that we have. I've put some proxy data in there

02:46

now. It could be a policy in the front end to generate an employee, and then the user would just changes these values to what they are and then call a day employee procedure. But, you know, that varies a lot from company to company application application.

03:00

So that's what we've done right here in this procedure.

03:05

If Sinclair is true,

03:07

then we roll back to save point before employees, and then we leave the procedure.

03:12

So if we leave the procedure, none of this below happens because we've left.

03:16

However, if there's no sequel air, we continue on.

03:22

So at the next point we create another save point,

03:27

and that is we're gonna call that safe point before department.

03:31

We're gonna insert into the employees department, underscore EMP table the values that we had ah received from the select statement along with the values we got from the parameters that were past that

03:45

After that runs, we check again. If the secret lair is true.

03:49

If it is, we then rolled back to the save point before department

03:53

when we set the sequel heir to False so that we can go ahead and reset it for the final statement.

04:00

Now, if the department insert fells, it might be okay. That's its own table with its own set of data.

04:06

Um, that doesn't affect the title, so we can still insert the title.

04:12

So if we haven't employees end up without a department that's different.

04:15

If we could still save, save it and have them end up with a title that's better than them ending up with a title and no department that both know department and no title.

04:25

So that's why we're doing this same point and this rollback that specific between the inserts.

04:30

So we create a save point before title, we insert into titles and again provide the data that we had

04:39

received from parameters above and the employees idea that we established above by a select statement. I almost forgot something.

04:47

We're gonna need this

04:48

date in there as well.

04:53

And finally, if there's a sequel, Air True. Then we rolled back to save point before Title

04:58

Re and F,

05:00

and at this point, we're at the end of the procedure and

05:02

and we exit.

05:04

So we used. We have three safe points in here. They each check each transaction

05:10

and roll back if necessary.

05:12

Now, this isn't the best example just because it's were essentially rolling back one transaction per save point

05:18

and a lot of practical examples, you'd have probably three or four transactions per save point that you would roll back in a larger procedure. But

05:30

and this procedure, you can see the how we're using safe points, how we're rolling back. And it's something that keeping your tool belt

05:36

down the road should you need it.

05:39

So that completes this Lessing on safe points.