insert data into an audit table

[wizardofsilence]

I need to enter data into an audit table using a trigger.. I created the following audit table and trigger... trigger dsnt give the expected answer...

Q - if a customer gives a 0 evaluation, the details of the attendance must be placed in an audit table..
-- cusarrive is the table which has the evaluation attribute

CREATE TABLE audit_cus(
cusname varchar2(30),
concertname varchar2(30),
evdate DATE,
venuename varchar(50),
evaluation number(1)
)

select * from audit_cus;

CREATE OR REPLACE TRIGGER QUES2TRIG
BEFORE INSERT OR UPDATE OF cusevaluation ON cusarrive
FOR EACH ROW

DECLARE
cuse NUMBER(1);

BEGIN
select cusevaluation INTO cuse from cusarrive;
IF :OLD.cusevaluation = 0 THEN
INSERT INTO audit_cus (cusname,concertname,evdate,venuename,evaluation) (
SELECT C.CUSNAME, S.CONCERTNAME, E.EVDATE, V.VENUENAME, D.CUSEVALUATION
FROM customer C JOIN cusarrive D ON C.customerid = D.customerid
JOIN event E ON D.eventid = E.eventid
JOIN venue V ON V.venueid = E.venueid
JOIN concert S ON S.concertid = E.concertid );
END IF;
END;
/

thanks for the help in advance.

GKD...

 

[SantaMufasa]

Q - if a customer gives a 0 evaluation, the details of the attendance must be placed in an audit table...

I presume, Wizard, that the way your code appears now, it is not checking the new value of "cusevaluation":

...IF :OLD.cusevaluation = 0 THEN...

So, I guarantee that nothing will happen on an INSERT since ":OLD.cusevaluation" will be null, and for an UPDATE, you will be checking the previous, not the ":new." value of cusevaluation.

Could this be why you are not receiving the results you hoped for?


Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[wizardofsilence]

@SantaMufasa

yeah.. i think there must be the problem as if i run the insert statement it will execute and enter data.. i just want to check data (previously inserted and inserting in to the event table).
if u can help t ll be great..

thanks alt

 

[SantaMufasa]

Have you tried using the ":new." data buffer that appears in your modified code, below:

CREATE OR REPLACE TRIGGER QUES2TRIG 
BEFORE INSERT OR UPDATE OF cusevaluation ON cusarrive
FOR EACH ROW

DECLARE
cuse NUMBER(1);

BEGIN
select cusevaluation INTO cuse from cusarrive;
IF :new.cusevaluation = 0 THEN
INSERT INTO audit_cus (cusname,concertname,evdate,venuename,evaluation) (
SELECT C.CUSNAME, S.CONCERTNAME, E.EVDATE, V.VENUENAME, D.CUSEVALUATION 
FROM customer C JOIN cusarrive D ON C.customerid = D.customerid
JOIN event E ON D.eventid = E.eventid 
JOIN venue V ON V.venueid = E.venueid
JOIN concert S ON S.concertid = E.concertid );
END IF;
END;
/

Let us know how this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[wizardofsilence]

@SantaMufasa

Yeah I used it and tried... Then also data is not inserted into audit table which I have created previously..
and I can't insert data to CusArrive table it throws the following error..

"
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "TEST.QUES2TRIG", line 5
ORA-04088: error during execution of trigger 'TEST.QUES2TRIG' "

 

[SantaMufasa]

Wizard,

The code that is causing your current error comes from:

select cusevaluation INTO cuse from cusarrive;

Since you have no where clause on your SELECT statement, if there is more than one row in cusarrive, you will receive this error. In PL/SQL, it is the programmer's responsibility to ensure code that will always return exactly one row (never 0 rows and never 2 or more rows, else you will receive a run-time error as you have in this case).

Fix with a WHERE clause, then post again if you have another error you cannot figure out.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[wizardofsilence]

@SantaMufasa


i tried that too..

CREATE OR REPLACE TRIGGER QUES2TRIG
BEFORE INSERT OR UPDATE OF cusevaluation ON cusarrive
FOR EACH ROW

DECLARE
cuse NUMBER(1);

BEGIN
select cusevaluation INTO cuse from cusarrive where cusevaluation = 0;
IF :new.cusevaluation = 0 THEN
INSERT INTO audit_cus (cusname,concertname,evdate,venuename,evaluation) (
SELECT C.CUSNAME, S.CONCERTNAME, E.EVDATE, V.VENUENAME, D.CUSEVALUATION
FROM customer C JOIN cusarrive D ON C.customerid = D.customerid
JOIN event E ON D.eventid = E.eventid
JOIN venue V ON V.venueid = E.venueid
JOIN concert S ON S.concertid = E.concertid );
END IF;
END;
/


trigger executes and when I execute below code..

select * from audit_cus;

I get 'no data found'

I just want the data which have 0 for cusevaluation.

thanks alot for your help... really appreciate it..

 

[Beilstwh]

Your trigger is on the table cusarrive and you are querying the same table in the trigger. This is NOT allowed. Try it like this

CREATE OR REPLACE TRIGGER QUES2TRIG
BEFORE INSERT OR UPDATE OF cusevaluation ON cusarrive
FOR EACH ROW
BEGIN
IF :new.cusevaluation = 0 THEN
INSERT INTO audit_cus (cusname,concertname,evdate,venuename,evaluation)
SELECT C.CUSNAME, S.CONCERTNAME, E.EVDATE, V.VENUENAME, :new.CUSEVALUATION
FROM customer C,event E,venue V,concert S
where C.customerid = :new.customerid
and   :new.eventid = E.eventid
and  V.venueid = E.venueid
and S.concertid = E.concertid;
END IF;
END;
/

Bill
Lead Application Developer
New York State, USA

 

[wizardofsilence]

@Beilstwh

Finally it worked.. But I wonder whether there is a way to insert data to the audit_cus table which is already in cusarrive table.

Thanks alt for the help...

 

[Beilstwh]

Sure,
before you turn on the trigger simply run the following sql.

INSERT INTO audit_cus (cusname,concertname,evdate,venuename,evaluation)
SELECT C.CUSNAME, S.CONCERTNAME, E.EVDATE, V.VENUENAME, D.CUSEVALUATION
FROM customer C JOIN cusarrive D ON C.customerid = D.customerid
JOIN event E ON D.eventid = E.eventid
JOIN venue V ON V.venueid = E.venueid
JOIN concert S ON S.concertid = E.concertid
where d.cusevaluation = 0;


Bill
Lead Application Developer
New York State, USA