BEFORE Rule Example: The Audit Procedure and Rule
This example demonstrates the use of a BEFORE rule (trigger) using a similar audit procedure and rule as shown in the previous example.
The BEFORE rule fires before the base table is updated. Using a BEFORE rule lets you prevent unwanted changes to the table. For example you can prevent a new airport being added when air space in a country is filled to capacity.
Create the BEFORE Procedure
The following SQL creates the audit procedure, airport_before_add_dbp, which raises an error if the route is not available:
CREATE PROCEDURE airport_before_add_dbp (
airport_iata NCHAR(3) NOT NULL,
airport_ccode NCHAR(3) NOT NULL)
AS
DECLARE
airport_count INTEGER;
BEGIN
SELECT count(*) INTO :airport_count
FROM airport
WHERE ap_ccode = :airport_ccode;
IF (airport_count > 2)
THEN
RAISE ERROR 1 'Number of airports exceeded.';
ENDIF
END
Create the BEFORE Rule
The following SQL creates the audit rule airport_before_add_trg, which executes the audit procedure before a row is added to the airport table:
CREATE TRIGGER airport_before_add_trg
BEFORE INSERT INTO airport
EXECUTE PROCEDURE airport_before_add_dbp (
airport_iata = new.ap_iatacode,
airport_ccode = new.ap_ccode
)
Test the BEFORE Rule
The rule is tested by inserting a new row into the airport table, and then viewing the results.
INSERT INTO airport VALUES (10001, 'BIO', 'Bilbao', 'Aeropuerto de Bilbao', 'ES')
The results should show the error message “Number of airports exceeded.”