Triggers are defined objects that produce an automatic response to an event that occurs in the database.
A good example for what triggers are sometimes used for is trying to
keep two tables in sync (replication). Since the
UPDATE statement is a single table
statement, you can't update two different tables with one SQL
statement. So a trigger would be placed on one table, and when a value
changed in that table (event), the database would automatically update
the other table with the appropriate information (response).
Since triggers are an automatic response to an event occurring, there are a couple of basic things that you need to know when defining a trigger:
- What the event is you are waiting for
- What your response is going to be
- When you are going to process the response based upon the event occurring
- What you are going to name your trigger
- If you want to use the values of the event in your response
Listing 27. Example trigger
CREATE TRIGGER cust_ins_trig INSERT ON customer REFERENCING NEW AS new FOR EACH ROW WHEN (new.Birthday < '01/01/2000') (EXECUTE PROCEDURE ins_mailing_list(new.LName, new.Birthday) );
The example in Listing 27 would take the customer information that was being inserted into the customer table and pass two columns of it—the LName and Birthday columns—to a stored procedure that would use that information to do something, like insert them into a mailing list table. But the stored procedure would only be called if the value of Birthday was before 01/01/2000.
The answers to your five-point checklist above would be:
- You are waiting for an INSERT event on the customer table
- Your response is to run a stored procedure
- You are going to process this for each row that is inserted
- you are naming your trigger cust_ins_trig
- You are using the values using the
REFERENCING NEW AS newclause.
The example in Listing 27 uses two optional clauses, which are the
REFERENCING clause and the
The triggering event can be any one of the following:
- Insert of a row in a table
- Delete of a row in a table
- Update of a row in a table
- Update of a column in a table
- Select of a row in a table
- Select of a column in a table
The trigger response can be any one of the following:
- Insert into a table
- Delete from a table
- Update of a table or column
- Execution of a Stored Procedure or Function
The response can be run at three different times:
- For each row, as shown in the example in Listing 27
- Before the triggering event processes the SQL statement
- After the triggering event finishes processing the SQL statement
Listing 28. Another trigger example
CREATE TRIGGER cust_del_orders DELETE on customer REFERENCING OLD AS abc FOR EACH ROW ( DELETE FROM orders WHERE SSN = abc.SSN);
The example in Listing 28 deletes any orders from the orders table when
the associated customer, using SSN, is deleted from the customer
table. Here, the
REFERENCING AS clause uses
OLD AS syntax instead, because this is
data that is being removed. The reference correlation value that you
give is just a variable and can be anything. This example uses
Note: When a triggering event happens because of an SQL statement that is run, the trigger response is considered part of the SQL statement. This means that if the trigger response fails, then both the response and the initial trigger event will roll back.
The "Views" section talked about the restrictions placed on modifying data through a view. For example, you cannot insert a new row into a multi-table view. This is where INSTEAD OF triggers come in.
INSTEAD OF triggers are triggers that are defined on a view. Remember that two of the most important pieces of a trigger are the triggering event and the trigger response. It takes the triggering event to happen from the user before the triggering response is invoked.
If you create a multi-table view and then try to insert into that view, you would immediately get back an error. unless you create an INSTEAD OF trigger on the view that said "instead of" doing the event (insert into view), just perform the response of the trigger.
Listing 29. Example INSTEAD OF trigger
CREATE TABLE customer ( cust_num int, Name char(50), phone char(12)); CREATE TABLE orders (order_num serial, cust_num int, order_total money); CREATE VIEW join_view AS SELECT Name, order_total FROM customer, orders WHERE customer.cust_num = orders.cust_num; CREATE TRIGGER instdof_trig INSTEAD OF INSERT ON join_view REFERENCING NEW AS new FOR EACH ROW ( INSERT INTO customer VALUES (25, new.Name, "555-1212"), INSERT INTO orders VALUES (0, 25, new.order_total)); INSERT INTO join_view VALUES ("Fred", 25.68);
INSERT statement in Listing 29 will not
get back and error because it is not actually inserting a row into the
view (which is a no-no on a multi-table view). Instead, it is issuing
two single table
INSERT statements. The
example needs a little work, as it contains have hard-coded values for
some of the columns you wouldn't want hard-coded values in, but it
gives you the idea of what INSTEAD OF triggers are trying to