Triggers are a special PL/SQL construct similar to procedures. However, a procedure is executed explicitly from another block via a procedure call, while a trigger is executed implicitly whenever the triggering event happens. The triggering event is either a INSERT, DELETE, or UPDATE command. The timing can be either BEFORE or AFTER. The trigger can be either row-level or statement-level, where the former fires once for each row affected by the triggering statement and the latter fires once for the whole statement.
CREATE TABLE chicken (cID INT PRIMARY KEY, eID INT REFERENCES egg(eID)); CREATE TABLE egg(eID INT PRIMARY KEY, cID INT REFERENCES chicken(cID));But if we simply type the above statements into Oracle, we'll get an error. The reason is that the CREATE TABLE statement for chicken refers to table egg, which hasn't been created yet! Creating egg won't help either, because egg refers to chicken.
To work around this problem, we need SQL schema modification commands. First, create chicken and egg without foreign key declarations:
CREATE TABLE chicken(cID INT PRIMARY KEY, eID INT); CREATE TABLE egg(eID INT PRIMARY KEY, cID INT);Then, we add foreign key constraints:
ALTER TABLE chicken ADD CONSTRAINT chickenREFegg FOREIGN KEY (eID) REFERENCES egg(eID) INITIALLY DEFERRED DEFERRABLE; ALTER TABLE egg ADD CONSTRAINT eggREFchicken FOREIGN KEY (cID) REFERENCES chicken(cID) INITIALLY DEFERRED DEFERRABLE;INITIALLY DEFERRED DEFERRABLE tells Oracle to do deferred constraint checking. For example, to insert (1, 2) into chicken and (2, 1) into egg, we use:
INSERT INTO chicken VALUES(1, 2); INSERT INTO egg VALUES(2, 1); COMMIT;Because we've declared the foreign key constraints as "deferred", they are only checked at the commit point. (Without deferred constraint checking, we cannot insert anything into chicken and egg, because the first INSERT would always be a constraint violation.)
Finally, to get rid of the tables, we have to drop the constraints first, because Oracle won't allow us to drop a table that's referenced by another table.
ALTER TABLE egg DROP CONSTRAINT eggREFchicken; ALTER TABLE chicken DROP CONSTRAINT chickenREFegg; DROP TABLE egg; DROP TABLE chicken;
Some vendor specific error code numbers are 1 for primary key constraint violations, 2291 for foreign key violations, 2290 for attribute and tuple CHECK constraint violations. Oracle also provides simple error message strings that have a format similar to the following:
ORA-02290: check constraint (YFUNG.GR_GR) violated
or
ORA-02291: integrity constraint (HONDROUL.SYS_C0067174) violated - parent key not found
For more details on how to do error handling, please take a look at Pro*C Error handling or at the Retrieving Exceptions section of JDBC Error handling.
CREATE [OR REPLACE] TRIGGER <trigger_name>
{BEFORE|AFTER} {INSERT|DELETE|UPDATE} ON <table_name>
[REFERENCING [NEW AS <new_row_name>] [OLD AS <old_row_name>]]
[FOR EACH ROW [WHEN (<trigger_condition>)]]
<trigger_body>
Some important points to note:
... INSERT ON R ... ... INSERT OR DELETE OR UPDATE ON R ... ... UPDATE OF A, B OR INSERT ON R ...
CREATE TABLE T4 (a INTEGER, b CHAR(10)); CREATE TABLE T5 (c CHAR(10), d INTEGER);We create a trigger that may insert a tuple into T5 when a tuple is inserted into T4. Specifically, the trigger checks whether the new tuple has a first component 10 or less, and if so inserts the reverse tuple into T5:
CREATE TRIGGER trig1 AFTER INSERT ON T4 REFERENCING NEW AS newRow FOR EACH ROW WHEN (newRow.a <= 10) BEGIN INSERT INTO T5 VALUES(:newRow.b, :newRow.a); END trig1; . run;Notice that we end the CREATE TRIGGER statement with a dot and run, as for all PL/SQL statements in general. Running the CREATE TRIGGER statement only creates the trigger; it does not execute the trigger. Only a triggering event, such as an insertion into T4 in this example, causes the trigger to execute.
Warning: Trigger created with compilation errors.you can see the error messages by typing
show errors trigger <trigger_name>;Alternatively, you can type, SHO ERR (short for SHOW ERRORS) to see the most recent compilation error. Note that the reported line numbers where the errors occur are not accurate.
select trigger_name from user_triggers;
For more details on a particular trigger:
select trigger_type, triggering_event, table_name, referencing_names, trigger_body from user_triggers where trigger_name = '<trigger_name>';
drop trigger <trigger_name>;
alter trigger <trigger_name> {disable|enable};
create table Person (age int);
CREATE TRIGGER PersonCheckAge
AFTER INSERT OR UPDATE OF age ON Person
FOR EACH ROW
BEGIN
IF (:new.age < 0) THEN
RAISE_APPLICATION_ERROR(-20000,
'no negative age allowed');
END IF;
END;
.
RUN;
If we attempted to execute the insertion:
insert into Person values (-3);
we would get the error message:
ERROR at line 1:
ORA-20000: no negative age allowed
ORA-06512: at "MYNAME.PERSONCHECKAGE", line 3
ORA-04088: error during execution of trigger 'MYNAME.PERSONCHECKAGE'
and nothing would be inserted. In general, the effects of both the trigger and the triggering statement are rolled back.