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