Time
7 hours 36 minutes
Difficulty
Beginner
CEU/CPE
3

Video Transcription

00:00
all right, welcome back to Module four. Sequel programming to were in less than three concurrency and transactions, and we're in the first sub lesson, 3.2 transactions.
00:09
Now it's your transaction is a
00:13
method for us to combine sequel statements together into a single
00:17
group of logical work.
00:19
So if we take a look at the procedure that we coded in the previous lesson will see that we have two statements that need to run in order for the, uh,
00:29
the works to complete successfully. And if one of these fails, then the work has not completed successfully. For example, on Line 31 we update the salaries table and we set their current salary, too, and we manipulate the end date.
00:43
Now. What would happen if
00:44
this first statement ran? And then something went wrong? Table was locked and this insert into employee salary failed. Well, then we only did half the work. We updated the salary, but we didn't insert the new record. And now there's a logical gap in our salary table with respect to business rules. So how would we prevent that? Well,
01:04
my sequel on a lot of data base equal engines provide a way to deal with that in the form of transactions. Now, something to be aware of is that there are some sequel engines that do not support transactions, so you would not be able to use them in that in that database. Tension. So if you happen to be in a certain database engine, be sure to look up and
01:25
and ensure that it supports transactions.
01:27
So let's go ahead and start coating this. So what? We need to dio as we need to declare a new variable at the top
01:38
So we're going to declare
01:44
sequel Air.
01:47
Tiny ants. It's just a ***, but it's not allowed to be come as larger number. So it's safe space.
01:53
The fault falls.
01:56
So we're declaring this new sequel, Air Flag. That's a tiny ain't tiny ant and we've defaulted to false,
02:02
which will be zero.
02:07
So next we need to declare a continue handler
02:13
and this is all part of the transactions
02:15
transaction. Ah, syntax so that we can get it to work correctly.
02:19
Continue
02:22
handler
02:24
for a sequel exception
02:31
on DDE.
02:34
We're going to
02:36
set
02:38
sequel
02:40
there
02:44
equal to true.
02:49
So these two statements roll together.
02:53
Mmm. And we're saying that we have an air handler, and if that executes, we want to set this sequel Air flag too True.
03:00
Right?
03:04
So then right here
03:07
before
03:09
our sequel statements, we want to begin our transaction.
03:14
So we do that with a start
03:16
transaction command.
03:21
It's in my colon.
03:24
And that prevents it from
03:27
from my single from doing it the default way, which is it would
03:31
update and committed as soon as it ran the statement so it would update commit this. It would uncertain. Commit this.
03:38
So now it will send the database the data, but the database will not commit it
03:43
until it ah, gets the commit command.
03:49
So if we're going to do an if statement, if sequel
03:53
air
03:59
equal false,
04:00
then
04:03
commit
04:11
all right,
04:13
else
04:15
we're going to roll back.
04:17
That means it's going to undo what it tried to do.
04:24
And then we're also going to send out a statement from
04:30
we're gonna do. We're gonna send another air statement out in this case, but we're gonna do it a different way. We're not gonna actually,
04:36
uh, change the signal sequence state. So we're going to select this will cause the procedure. Just spit out a message. Still,
04:45
select
04:46
the transaction
04:49
filled
04:51
and rolled
04:53
back.
04:57
I'm going to see Michael on that.
05:00
And if
05:02
go ahead and close this up
05:10
and we still got that drop of exist, So we're just gonna rerun it
05:16
and it ran successfully.
05:19
So what is going on down here? Let's walk through this one more time because this procedure is starting to grow, as you may have noticed.
05:26
But if we break apart each component, it's each component by itself is not bad.
05:33
So
05:35
so we're doing all the stuff we did above.
05:39
But we added a sequel, Air Flag. We said It's the fault value to false.
05:46
The tiny in data type
05:46
is a
05:48
value that can either be true or false, which is 0104 false one for true. We said it's the fault of false
05:57
we declared a
05:59
continue handler.
06:01
And if that is,
06:03
if that occurs, if there's a sequel exception,
06:06
it sets our sequel Underscore Air Too true.
06:12
And then at the bottom. After we pass our checks, we start a transaction,
06:16
which tells us my sequel to not automatically commit the statements
06:21
and then runs the update statement
06:25
and the insert statement. Now, if very of both succeeded
06:29
sequel Air will still be false and then we can commit.
06:32
If sequel Air is true, then we're going to roll back. The statements were not going to commit anything.
06:39
We're gonna return it. We're gonna have the procedure return a message to the caller that the transaction filled and rolled back. Now we could have
06:46
raised our own signals. Signal State sequel, Ste era again. But in this instance, we decided just to return a message. The procedure will actually exit is if, though it did not have an heir,
06:58
so
07:00
and there you go. We have successfully inserted a transaction into our procedure.
07:08
And that's how you implement a transaction with a roll back so that you can help make sure that
07:15
either all your statements run or none of them run.
07:17
Anyways, that completes this lesson, and I hope to see 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