Triggers

00:00

Welcome back to module five. This is less than 5.7. Trigger triggers are commonly used to enforce unique business rules. For example, perhaps after inserting a transactional record, you need to round to a specific precision. That's Ah,

00:15

what business management wants to use. For example, a lot of data base applications will have four decimal, six decibels of precision and, on occasion, the business. Use my say, Well, we only want to

00:28

decimal places of precision. So let's go ahead and implement that, and you might use a trigger to implement that.

00:34

Now they're also commonly used to log events.

00:38

When implementing triggers. Try to use strong core code organization wrapped complex code in a procedure that is called by the trigger. Don't just stack endless amounts of code in the trigger.

00:51

This is really useful for triggers that are doing multiple things where you have multiple distinct things happening. And if you just stack all that code on top of each other. When someone goes to edit that code, it could be difficult to tell When one action is starting and the old one is ending.

01:08

Try to use names that self document for the trigger For example, if you have a trigger on the user table and it's after insert, call the trigger user after insert.

01:17

Then when someone sees the trigger just by the title, they have an idea of what's going on or what causes that trigger to fire.

01:25

Avoid triggers in the falling scenarios. You don't want to use trails for complex data processing. They aren't ideal in those scenarios. You also don't want to use them in transactions, and my civil won't let you do this, although some other database engines may, you do want to avoid it if you find yourself in that environment

01:42

now. With that said, let's go ahead and start implementing a logging trigger.

01:47

All right, let's get a trigger implemented. I already have one laid out here. Let's go ahead and walk through what's occurring.

01:55

So I'm doing a delimit er change. When we implement this in the migration file, we actually won't need this line or the delimit er switching to occur. I'm going to create trigger users after delete the reasonable name. It's targeting the users table and it occurs after delete.

02:13

So after delete on users for each row begin, we're gonna declare an i d.

02:17

It's big and unsigned. We want to do our best to use the same data types or the columns that were targeting. So our i d fields are big imagers on signs, so we're going to use that data type. Otherwise, we can get these weird airs if one of the state

02:36

structures goes out of bounds for

02:38

the data type, like an imager verse a big manager.

02:43

So we're gonna set the i d to a selection of the max i d. From logs where school I d equals old school i d. So we're bringing in the school idea of the user that got deleted. And then we're doing a coal ace function police function so that if we get no back, we'll just use zero instead.

03:02

That way

03:04

we don't get no and said I d equal to know which would fail on the insert.

03:09

So once we get the next Max, I d. Plus one back,

03:13

we then insert into lochs the values the i. D. The old school i d. The title that the user was deleted. No, for the code because it's not an air code. And then aken caffeinated message user old I d deleted from database

03:30

with the created and updated time steps being now.

03:34

So let's go ahead and implement that in a migration file.

03:39

So I'm in the wagon session Ssh session.

03:45

So I'm in the right folder. So we're gonna do PSP artists in

03:49

make colon a migration

03:54

trigger

03:57

user after delete.

04:02

So the more files you make, the more happy you will be if you, uh, went with good naming structures. If your files don't reflect what's in them and you end up having hundreds of files as an application of balls,

04:17

you will not be a happy camper. When none of those fall names describe what's inside the contents, you actually have to look inside them.

04:27

Let's go open that

04:29

folder or that file. So database migrations tree users after the lead.

04:34

So when added this one

04:36

and I'm gonna go ahead and peak back in my old my old, uh, get g p a function.

04:44

I know I need something along these lines. Db unprepared.

04:48

Was it a copy paste that in? But I wouldn't delete what I don't need,

04:56

and I'm gonna go to my script and I'm going to copy

05:01

except I'm not going to get the delimit er's changing.

05:08

So go ahead and place this in there.

05:12

Now, we have some issues with the single quotes

05:20

and to fix that, I'm gonna actually switch these beginning single quotes and ending sequel quotes, double quotes

05:28

that should solve that issue.

05:31

And I need another one of these.

05:35

I need it in down section

05:40

and we're gonna drop trigger if exists,

05:46

like so

05:47

not forget. I semicolons

05:50

gonna save it. Now I'm gonna go into our

05:55

ssh session and I'm gonna run a PHP artisan.

06:00

My great refresh.

06:08

All run through.

06:15

Okay, it looks like our trigger is created. So let's give a let's go. Make sure it's in our database,

06:24

you fresh And sure enough it is

06:28

So we can see that the code has been implemented in our DB recession.

06:33

So now I need to see the database because when I ran the refresh, it would have wiped the database data. So it's be artisan

06:43

because it's a good idea to test, definitely at least test once when you implement a new data structure just to make sure that something doesn't catch you off guard or that you made you might have made some silly mistake because it happens to the best of us.

06:58

So it looks like it's seated.

07:00

Let's check our logs table.

07:01

No doubt in there. We haven't logged anything. Let's go to our users table

07:06

and let's delete a random user 95.

07:11

Leave him.

07:13

Save it. He's gone.

07:15

Now let's go check our logs table refresh.

07:19

And sure enough, we have an idea of zero. Which is fine

07:23

because we used a police to select zero. If we get no back school, i d three

07:30

title is user deleted and description is User 95 deleted from database, and we can see the time that happened.

07:36

So that appears to be working. We are effectively logging when users are deleted,

07:44

so that completes this portion of lesson. Let's go ahead and head over to the summary,

07:50

and that brings us to the 5.7 triggers summary. We discussed when and when not to use triggers, and then we implemented a logging trigger in our migration falls that logs when users are deleted.