Describe the syntax and use of special record variables.
Special Variables available in Triggers
This lesson covers the basic syntax of a trigger and describes some special variables that can only be used in triggers.
Look at the diagram below for an example of the code and the values when using these variables. The MouseOver shows you the CREATE TRIGGER command for a trigger on the PRODUCT table. The next line shows you the current values in one row of the PRODUCT table. Finally, an UPDATE command is used to illustrate the new values that are going to be updated into that same row in the PRODUCT table. Place your mouse over the variables in the trigger code to view the value that each one contains when the trigger is fired for the update.
This includes an example of a PL/SQL trigger and annotations about its syntax.
CREATE OR REPLACE TRIGGER PRE_CUSTOMER
BEFORE
INSERT OR UPDATE ON CUSTOMER
FOR EACH ROW
BEGIN
... pl/sql statements
END;
Annotations:
Set the timing of the trigger It is fired either before or after an action.
Name the trigger.
List the action or actions that cause the trigger to fire.
Assign the trigger to a table.
Include this line to cause the trigger to fire once per row. Leave it out to cause the trigger to fire once per transaction.
Syntax Explanation:
CREATE [OR REPLACE] TRIGGER [user.]triggername
BEFORE | AFTER
INSERT | [OR] UPDATE | [OR] DELETE ON [user.]tablename
[FOR EACH ROW]
PL/SQL block
This summarizes the PL/SQL code and descriptive text in the provided image. Let me know if you'd like further explanation or formatting.
Syntax and example of a trigger
Special variables
NULL
4.95
4.50
4.50
3.50
Today's date and time
Old sale price = 6.50
Old store cost = 3.50
New store cost = 4.50
New sale price = NULL
CREATE OR REPLACE TRIGGER PRE_PRODUCT
BEFORE UPDATE ON PRODUCT
FOR EACH ROW
BEGIN
IF :NEW.SALE_PRICE IS NULL THEN
:NEW.SALE_PRICE := :NEW.STORE_COST * 1.10
END IF;
IF :NEW.STORE_COST <> :OLD.STORE_COST THEN
:NEW.LAST_UPDATE_DATE := SYSDATE;
END IF;
END;
Old and new column variables in PL/SQL
CREATE OR REPLACE TRIGGER PRE_PRODUCT
BEFORE UPDATE ON PRODUCT
FOR EACH ROW
BEGIN
IF :NEW.SALE_PRICE IS NULL THEN
:NEW.SALE_PRICE := :NEW.STORE_COST * 1.10
END IF;
IF :NEW.STORE_COST <> :OLD.STORE_COST THEN
:NEW.LAST_UPDATE_DATE := SYSDATE;
END IF;
END;
Location 1
NULL
Location 2
4.95
Location 3
4.50
Location 4
4.50
Location 5
3.50
Location 6
Today's date and time
Location 7
Old sale price=6.50
Location 8
Old store cost=3.50
Location 9
New store cost=4.50
Location 10
New sale price=NULL
Creating a DML Trigger
To create (or replace) a DML trigger, use the syntax shown here:
1 CREATE [OR REPLACE] TRIGGER trigger_name
2 {BEFORE | AFTER}
3 {INSERT | DELETE | UPDATE | UPDATE OF column_list } ON table_name
4 [FOR EACH ROW]
5 [WHEN (...)]
6 [DECLARE ... ]
7 BEGIN
8 ...executable statements...
9 [EXCEPTION ... ]
10 END [trigger_name];
The following table provides an explanation of these different elements:
Line
Description
1
States that a trigger is to be created with the name supplied. Specifying OR REPLACE is optional. If the trigger exists and REPLACE is not specified, then your attempt to create the trigger anew will result in an ORA-4081 error. It is possible, by the way, for a table and a trigger (or a procedure and a trigger, for that matter) to have the same name.I recommend, however, that you adopt naming conventions to avoid the confusion that will result from this sharing of names.
2
Specifies if the trigger is to fire BEFORE or AFTER the statement or row is processed.
3
Specifies the combination of DML types to which the trigger applies: insert, update, or delete. Note that UPDATE can be specified for the whole record or just for a column list separated by commas. The columns can be combined (separated with an OR) and may be specified in any order. Line 3 also specifies the table to which the trigger is to apply. Remember that each DML trigger can apply to only one table.
4
If FOR EACH ROW is specified, then the trigger will activate for each row processed by a statement. If this clause is missing, the default behavior is to fire only once for the statement (a statement-level trigger).
5
An optional WHEN clause that allows you to specify logic to avoid unnecessary execution of the trigger.
6
Optional declaration section for the anonymous block that constitutes the trigger code. If you do not need to declare local variables, you do not need this keyword. Note that you should never try to declare the NEW and OLD pseudorecords.This is done automatically.
7-8
The execution section of the trigger. This is required and must contain at least one statement.
9
Optional exception section. This section will trap and handle (or attempt to handle) any exceptions raised in the execution section only.
10
Required END statement for the trigger. You can include the name of the trigger after the END keyword to explicitly document which trigger you are ending.
