The "inserted" and "deleted" Tables
Triggers make use of a pair of special reserved tables named "inserted" and "deleted". The "inserted" table contains the data referenced in an INSERT before it is actually committed to the database. The "deleted" table contains the data in the table referenced in the DELETE statement before it is actually removed from the database.
Both tables are used when an UPDATE statement is issued. The new data referenced in the UPDATE statement is contained in "inserted" and the data that is being updated is placed in "deleted".
Basic Trigger Syntax:
CREATE OR REPLACE TRIGGER <TRIGGER_NAME>
This example records the user and the time whenever a user logs onto the database. It uses the following table:
The actual trigger code is as follows:
CREATE OR REPLACE TRIGGER trg_logon_db
This example is based on the following two tables:
This creates a trigger that may insert a tuple into table Table_5 when a tuple is inserted into table Table_4. Specifically, the trigger checks whether the new tuple has a first component 10 or less, and if so inserts the reverse tuple into Table_5:
CREATE TRIGGER trig_1
A statement level trigger with multiple actions:
One danger of using triggers is the condition known as "cascading". This occurs when the actions of one trigger cause another trigger to fire, which in turn causes the original trigger to fire again. When the original trigger is re-fired it causes the second trigger to fire again, and the cycle continues, sometimes indefinitely.
If not caught and handled properly in code this cascading condition (sometimes referred to as a trigger war) will nearly always cause problems (some of which may be fatal to the database). For example, storage space may become exhausted, endless streams of emails or alerts may be sent out, an order may never finish processing, or multiple orders may be recorded instead of one. Always test to make sure that the triggers you create do not cause a cascading effect. If necessary, add code flags that lock out repeating trigger actions where appropriate, for example, when a specific condition has been met.
Related Code Snippets: