RelationalDBDesign RelationalDBDesign


Creating Triggers  «Prev  Next»
Lesson 5 The insert, update, and delete triggers
Objective Create an insert, update, or delete trigger.

Insert, update, and delete Triggers

Database Trigger Usage
Now that you have seen the syntax of triggers, you will view another example of how to code a trigger and then complete an exercise where you create three triggers of your own.
First, however, look at the table below to see a summary of the combinations of action, timing, and scope that can be used when creating table triggers.
There are potentially 12 different triggers that can be set up for any database table.

Action Timing Scope Syntax example
Update Before Once per row CREATE TRIGGER PRE_UPDATE
BEFORE UPDATE ON CUSTOMER FOR EACH ROW...
Insert Before Once per row CREATE TRIGGER PRE_INSERT
BEFORE INSERT ON CUSTOMER FOR EACH ROW...
Delete Before Once per row CREATE TRIGGER PRE_DELETE
BEFORE DELETE ON CUSTOMER FOR EACH ROW...
Update After Once per row CREATE TRIGGER POST_UPDATE
BEFORE UPDATE ON CUSTOMER FOR EACH ROW...
Insert After Once per row CREATE TRIGGER POST_INSERT
BEFORE INSERT ON CUSTOMER FOR EACH ROW...
Delete After Once per row CREATE TRIGGER POST_DELETE
BEFORE DELETE ON CUSTOMER FOR EACH ROW...
Update Before Once per transaction CREATE TRIGGER PRE_UPDATE
BEFORE UPDATE ON CUSTOMER...
Insert Before Once per transaction CREATE TRIGGER PRE_INSERT
BEFORE INSERT ON CUSTOMER...
Delete Before Once per transaction CREATE TRIGGER PRE_DELETE
BEFORE DELETE ON CUSTOMER...
Update After Once per transaction CREATE TRIGGER POST_UPD_TRANS
BEFORE UPDATE ON CUSTOMER...
Insert After Once per transaction CREATE TRIGGER POST_INS_TRANS
BEFORE INSERT ON CUSTOMER...
Delete After Once per transaction CREATE TRIGGER POST_DEL_TRANS
BEFORE DELETE ON CUSTOMER...

The most commonly used triggers are the first three listed in the table below.
Follow along in the simulation to see how to create a trigger that can cause an update to fail. This trigger's purpose is to compare the current sale price of a product to the current cost of the product. If the sale price is lower than the cost, the trigger fails which in turn causes the update to fail. This prevents a user from setting a money-losing price for any product.
Create pre Update Trigger
The next lesson shows you how to combine multiple triggers into a single trigger.

Create Three Triggers - Exercise

Click the Exercise link below to check your knowledge about triggers.
Create Three Triggers - Exercise