5.11 Audit Table

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.