Time
7 hours 36 minutes
Difficulty
Beginner
CEU/CPE
3

Video Transcription

00:00
I welcome back to Module five sequel development. This is less than three triggers, and this lesson is going to have a few different sub lessons that linked together to create a nicely formed procedure that works off a trigger to record when an article is deleted.
00:17
And this is an example of an audit trigger that you might want to implement
00:22
when certain actions such as the leading important objects occurs because occasionally you will have users delete objects, and either they forget that they have deleted it or they don't realize that they've deleted it. And it's a good idea to have some kind of audit trails so that you can tell that it's been deleted.
00:41
Otherwise,
00:43
you may create this perception that data is just disappearing.
00:47
So with that said, let's go ahead and get started.
00:51
We're currently in sub Listen,
00:53
three point to the audit table, so first things first, we're gonna go ahead and create the audit table that will get populated when an article is deleted.
01:03
So that said, Let's right, click on our tables,
01:06
select, create new table.
01:11
We'll go to the Properties tab. We're gonna go ahead and call this audit table
01:18
when I had a new column, we're gonna make it the i d column.
01:23
And I'm gonna use the same data type that we've been using.
01:26
Big Ant. Unsigned. Not know.
01:29
Okay,
01:30
on the next new column is gonna be a bar charcoal. We're gonna call it
01:36
event.
01:40
Actually, we're gonna call it Ah,
01:42
event
01:45
type.
01:47
And it's a bar Char 100. It's not allowed to be. Noah needs to be filled in,
01:52
okay?
01:53
And then we're gonna select create new column, and we're going to create
02:00
description.
02:05
And, uh,
02:07
we could probably use of arch are 100.
02:09
But I'm just gonna provide some extra space and just go ahead and use tax
02:15
on. Don't say that. Can't be no either.
02:21
Let's go ahead and save that.
02:23
Persist.
02:25
We had an ad in our primary key.
02:31
I mean, Okay,
02:35
let's go back to let's go in and save that persists.
02:39
Then we'll go ahead and go to our auto and turn on our auto increment on the I D column. Save that.
02:50
So that completes our audit table. It's a pretty simple table. Basically, we're goingto have an event type,
02:55
which is purposely broad. So right now we're handling a delete event. So, for example, when a deletion of an article occurs, the event type will be article delete
03:07
with a description off article with I D and Title was deleted at this time
03:14
and
03:15
that will be that will make for a reasonably good audit. Trail will be able to see when articles were deleted
03:22
by their idea and title and their date in time and then down the road of anyone. Ever
03:27
ask what happened to an article? It seems to have vanished. We'll be able to check the audit table and see if it was deleted.
03:34
With that said, that completes this lesson, and I hope to see you in the next one.
03:38
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