Constraints are declaractions of conditions about the database that must remain true.
These include attributed-based, tuple-based, key, and referential integrity constraints.
Plus there is an added advantage: it allows sharing of common state between all the trigger-points using variable.
This is because compound trigger in oracle 11g has a declarative section where one can declare variable to be used within trigger.
ALTER TABLE chicken ADD CONSTRAINT chicken REFegg FOREIGN KEY (e ID) REFERENCES egg(e ID) INITIALLY DEFERRED DEFERRABLE; ALTER TABLE egg ADD CONSTRAINT egg REFchicken FOREIGN KEY (c ID) REFERENCES chicken(c ID) INITIALLY DEFERRED DEFERRABLE; In general, Oracle returns an error message when a constraint is violated.
Specifically for users of JDBC, this means an SQLException gets thrown, whereas for Pro*C users the SQLCA struct gets updated to reflect the error.
The compound trigger is useful when you want to accumulate facts that characterize the “for each row” changes and then act on them as a body at “after statement” time.
Two popular reasons to use compound trigger are: CREATE OR REPLACE TRIGGER compound_trigger_name FOR [INSERT|DELETE]UPDATE [OF column] ON table COMPOUND TRIGGER -- Declarative Section (optional) -- Variables declared here have firing-statement duration.
The WHEN clause or body of the trigger can check for the violation of certain conditions and signal an error accordingly using the Oracle built-in function RAISE_APPLICATION_ERROR.
The action that activated the trigger (insert, update, or delete) would be aborted.
This common state is established at the start of triggering statement and is destroyed after completion of trigger (regardless of trigger being in error or not).