Ingres 11.2 | BEFORE Rule Example: The Audit Procedure and Rule

SQL Reference Guide > SQL Reference Guide > Understanding Database Procedures, Sessions, and Events > Examples: Database Procedures and Rules > BEFORE Rule Example: The Audit Procedure and Rule

Was this helpful?

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.”

Last modified date: 08/28/2024