Triggers

00:00

I welcome back to Model seven. This is less a 7.4 triggers and this lesson. We're going to review and implement triggers in our database now for review. Triggers respond to indicated changes in the data base so they could run before update after update after delete before insert and so on.

00:18

They do not support transactions, and you typically do not want to use triggers for critical database update. For example, inserting customer payment records or updating customer payment records their best used for logging or enforcing unique business rules, such as rounding to a required precision.

00:37

Now triggers Do get a bad rap in the industry due to a lot of poor implementations. If you program a trigger well, it will work well for you now something they could make triggers. Difficult to work with is if they are not properly documented or they're coated in a confusing manner,

00:54

and also bad performance can occur. If they're not written efficiently,

00:58

for example, consider it in after update trigger. Let's say you have a somewhat expensive trigger that occurs after an update. Now for a single record, maybe it takes two seconds to run, and that's not a big deal. A customer might update the record and they wait two seconds and

01:17

they can. They don't notice it and they move on.

01:19

Now let's say that there's some process in the database that updates I 1000 records now 1000 times to would be 2000 seconds, which will be a long time to wait. And that could definitely stop someone from doing their job or bring the application to a halt.

01:38

So that's the kind of things you want to be careful about

01:41

when writing triggers now. With that said, let's go ahead and move over to the development environment and take a look at implementing it. Trigger.

01:48

I welcome back to the development environment. We're gonna go ahead and implement a trigger that keeps the full name updated for us in the database, based on changes that occur against the first name and the last name by the update. Now it's likely we would have to write one of these for the insert statement as well,

02:06

but we're just going to do it for the update at this point. And then, of course, we wouldn't need one for delete because the whole records gone.

02:12

So with that said, Let's go ahead and get started and let's take a look at this trigger I've already designed. We're gonna walk through the code and discuss what it's doing,

02:22

so we dropped the trigger. If exists, that's a common code block that occurs above use procedures and so on, so that we can always run this script. And when do the ah delimit er swapping that occurs in a lot of the other places, too?

02:34

Then we create a trigger. Akeem Attica were in the axiomatic a database, but I'm being explicit with this. So even if I don't have a database selected indie beaver, it will run against this database,

02:46

and I'm titling it users before update full name.

02:51

So the way I like to code my triggers in my databases is table name,

02:55

the time frame in which it occurs. It occurs before the update and what it's updating. So full name.

03:04

So just by looking at the trigger name, I got a good sense of what the trigger is doing now. That's not always easy to do, depending on how complicated things are getting. But if you can make it the code, explain itself as you read it.

03:19

It's very useful to new programmers and even you when you come back after six months and have to look at the way something's being done.

03:27

Next is just the syntax sugar that brings a trigger together. So before update on axiomatic, Adat users for each row begin.

03:38

If old first name

03:40

is not equal to new first name

03:45

or

03:46

old, last name is not equal to new last name. Then set the new full name

03:54

equal to a cat nation of the new first name comma that in parentheses, comma space, comma, new last name. And if

04:05

so, the effect of this trigger is it's real simple, but it keeps a column updated for us on the user stable. So let's go ahead and run This

04:15

Okay says I haven't air. Let's try this again.

04:18

All right, Iran. I must add something highlighted.

04:21

And that's now in there.

04:25

And let's see if we can see these triggers. So let's refresh

04:31

uses before update full name.

04:34

There it ISS. Now let's go to the users table and give it a try. So let's take Ian,

04:41

who is picking in and name him John.

04:45

I'm gonna go ahead and put that change in save.

04:48

Okay, that didn't change. But let's refresh.

04:51

Now we have John pick

04:55

and let's just try the last name of Ross in er clicks save.

05:00

Go ahead and click. Refresh. Now we have John Ross.

05:02

All right,

05:04

So as we can see,

05:06

the full name is changing in response to a trigger that occurs after or to be precise before update of the first name and last name fields. So now we don't have to worry about the full name. Now, Trigger will watch that for us.

05:20

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

05:26

and that brings us to the 7.4 summary

05:29

and this lesson review Trigger scope and implementation. We implemented a trigger for updating the full name. We discussed a good code pattern to promote self documenting code

05:39

and remember to practice good form with your triggers.