Module 7 Challenge Part 3

00:00

All right, welcome back to Module seven. This is less than 7.8 challenge. Three. We've already gone over those top bullet points quite a few times, so I'm not gonna go over them again. However, I'm going to focus on that last bullet point and this lesson or this challenge we're going to create a sequel trigger that implements the correct precision

00:20

to solve some new business requirements Now, with that said, let's go ahead and head over to the requirements document to identify exactly what's being asked for.

00:32

All right, here we are at the requirements document again. This is for the Model seven course challenged three precision trigger,

00:40

and the requirements are as follows utilizing the axiomatic of database. Create the appropriate triggers that will round PM tran dot amount to two decimal places. The default is four decimal places. This should work on updates and inserts, so the key note there is that

00:58

should work on updates and inserts, so we'll need a trigger for the update, and we'll need a trigger for the insert.

01:03

The table required is PM Tran. The column required is the amount column, and some additional instructions are as follows due to business requirements, we need to ensure that the amounts found on transaction tables are rounded to the correct precision.

01:19

A Que Matic A uses a precision of four decimal places by default. We do not want to change the actual table architecture,

01:27

so implement triggers that will ensure that the decimal amount provided is rounded to two decimal places. For example, 4.1255 becomes 4.1300

01:42

So go ahead and pause the video and see if you can come over the solution on your own before viewing the solution.

01:49

Now, with that said, let's go ahead and take a look at that solution.

01:53

So here we are at the solution. You know, walking through the script I created to solve the problem indicated in that requirements document. I think you'll find the code used is pretty short, simple and to the point

02:06

now, with that said, let's go ahead and walk through this code

02:08

so at the top were dropping the triggers if they exist, and I plural eyes that because there are two triggers were creating, we're creating one for the update action and one for the insert action Then I dropped those so that if I run this script, I don't get the object already exists there.

02:25

And the next line of doing a delimit er swap, and that's so that we can use the semicolon in the body of the definition. Otherwise, this semicolon right here would cause an air.

02:34

And then I'm just walking through the standard create trigger syntax. So of course you need to get that syntax correct. Otherwise, the trigger create command won't work.

02:44

So create trigger

02:46

trigger name. And I like this name because it's the table name. It's the before keyword. It's the action, which is update, and it's what it's doing keyword that has something to do, what it's doing, precision. So just reading the trigger name gives you an idea of what it is doing.

03:01

And then before update on table name for each row began, and this is where we actually implement the code, this solves their problem,

03:10

which is set new amount. New dot amount equal to the rounding of new dot amount to two decimal places, and that is it that is done. That's all we need.

03:21

And then we end the definition with a dollar sign dollar signs

03:24

and then below that, we create the insert variant of that trigger. Again, we just get this syntax correct. And then the code is exactly the same. Said new amount equal to the rounding of new amount to two decimal places. And then we swapped back to the old semicolon delimit er at the end of that. And if we run this, it'll

03:44

run over and over

03:46

because of the way we've designed it.

03:47

I'm gonna go ahead and open up PM Tran, take a look at it. I will click out in one of the amount columns. Where is the column that are Trigger is focusing on. So we'll give it a test, would say to

04:00

2 55.5560

04:08

and then I'm gonna save that,

04:11

and that should be com 0.56. So let's see.

04:15

Refresh.

04:16

And sure enough, it did. So the trigger is working correctly,