Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

insert data into an audit table

Status
Not open for further replies.

wizardofsilence

IS-IT--Management
Jul 29, 2012
12
LK
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...
 
Wizard said:
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":
Code:
...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?


[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.
 
@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
 
Have you tried using the ":new." data buffer that appears in your modified code, below:

Code:
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.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.
 
@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' "

[banghead]
 
Wizard,

The code that is causing your current error comes from:

Code:
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.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.
 
@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..
[bomb]
 
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

Code:
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
 
@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... [thumbsup2]
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top