Create trigger which updates a separate "audit table" automatically after update of a table

Question

My tables are

Book

CREATE TABLE Book 
(
    Bk_id CHAR(06) NOT NULL,
    BK_Name VARCHAR(60) NOT NULL,
    Author VARCHAR(30) NOT NULL,
    Price NUMERIC(03) NOT NULL,
    No_of_copies NUMERIC(02) NOT NULL,
    CONSTRAINT Book_PK PRIMARY KEY (Bk_id) 
);

Location

CREATE TABLE Location 
(
    Loc_id CHAR(06) NOT NULL,
    Loc_Name VARCHAR(15) NOT NULL,
    Stock NUMERIC(02) NOT NULL,
    CONSTRAINT Location_PK PRIMARY KEY (Loc_id)
);

Customer

CREATE TABLE Customer 
(
    Cus_id CHAR(06) NOT NULL, 
    Cus_Name VARCHAR(25) NOT NULL,  
    Gender VARCHAR(06) NOT NULL, 
    TP CHAR(12) NOT NULL,
    Address VARCHAR(40) NOT NULL, 
    CONSTRAINT Customer_PK PRIMARY KEY (Cus_id)
);

Copy

CREATE TABLE Copy 
(
    Copy_id CHAR(06) NOT NULL,
    Bk_id CHAR(06) NOT NULL,
    Loc_id CHAR(06) NOT NULL,
    Opinion CHAR(02) NOT NULL,
    CONSTRAINT pk_Copy PRIMARY KEY (Copy_id),
    CONSTRAINT fk_Copy_Bk_id_FK FOREIGN KEY (Bk_id) REFERENCES Book(Bk_id),
    CONSTRAINT fk_Copy_Loc_id_FK FOREIGN KEY (Loc_id) REFERENCES Location(Loc_id)
);

Borrow

CREATE TABLE Borrow 
(
    Cus_evo NUMERIC(02) NOT NULL,
    B_Date DATE NOT NULL,
    R_Date DATE NOT NULL,
    Fee NUMERIC(03) NOT NULL,
    Copy_id CHAR(06) NOT NULL,
    Cus_id CHAR(06) NOT NULL,
    CONSTRAINT pk_Borrow PRIMARY KEY (Cus_id,Copy_id),
    CONSTRAINT fk_Borrow_Copy_id_FK FOREIGN KEY (Copy_id) REFERENCES Copy(Copy_id),
    CONSTRAINT fk_Borrow_Cus_id_FK FOREIGN KEY (Cus_id) REFERENCES Customer(Cus_id)
);

Audit_Table

Create table Audit_Table
(
    Cus_Name VARCHAR(25) NOT NULL,
    BK_Name VARCHAR(60) NOT NULL,
    B_Date DATE NOT NULL,
    Loc_Name VARCHAR(15) NOT NULL,
    Cus_evo NUMERIC(02) NOT NULL
);

If a customer gives a zero evaluationCus_evo=0, the details of their Borrowing (Cus_Name from CUSTOMER, the BK_Name from Book, B_Date from Borrow, Loc_Name of the copy from Location and Cus_evo from Borrow) must be placed in an audit table.

The trigger that I created:

CREATE OR REPLACE TRIGGER "AUDIT_TRIGGER"
BEFORE
INSERT OR UPDATE ON Borrow
FOR EACH ROW
    WHEN (new.Cus_evo = 0)
    BEGIN
        INSERT INTO Audit_Table
        VALUES (:OLD.Cus_Name, :OLD.BK_Name, :OLD.B_Date, :OLD.Loc_Name, :OLD.Cus_evo);
    END;
/

I get this error:

Errors: TRIGGER AUDIT_TRIGGER
Line/Col: 3/9 PLS-00049: bad bind variable 'OLD.CUS_NAME'
Line/Col: 3/24 PLS-00049: bad bind variable 'OLD.BK_NAME'
Line/Col: 3/51 PLS-00049: bad bind variable 'OLD.LOC_NAME'

user_146325 · Answer

Those variables reference columns on other tables. So they are not available in the namespace of the borrow :OLD namespace. So what you need to do is write a query which looks up those tables using the foreign key values from the borrow record. Something like this:

CREATE OR REPLACE TRIGGER "AUDIT_TRIGGER"
BEFORE
INSERT OR UPDATE ON Borrow
FOR EACH ROW
    WHEN (new.Cus_evo = 0)
    BEGIN
        INSERT INTO Audit_Table
        select cus.Cus_Name, book.BK_Name, :NEW.B_Date, loc.Loc_Name, :NEW.Cus_evo
        from       customer cus
        cross join location loc
        cross join copy 
        cross join book
        where cus.cus_id   = :new.cus_id
        and   copy.copy_id = :new.copy_id
        and   book.bk_id   = copy.bk_id
        and   loc.loc_id   = copy.loc_id
    END;
/

Note that I suggest the :NEW namespace. That is the appropriate one for INSERTING actions, and also fits your use case when UPDATING.

Create trigger which updates a separate "audit table" automatically after update of a table

1 Answers