Triggers can call stored procedures and cause a solidDB® server to execute other triggers. You can invoke procedures within a trigger body. You can also define a trigger body that contains only procedure calls. A procedure invoked from a trigger body can invoke other triggers.
When using stored procedures within the trigger body, you must first store the procedure with the CREATE PROCEDURE statement.
In a procedure definition, you can use COMMIT and ROLLBACK statements. However, in a trigger body, you cannot use COMMIT (including AUTOCOMMIT and COMMIT WORK) and ROLLBACK statements. You can use only the WHENEVER SQLERROR ABORT statement.
By default, you can nest triggers up to 16 levels deep. The limit can be changed using the SQL.MaxNestedTriggers parameter. If a trigger gets into an infinite loop, solidDB® detects this recursive action when the maximum nesting level is reached, and returns an error to the user. For example, you could activate a trigger by attempting to insert into the table T1 and the trigger could call a stored procedure which also attempts to insert into T1, activating the trigger recursively.
If a set of nested triggers fails at any time, solidDB® rolls back the statement which originally activated the triggers.
Setting default or derived columns
You can create triggers to set up default or derived column values in INSERT and UPDATE operations. When you create the trigger for this purpose using the CREATE TRIGGER command, the trigger must follow these rules:
▪The trigger must be executed BEFORE the INSERT or UPDATE operation. Column values are modified with only a BEFORE trigger. Because the column value must be set before the INSERT or UPDATE operation, using the AFTER trigger to set column values is meaningless. Note also that the DELETE operation does not apply to modifying column values.
▪For an INSERT and UPDATE operation, the REFERENCING clause must contain a NEW column value for modification. Note that modifying the OLD column value is meaningless.
▪New column values can be set by simply changing the values of variables defined in the referencing section.
Using parameters and variables with triggers
When you update a record and that update invokes a trigger, the trigger itself can change the value of some columns within that record. In some situations, you might want to refer to both the “old” value and the “new” value within the trigger.
The REFERENCING clause allows you to create “aliases” for old and new values so that you can refer to either one within the same trigger. For example, assume there are two tables, one that holds customer information and one that holds invoice information. In addition to storing the amount of money billed for each invoice, the table contains a “total_bought” field for each customer; this “total_bought” field contains the cumulative total for all invoices ever sent to this customer. (This field might be used to identify high-volume customers.)
Any time the total_amount on an invoice is updated, the “total_bought” value for that customer’s record in the customer table is also updated. To do this, the amount of the old value stored in the invoice is subtracted and the amount of the new value in the invoice is added. For example, if a customer’s invoice used to be for $100 and it is changed to $150, then $100 is subtracted and $150 is added to the “total_bought” field. By properly using the REFERENCING clause, the trigger can “see” both the old value and the price column, thereby allowing the update of the total_bought column.
The column aliases created by the REFERENCING clause are valid only within the trigger.
Example: Trigger with referencing clause
-- This SQL sample demonstrates how to use the clause -- "REFERENCING OLD AS old_col, REFERENCING NEW AS new_col" -- to have simultaneous access to both the "OLD" and "NEW" -- column values of the field while inside a trigger. -- In this scenario, we have customers and invoices. -- For each customer, we keep track of the cumulative total of -- all purchases by that customer. -- Each invoice stores the total amount of all purchases on -- that invoice. If an total price on an invoice must be -- adjusted, then the cumulative value of that customer's -- purchases must also be adjusted. -- Therefore, we update the cumulative total by subtracting -- the "old" price on the invoice and adding the "new" price. -- For example, if the amount on a customer's invoice was -- changed from $100 to $150 (an increase of $50), then we -- would update the customer's cumulative total by -- subtracting $100 and adding $150 (a net increase of $50). -- Drop the sample tables if they already exist. DROP TABLE customers; DROP TABLE invoices; CREATE TABLE customers ( customer_id INTEGER, -- ID for each customer total_bought FLOAT -- The cumulative total price of -- all this customer's purchases . ); -- Each customer may have 0 or more invoices. CREATE TABLE invoices ( customer_id INTEGER, invoice_id INTEGER, -- unique ID for each invoice invoice_total FLOAT -- total price for this invoice ); -- If the total_price on an invoice changes, then -- update customers.total_bought to take into account -- the change. Subtract the old invoice price and add the -- new invoice price. "CREATE TRIGGER old_and_new ON invoices AFTER UPDATE REFERENCING OLD invoice_total AS old_invoice_total, REFERENCING NEW invoice_total AS new_invoice_total, -- If the customer_id doesn't change, we could use -- either the NEW or OLD customer_id. REFERENCING NEW customer_id AS new_customer_id BEGIN EXEC SQL PREPARE upd_curs UPDATE customers SET total_bought = total_bought - ? + ? WHERE customers.customer_id = ?; EXEC SQL EXECUTE upd_curs USING (old_invoice_total, new_invoice_total, new_customer_id); EXEC SQL CLOSE upd_curs; EXEC SQL DROP upd_curs; END"; -- When a new invoice is created, we update the total_bought -- in the customers table. "CREATE TRIGGER update_total_bought ON invoices AFTER INSERT REFERENCING NEW invoice_total AS new_invoice_total, REFERENCING NEW customer_id AS new_customer_id BEGIN EXEC SQL PREPARE ins_curs UPDATE customers SET total_bought = total_bought + ? WHERE customers.customer_id = ?; EXEC SQL EXECUTE ins_curs USING (new_invoice_total, new_customer_id); EXEC SQL CLOSE ins_curs; EXEC SQL DROP ins_curs; END"; -- Insert a sample customer. INSERT INTO customers (customer_id, total_bought) VALUES (1000, 0.0); -- Insert invoices for a customer; the INSERT trigger will -- update the total_bought in the customers table. INSERT INTO invoices (customer_id, invoice_id, invoice_total) VALUES (1000, 5555, 234.00); INSERT INTO invoices (customer_id, invoice_id, invoice_total) VALUES (1000, 5789, 199.0); -- Make sure that the INSERT trigger worked. SELECT * FROM customers; -- Now update an invoice; the total_bought in the customers -- table will also be updated and the trigger that does -- this will use the REFERENCING clauses -- REFERENCING NEW invoice_total AS new_invoice_total, -- REFERENCING OLD invoice_total AS old_invoice_total UPDATE invoices SET invoice_total = 235.00 WHERE invoice_id = 5555; -- Make sure that the UPDATE trigger worked. SELECT * FROM customers; COMMIT WORK;