Database Triggers: Examples & Overview

Instructor: Christopher Nott

Christopher has taught college level information technology and IT security, has a master's degree in Information Security, and holds numerous industry certifications.

Database triggers are small sets of code attached to database tables that can automate many database functions when activated by a single command. This lesson will look at common database trigger constructs and examples.

What is a Trigger?

Have you ever watched a line of Dominoes fall when you knock the first one down? You've just created a trigger. Your touch of the first tile triggered the chain reaction that knocked down the rest. This is the same principal applied to database triggers. A single event on a particular database element, like a table, can cause a chain reaction of many other events in the database. Let's look at some definitions and examples.

Definition and Examples

A trigger, in database terms, is a set of instructions that is activated (or we say it is 'fired') by some specific event, normally a command issued through the database's Data Manipulation Language (DML). DML is what we use to put data in, take data out, retrieve data from, and change data within a database. While each database vendor may add their own extensions to the language, most DML instances contain at least four basic commands, SELECT, INSERT, UPDATE, and DELETE. Triggers can be set to fire BEFORE, AFTER, or INSTEAD OF any of these commands.

Triggers are most commonly used to automate related, repetitious tasks with a database and to ensure that data is consistent across the database wherever it is stored. Consider the following scenario.

John is a new employee at Super Cyber Store (SCS). Like most companies, SCS has a few different applications that will need access to John's information, like Human Resources, Payroll, and Benefits. Without a database trigger, the data entry person at SCS would have to enter all of John's personal data three times, once for each system. With a trigger, the database that holds the records for the three systems can be made to transfer all of John's information after it's entered one time. Once the data entry person at SCS enters the data into the HR table in the database, a trigger inserts the same data into the payroll and benefits tables. Lets' look at what that code might look like.


simple_trigger_example


Now, let's dissect this code snippet line-by-line.

The first line is telling the database 'I want to create a trigger called new_employee'.

The second line is telling the database 'After a record is inserted into the table hr_table I want you to execute these instructions …'

The keyword 'begin' in the third line tells that database that this is the beginning of the instructions to execute after the insert. In line six, we see the 'end' keyword, which tells the database it has reached the end of the instructions.

Lines four and five are the instruction we want to execute. In this case we're telling the database to insert the values 'john', '123 Any Street', and '1022' into the fields named 'name', 'street', and 'workid' in the table named benefits_table.

So once the trigger is created, every time data is inserted into the hr_table, that same data will also be inserted into the benefits_table. That way, the data between the two tables will always be consistent.

But what about the payroll table?

We could create another trigger, just like the previous example, on the benefits_table to insert the data into the payroll system. That would work, but, since we're using the same data, and we already have a trigger, we can just modify it and add one more set of instructions. See the code snippet below.


compound_trigger_example


Lines one and two are exactly the same.

On line three, we tell the database that this is the beginning of the instructions and use the keyword 'atomic' to signify that there is more than one instruction to be executed. Line eight is the end of the block.

To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support