7 hours 36 minutes
I welcome back to Module five sequel development.
We're currently in less than three triggers and we're moving into sub lesson 3.3, the procedure.
So in this lesson, we're gonna develop the procedure that we're gonna use with our trigger to populate our audit table.
Now, this procedure is gonna accept two parameters the article I. D.
And the article title.
And within that procedure, we're gonna insert the new record into the autumn table.
So let's go ahead and get started with that. We're going to use the D Beaver interface to implement the procedure.
So if we left click on procedure on the right, click and select create new procedure,
we're gonna name this procedure
It's a good idea to give your procedures and your functions names that kind of imply exactly what they're doing or tracking
It just makes everyone's life easier because you could read the title and kind of get a good idea of what's going on.
Now we look at the perimeters, the parameters de beber shows is the parameters. Unfortunately, we can't actually make any changes here. There's no options to create new parameters.
We're going to make a deterministic, which means that the procedure
gets the same variable or the same parameters. In the next call.
It assumes that it can return the same result, which is allows my sequel to make some assumptions. So I'm just gonna turn that on.
And the parameters that we're going to add to this procedure are as funnels
gonna add the article i d.
And that's gonna be a big ant unsigned,
just like the, uh,
variable types that we have on the tables.
And the next one is going to be art of Coal title
Bartsch are 100 just like we used for the title. It's a good idea that when you're accepting parameters that are supposed to accept a specific Tables column data to make those the same data types That way there's no truncation areas. For example, if we put bar chart
some titles would obviously probably be longer than that. And what would happen is you just get the 1st 10 characters,
so we're gonna make sure that we support the same length.
so what are we going to d'oh? We're going to insert
go ahead and name the database. CMS
which is the database we're in right there.
So we're just being extra specific
on a table
and we're gonna name the columns were inserting into event
Hope I'm not in the parentheses.
on that description.
And what are we inserting were certainly the values
recall that we made the
primary I d
for the i. D column, the primary key. And we made that auto increment. That's why we don't have to listed here. It's going to automatically increment itself whenever we add a new record.
So we're inserting the values.
Article II, or the event type is
and the description will be cats
Article I d.
Comma the actual article actual article I. D
so that we have a space between that and the next word article title
Running a room a little bit here,
and that is it. Let's go ahead and say that
there's a code that will generate it. You can see the d beavers also adding that the limiter to make sure that it doesn't clash with the delimit er at the end statement
Okay, I made a mistake. I forgot a parentheses because we have one that sending the cat function and we have one that's ending the values.
So let's go ahead and save that
There we go. So we've created our procedure.
Now let's go ahead and give that a test.
So if we go to sequel editor first, make sure see messes are
active. Database Sequel editor. New sequel editor.
Right click on the article deleted
Jerry Sequel Call
reading. Copy this portion
of the call procedure.
Paste it in there and there's no four key checks on this table, which is fine. It's an audit table. It's not as important as an audit table.
So let's go five on and
this is a titled test
and go ahead and run. This
okay, seems like we have another air, and this happens from time to time.
It looks like I spelled article wrong here, so let's go ahead and fix that.
But in are in there
article I d.
Go back to our
script, going and running again.
It ran it updated one row. Let's go look at our audit table.
Let's refresh that
and we can see, the event type is article delete.
You see, the description is our client E five
article title. This is a title test so we could see the procedure worked. And that completes this lesson. And the next lesson we will implement the actual trigger that calls the procedure and that will actually finish up our triggers lesson. I hope you're able to get this procedure implemented. And I hope to see you in the next lesson. Thank you.