Introduction to Triggers:
Triggers are named PL/SQL blocks. In that way they are similar to procedures, however
procedures are involved with an explicit call and a passing of arguments while a trigger
event causes the implicit execution of the trigger code and arguments are not involved.
A trigger event is associated with DML such as INSERT, UPDATE or DELETE and when it is
executed it is referred to as the "firing" of the trigger. What this means is that I can
use a trigger so that if I update a record a trigger event occurs that records the update
or does some other kind of processing. When defining the trigger, I can choose to have
the trigger fire either BEFORE or AFTER the insert, update or delete. Note: you can use
the code AFTER INSERT OR DELETE OR UPDATE ON or you can choose to only do it for one
thing such as AFTER INSERT ON . In addition I can choose to have an optional row level
trigger which fires when a row is effected using the clause FOR EACH ROW or an optional
event driven trigger that fires because of the event or statement execution. This is
handled with a WHEN followed by the trigger condition clause. Note: a trigger cannot
include statements like COMMIT, ROLLBACK or SAVEPOINT. Read more about triggers in your
text or in the locations on the Web.
In the example below, I created a new table called total_notes. Whenever I update the
new_donation_one table I want it to trigger processing that will write a record in
total_notes telling the hard coded note (I could have uses the system date or had the
date entered - getting lazy!) and the current sum of the contributions.
SQL CODE:
SQL> create table total_notes
2 (changedate DATE, totamt NUMBER(6,2));
Table created.
SQL> DESC total_notes
Name Null? Type
------------------------------- -------- ----
CHANGEDATE DATE
TOTAMT NUMBER(6,2)
The trigger code is shown below. The name of the trigger is trigger1 and I activated it
based on the UPDATE of new_donation_one. Note that I could have used AFTER INSERT OR
DELETE OR UPDATE ON... if the program did all of these things, my program only does an
update. I hardcoded in the date of July 27, 1999 and used SUM to sum the contamt in the
new_donation_one table and store it in the variable v_totamt. Then I inserted the hard
coded date and the result of the sum as a row in the new table I created, total_notes.
Then I put in the command END trigger1;. Remember that I have to create the trigger
before it will be used so the next SQL code is @ trigger1.
SQL CODE:
SQL> edit trigger1
PL/SQL CODE:
CREATE OR REPLACE TRIGGER trigger1
AFTER UPDATE ON new_donation_one
DECLARE
v_date total_notes.changedate%TYPE :='27-JUL-99';
v_totamt total_notes.totamt%TYPE;
BEGIN
SELECT SUM(contamt) INTO v_totamt
FROM new_donation_one;
INSERT INTO total_notes (changedate, totamt)
VALUES(v_date, v_totamt);
END trigger1;
/
SQL CODE:
SQL> @ trigger1
Input truncated to 1 characters
Trigger created.
SQL> edit use_trigger1
The use_trigger code does nothing special, it simply updates the contamt in the
new_donation_one table for the record where the idno and the driveno are equal to the
user input. There is no reference to the trigger. The trigger when it was created was
associated with the updating of the new_donation_one table and since this code updates
that table, it causes the trigger to be fired.
PL/SQL CODE:
DECLARE
v_idno new_donation_one.idno%TYPE :='&input_idno';
v_driveno new_donation_one.driveno%TYPE :='&input_driveno';
v_contamt new_donation_one.contamt%TYPE :=&input_contamt;
BEGIN
UPDATE new_donation_one
SET contamt = v_contamt
WHERE idno = v_idno AND driveno = v_driveno;
END;
/
The SQL below executes use_trigger1 which takes the user input and updates the table. The
trigger is fired and a row/record is written in the total_notes table. The contents of
both tables are shown below. Note that I executed use_trigger1, two times.
SQL CODE:
SQL> @ use_trigger1
Input truncated to 1 characters
Enter value for input_idno: 33333
old 2: v_idno new_donation_one.idno%TYPE :='&input_idno';
new 2: v_idno new_donation_one.idno%TYPE :='33333';
Enter value for input_driveno: 300
old 3: v_driveno new_donation_one.driveno%TYPE :='&input_driveno';
new 3: v_driveno new_donation_one.driveno%TYPE :='300';
Enter value for input_contamt: 30
old 4: v_contamt new_donation_one.contamt%TYPE :=&input_contamt;
new 4: v_contamt new_donation_one.contamt%TYPE :=30;
PL/SQL procedure successfully completed.
SQL> SELECT * FROM new_donation_one;
IDNO DRI CONTDATE CONTAMT
----- --- --------- ---------
11111 100 07-JAN-99 35
33333 300 10-MAR-99 30
22222 100 14-MAR-99 10
23456 300 14-JUN-99 10
12121 300 10-JUN-99 75
SQL> SELECT * FROM total_notes;
CHANGEDAT TOTAMT
--------- ---------
27-JUL-99 160
SQL> @ use_trigger1
Input truncated to 1 characters
Enter value for input_idno: 23456
old 2: v_idno new_donation_one.idno%TYPE :='&input_idno';
new 2: v_idno new_donation_one.idno%TYPE :='23456';
Enter value for input_driveno: 300
old 3: v_driveno new_donation_one.driveno%TYPE :='&input_driveno';
new 3: v_driveno new_donation_one.driveno%TYPE :='300';
Enter value for input_contamt: 45
old 4: v_contamt new_donation_one.contamt%TYPE :=&input_contamt;
new 4: v_contamt new_donation_one.contamt%TYPE :=45;
PL/SQL procedure successfully completed.
SQL> SELECT * FROM new_donation_one;
IDNO DRI CONTDATE CONTAMT
----- --- --------- ---------
11111 100 07-JAN-99 35
33333 300 10-MAR-99 30
22222 100 14-MAR-99 10
23456 300 14-JUN-99 45
12121 300 10-JUN-99 75
SQL> SELECT * FROM total_notes;
CHANGEDAT TOTAMT
--------- ---------
27-JUL-99 160
27-JUL-99 195