Insert, update, and delete Triggers(Create)

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