Creating a dependent audit trigger

Question

I have a set of tables in a database. Their syntax are:

CREATE TABLE Concert
(concert_id NUMBER (3) NOT NULL,
 concert_name VARCHAR (14) NOT NULL,
 concert_duration NUMBER (1) NOT NULL,
 concert_type VARCHAR (10) NOT NULL,
 artist VARCHAR (14) NOT NULL,
 concert_cost NUMBER (3) NOT NULL
);

CREATE TABLE Customer
(customer_id NUMBER (4) NOT NULL,
 firstname VARCHAR (14) NOT NULL,
 lastname VARCHAR (14) NOT NULL,
 gender VARCHAR (1) NOT NULL,
 street VARCHAR (14),
 city VARCHAR (14),
 country VARCHAR (14),
 postcode VARCHAR (8) NOT NULL
);

CREATE TABLE Event
(event_id NUMBER (3) NOT NULL,
 event_date DATE NOT NULL,
 venue_id NUMBER (2) NOT NULL,
 concert_id NUMBER (3) NOT NULL
);

CREATE TABLE Venue
(venue_id NUMBER (2) NOT NULL,
 venue_name VARCHAR (14) NOT NULL,
 max_capacity NUMBER (5) NOT NULL,
 street VARCHAR (14),
 city VARCHAR (14),
 country VARCHAR (14),
 postcode VARCHAR (8) NOT NULL
);

CREATE TABLE Booking
(arrival_time VARCHAR (8) default to_char(localtimestamp,'HH:MI') check(arrival_time like '__:___AM' or arrival_time like '__:___PM'),
 vehicle_no VARCHAR (8),
 evaluation NUMBER (1) NOT NULL CONSTRAINT ck_evaluation CHECK (evaluation BETWEEN 0 AND 5),
 event_id NUMBER (3) NOT NULL,
 customer_id NUMBER (4) NOT NULL
);

Concert_id and venue_id are foreign keys in the event table. Event_id and customer_id are also foreign keys in the booking table. I want to create a trigger that will place the firstname and lastname (from customer table), concert_name (from concert table), event_date (from event table), venue_name (from venue table) and evaluation (from booking table) into and audit table if a customer gives a zero evaluation. I have created the audit table as:

CREATE TABLE audit_evaluation
(firstname VARCHAR (14),
 lastname VARCHAR (14),
 concert_name VARCHAR (14),
 event_date DATE,
 venue_name (14),
 evaluation NUMBER (1)
);

I tried to create the trigger with this syntax but it gave an error:

CREATE OR REPLACE TRIGGER trg_evaluation
  AFTER INSERT ON booking
    FOR EACH ROW
BEGIN
  IF (evaluation = 0)
  THEN
    INSERT INTO audit_evaluation (firstname, lastname, concert_name, event_date, venue_name, evaluation)
      VALUES (customer.firstname, customer.lastname, concert.concert_name, event.event_date, venue.venue_name, booking.evaluation);
  END IF;
END;

Please help!!!

Justin just explained, on the other question you posted, about the use of :new.* vs. :old.*. Please don't write a trigger before you understand this concept. — Nir Alfasi
Do you understand what a trigger does and how it works? It can't magically reference specific rows in a table that it's not touching. Why are you wanting a trigger to do this rather than anything else? — Ben

AnBisw AnBisw · Accepted Answer · 2012-08-18T14:15:50

CREATE OR REPLACE TRIGGER trg_evaluation
  AFTER INSERT ON booking
    FOR EACH ROW
BEGIN
  IF (evaluation = 0)
  THEN
    INSERT INTO audit_evaluation (firstname, lastname, concert_name, event_date, venue_name, evaluation)
      VALUES (customer.firstname, customer.lastname, concert.concert_name, event.event_date, venue.venue_name, booking.evaluation);
  END IF;
END;

First of all there is no :old or :new reference here so the trigger doesn't know what row to pick from the other 4 tables. You should learn how to use them here. Once you are ready to write a trigger, your insert statement "could" look something like below (note the usage of :new)-

INSERT INTO audit_evaluation (firstname, lastname, concert_name, event_date, venue_name, evaluation)
select c.firstname,c.lastname,con.concert_name,
       evt.event_date, ven.venue_name, :new.evaluation
  from customer c, concert con, event evt, venue ven
 where evt.evt_id = :new.event_id
   and c.customer_id = :new.customer_id
   and con.customer_id=c.customer_id
   and con.concert_id = evt.concert_id
   and evt.venue_id=ven.venue_id;

Note- Don't get discouraged by some "not-so-freindly" comments here. People are just trying to "Help". :)

Creating a dependent audit trigger

1 Answers