• 0

[SQL] Triggers


Question

I have to make a trigger to insert some data into a table depending on a condition in this case it is if a value is 0.

I have the following code but it just will not work

CREATE OR REPLACE TRIGGER libloan_zero
BEFORE INSERT ON evaluation
FOR EACH ROW

DECLARE

thisopinion NUMBER(1);
thisfirstname VARCHAR2 (25);
thissurname VARCHAR2 (25);
thishire_date DATE;
thislocation VARCHAR2 (25);
thistitle VARCHAR2 (25);
thiscopy_id NUMBER (10);

begin

SELECT opinion,hire_date,copy_id 
INTO thisopinion,thishire_date,thiscopy_id
FROM evaluation WHERE evaluation_id = :new.evaluation_id;
dbms_output.put_line('arse');

IF thisopinion = 0 THEN
dbms_output.put_line(thisopinion);
INSERT INTO test VALUES (thiscopy_id,thisopinion,thishire_date);
dbms_output.put_line('Record Moved');
end if;

end;

Where am I going wrong? The dbms lines are me trying to debug the problem.

Link to comment
Share on other sites

1 answer to this question

Recommended Posts

  • 0

Updated code

CREATE OR REPLACE TRIGGER libloan_zero
BEFORE INSERT ON evaluation
FOR EACH ROW

DECLARE


thisfirstname VARCHAR2 (25);
thissurname VARCHAR2 (25);
thishire_date DATE;
thislocation VARCHAR2 (25);
thistitle VARCHAR2 (25);

begin

IF :new.opinion = 0 THEN
dbms_output.put_line(:new.opinion);
SELECT firstname,surname,location,title
INTO thisfirstname,thissurname,thislocation,thistitle
FROM evaluation JOIN customer
ON evaluation.customer_id = customer.customer_id
JOIN bookcopy
ON evaluation.copy_id = bookcopy.copy_id
JOIN book
ON bookcopy.book_id = book.book_id
WHERE evaluation_id = :new.evaluation_id;
dbms_output.put_line(:new.evaluation_id);
INSERT INTO book_audit VALUES (thisfirstname,thissurname,thistitle,:new.copy_id,thislocation,:new.opinion,:new.hire_date);
dbms_output.put_line(:new.copy_id);
dbms_output.put_line(:new.opinion);
dbms_output.put_line(:new.hire_date);
dbms_output.put_line('Record Moved');
end if;

end;

ERROR at line 1:

ORA-01403: no data found

ORA-06512: at "OPS$0510975.LIBLOAN_ZERO", line 13

ORA-04088: error during execution of trigger 'OPS$0510975.LIBLOAN_ZERO'

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.