INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

insert data into an audit table

insert data into an audit table

(OP)
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...

RE: insert data into an audit table

Quote (Wizard)

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?

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

RE: insert data into an audit table

(OP)
@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

RE: insert data into an audit table

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.

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

RE: insert data into an audit table

(OP)
@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

RE: insert data into an audit table

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.

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

RE: insert data into an audit table

(OP)
@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]

RE: insert data into an audit table

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;
/
[/code]

Bill
Lead Application Developer
New York State, USA

RE: insert data into an audit table

(OP)
@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

RE: insert data into an audit table

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close