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.
Now it's your transaction is a
method for us to combine sequel statements together into a single
group of logical work.
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,
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.
Now. What would happen if
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,
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
and ensure that it supports transactions.
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
So we're going to declare
Tiny ants. It's just a ***, but it's not allowed to be come as larger number. So it's safe space.
So we're declaring this new sequel, Air Flag. That's a tiny ain't tiny ant and we've defaulted to false,
So next we need to declare a continue handler
and this is all part of the transactions
transaction. Ah, syntax so that we can get it to work correctly.
for a sequel exception
So these two statements roll together.
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.
our sequel statements, we want to begin our transaction.
So we do that with a start
And that prevents it from
from my single from doing it the default way, which is it would
update and committed as soon as it ran the statement so it would update commit this. It would uncertain. Commit this.
So now it will send the database the data, but the database will not commit it
until it ah, gets the commit command.
So if we're going to do an if statement, if sequel
we're going to roll back.
That means it's going to undo what it tried to do.
And then we're also going to send out a statement from
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,
uh, change the signal sequence state. So we're going to select this will cause the procedure. Just spit out a message. Still,
I'm going to see Michael on that.
go ahead and close this up
and we still got that drop of exist, So we're just gonna rerun it
and it ran successfully.
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.
But if we break apart each component, it's each component by itself is not bad.
so we're doing all the stuff we did above.
But we added a sequel, Air Flag. We said It's the fault value to false.
The tiny in data type
value that can either be true or false, which is 0104 false one for true. We said it's the fault of false
if that occurs, if there's a sequel exception,
it sets our sequel Underscore Air Too true.
And then at the bottom. After we pass our checks, we start a transaction,
which tells us my sequel to not automatically commit the statements
and then runs the update statement
and the insert statement. Now, if very of both succeeded
sequel Air will still be false and then we can commit.
If sequel Air is true, then we're going to roll back. The statements were not going to commit anything.
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
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,
and there you go. We have successfully inserted a transaction into our procedure.
And that's how you implement a transaction with a roll back so that you can help make sure that
either all your statements run or none of them run.
Anyways, that completes this lesson, and I hope to see in the next one. Thank you.