5.12 Procedure
Video Activity
Join over 3 million cybersecurity professionals advancing their career
Sign up with
Required fields are marked with an *
or
Already have an account? Sign In »
Video Transcription
00:00
I welcome back to Module five sequel development.
00:03
We're currently in less than three triggers and we're moving into sub lesson 3.3, the procedure.
00:10
So in this lesson, we're gonna develop the procedure that we're gonna use with our trigger to populate our audit table.
00:16
Now, this procedure is gonna accept two parameters the article I. D.
00:21
And the article title.
00:23
And within that procedure, we're gonna insert the new record into the autumn table.
00:28
So let's go ahead and get started with that. We're going to use the D Beaver interface to implement the procedure.
00:35
So if we left click on procedure on the right, click and select create new procedure,
00:41
we're gonna name this procedure
00:43
article deleted.
00:48
It's a good idea to give your procedures and your functions names that kind of imply exactly what they're doing or tracking
00:57
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.
01:03
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.
01:14
We're going to make a deterministic, which means that the procedure
01:19
gets the same variable or the same parameters. In the next call.
01:23
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.
01:34
And the parameters that we're going to add to this procedure are as funnels
01:41
gonna add the article i d.
01:45
And that's gonna be a big ant unsigned,
01:48
just like the, uh,
01:52
variable types that we have on the tables.
01:55
And the next one is going to be art of Coal title
02:00
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
02:17
Ken,
02:19
some titles would obviously probably be longer than that. And what would happen is you just get the 1st 10 characters,
02:25
so we're gonna make sure that we support the same length.
02:30
All right,
02:30
so what are we going to d'oh? We're going to insert
02:36
and two,
02:38
go ahead and name the database. CMS
02:40
which is the database we're in right there.
02:45
So we're just being extra specific
02:49
on a table
02:52
and we're gonna name the columns were inserting into event
02:55
type.
02:57
Hope I'm not in the parentheses.
03:00
Event type
03:05
on that description.
03:07
Okay.
03:09
And what are we inserting were certainly the values
03:14
now
03:16
recall that we made the
03:19
primary I d
03:21
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.
03:34
So we're inserting the values.
03:38
Article II, or the event type is
03:43
article
03:45
delete,
03:47
and the description will be cats
03:54
Article I d.
04:00
Comma the actual article actual article I. D
04:06
comma
04:08
space
04:10
so that we have a space between that and the next word article title
04:18
on then
04:20
Tom
04:23
Article
04:25
title.
04:28
Running a room a little bit here,
04:30
semi colon
04:33
and that is it. Let's go ahead and say that
04:40
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
04:47
and persists.
04:54
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.
05:04
So let's go ahead and save that
05:08
and persist.
05:10
There we go. So we've created our procedure.
05:13
Now let's go ahead and give that a test.
05:16
So if we go to sequel editor first, make sure see messes are
05:19
active. Database Sequel editor. New sequel editor.
05:24
Right click on the article deleted
05:27
Jerry Sequel Call
05:30
reading. Copy this portion
05:31
of the call procedure.
05:33
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.
05:43
So let's go five on and
05:49
this is a titled test
05:54
and go ahead and run. This
06:02
okay, seems like we have another air, and this happens from time to time.
06:06
It looks like I spelled article wrong here, so let's go ahead and fix that.
06:14
But in are in there
06:15
article I d.
06:17
Save that.
06:19
Persist.
06:21
Go back to our
06:24
script, going and running again.
06:27
It ran it updated one row. Let's go look at our audit table.
06:30
Let's refresh that
06:32
and we can see, the event type is article delete.
06:35
You see, the description is our client E five
06:40
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.
Up Next
Instructed By
Similar Content
