Ingres 11.0 | AFTER Rule Example: The Audit Procedure and Rule

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

Was this helpful?

AFTER Rule Example: The Audit Procedure and Rule

In this example, we want to audit the airports that are added to the Ingres Frequent Flyer database. To accomplish this, we will:

• Add a new table to store the new airports added to the database.

• Create a stored procedure to record the changes in the new table.

• Create a rule (trigger) to invoke the procedure when a new airport is added to the airport table.

The airport table definition is as follows:

CREATE TABLE airport (

ap_id INT,

ap_iatacode NCHAR(3) NOT NULL,

ap_place NVARCHAR(30),

ap_name NVARCHAR(50)

ap_ccode NCHAR(2)

)

Create the Audit Table

The following SQL creates the airport_added table, which stores the new airports:

CREATE TABLE airport_added (
   ap_iatacode       NCHAR(3) NOT NULL,
   ap_name NVARCHAR(50),
   ap_ccode NCHAR(2) )

Create the Audit Procedure

The following SQL creates the audit procedure, airport_added_dbp, which updates the new airport_added table with the new airports, identified by the IATA code, airport name, and airport code:

CREATE PROCEDURE airport_added_dbp (
          iatacode      NCHAR(3) NOT NULL NOT DEFAULT,
          airport_name NCHAR(3) NOT NULL NOT DEFAULT,
          airport_code  NCHAR(3) NOT NULL NOT DEFAULT
      )
      AS
      BEGIN
            INSERT INTO airport_added (
                  ap_iatacode,
                  ap_name,
                  ap_ccode
            )
          VALUES (
                  :iatacode,
                  :airport_name,
                  :airport_code
          );
      END

Create the Audit Rule

The following SQL creates the audit rule (trigger), airport_added_trg, which executes the new procedure whenever a row is added to the airport table:

CREATE RULE airport_added_trg
       AFTER INSERT INTO airport
   EXECUTE PROCEDURE airport_added_dbp (
      iatacode      = new.ap_iatacode,
      airport_name = new.ap_name,
      airport_code   = new.ap_ccode)

Test the Audit Rule

The rule is tested by inserting a new row into the airport table, and then viewing the contents of the airport_added table.

1. The following SQL inserts a new row into the airport table:

INSERT INTO airport VALUES (10000, 'TGL', 'Berlin', 'Tegel', 'DE')

2. Select from the airport_added table to check that the rule fired:

SELECT * FROM airport_added

Last modified date: 01/30/2023