Home » Other » Training & Certification » triggers help
triggers help [message #316071] Wed, 23 April 2008 11:54 Go to next message
virdeeb
Messages: 9
Registered: April 2008
Junior Member
Hi i am creating a database for a company and just need some help on 2 triggers to ensure that wrong data is not entered.

here are the tables i have created.

CREATE TABLE STUDENT
(STUDID VARCHAR(2) NOT NULL,
SNAME VARCHAR2(20),
GENDER VARCHAR2 (1),
COMPANY VARCHAR2 (20),
TELNO VARCHAR2 (12));

CREATE TABLE COURSE
(COURSEID VARCHAR2 (2) NOT NULL,
TITLE VARCHAR2 (20),
DURATION NUMBER(1),
COST NUMBER(4));

CREATE TABLE STUDENTOFFERING
(OFFERINGID VARCHAR2(2) NOT NULL,
STUDID VARCHAR2(2) NOT NULL,
EVALUATION NUMBER(1),
EXAMRESULT VARCHAR2 (1));

CREATE TABLE INSTRUCTOR
(INSTRUCTORID VARCHAR2(2) NOT NULL,
NAME VARCHAR2 (20));

CREATE TABLE OFFERING
(OFFERINGID VARCHAR2(2) NOT NULL,
COURSEID VARCHAR2(2) NOT NULL,
STARTDATE DATE,
LOCATION VARCHAR2(20) NOT NULL,
INSTRUCTORID VARCHAR2(2) NOT NULL);

CREATE TABLE AUDIT
(SNAME VARCHAR2(20) NOT NULL,
COURSEID VARCHAR2(2) NOT NULL,
ATTENDDATE DATE,
INSTRUCTORID VARCHAR2(2)
EVALUATION NUMBER(1));

the first trigger is needed to ensure that a course that lasts 5 days can not be run in december.

the second trigger is needed so that if a student gives a 0 for the evaluation, their name, courseid, current date, instructorid and evaluation are moved to the audit table

thank you
Re: triggers help [message #316079 is a reply to message #316071] Wed, 23 April 2008 12:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ please read OraFAQ Forum Guide, especially "How to format your post?" section
2/ homework must be posted in Homework forum
3/ you have to first post what you tried and explain us why it does not work.

Regards
Michel
Re: triggers help [message #316097 is a reply to message #316071] Wed, 23 April 2008 14:30 Go to previous messageGo to next message
virdeeb
Messages: 9
Registered: April 2008
Junior Member
i am very sorry about that.

right here is my attempt at the first trigger

CREATE OR REPLACE TRIGGER TRIGGER 1
AFTER INSERT OR UPDATE OF OFFERING
FOR EACH ROW
BEGIN
IF (select OFFERINGID from OFFERING where to_char(STARTDATE, 'Mon') = 'DEC')
AND 
IF (SELECT COURSEID FROM COURSE where DURATION = 5)
THEN
RAISE_APPLICATION_ERROR(
-20001,'Cannot run 5 day course in December');
END IF
END;


i am not too sure why this isnt working, but have a idea that it could be with the date trying to convert it.

here is my attempt at the second trigger

 create trigger STUDENT_EVAL
  before insert on STUDENTOFFERING
  for each row when (new.evaluation = 0)
begin
  insert into STUDENT_AUDIT (SNAME, COURSEID, ATTENDATE, INSTRUCTORID, EVALUATION)
  select :new.NAME, :new.COURSEID, O.SDATE, O.INSTRUCTOR, :new.EVAL
  from   OFFERING O
  where  :new.OFFID = O.OFFID;
end;

[Updated on: Wed, 23 April 2008 14:32]

Report message to a moderator

Re: triggers help [message #316101 is a reply to message #316097] Wed, 23 April 2008 14:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You know how to use :new record in second trigger but not in first one; weird!

Regards
Michel
Re: triggers help [message #316106 is a reply to message #316071] Wed, 23 April 2008 15:13 Go to previous messageGo to next message
virdeeb
Messages: 9
Registered: April 2008
Junior Member
ok i have this now

CREATE OR REPLACE TRIGGER TRIGGER1
AFTER INSERT OR UPDATE ON OFFERING
FOR EACH ROW
BEGIN
IF (select OFFERINGID from OFFERING where to_char(STARTDATE, 'Mon') = 'DEC')
AND 
IF (SELECT COURSEID FROM COURSE where DURATION = 5)
SELECT C.COURSEID, TITLE FROM COURSE C
WHERE :NEW.COURSEID = C.COURSEID
THEN
RAISE_APPLICATION_ERROR(
-20001,'Cannot run 5 day course in December');
END IF
END;


but it still builds with compilation errors
Re: triggers help [message #316109 is a reply to message #316106] Wed, 23 April 2008 15:52 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
SELECT can not be part of IF-THEN-ELSE; you'll have to use SELECT INTO a variable, and then use this variable in IF. Something like this:
  l_offeringid offering.offeringid%type;
BEGIN
  SELECT offeringid INTO l_offeringid
    FROM offering
    WHERE ...

  IF l_offeringid = 1234 THEN
     do_something;
  END IF;
 
  ...
Re: triggers help [message #316122 is a reply to message #316071] Wed, 23 April 2008 17:59 Go to previous messageGo to next message
virdeeb
Messages: 9
Registered: April 2008
Junior Member
ok now i have this but it still comes with build errors

CREATE OR REPLACE TRIGGER TRIGGER1
AFTER INSERT OR UPDATE OF STARTDATE ON OFFERING
FOR EACH ROW
BEGIN
SELECT C.COURSEID, C.TITLE, O.OFFERINGID, O.STARTDATE, C.DURATION FROM COURSE C, OFFERING O
WHERE :NEW.COURSEID = C.COURSEID

IF (select OFFERINGID from OFFERING where to_char(STARTDATE, 'Mon') = 'Dec')
AND 
IF (SELECT COURSEID FROM COURSE where DURATION = 5)
THEN
RAISE_APPLICATION_ERROR(
-20001,'Cannot run 5 day course in December');
END IF
END;
Re: triggers help [message #316124 is a reply to message #316071] Wed, 23 April 2008 18:04 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>ok now i have this but it still comes with build errors
Errors? What errors?
I don't see any errors so I have nothing to fix.
Re: triggers help [message #316126 is a reply to message #316071] Wed, 23 April 2008 18:10 Go to previous messageGo to next message
virdeeb
Messages: 9
Registered: April 2008
Junior Member
Warning: Trigger created with compilation errors.

This is my insert statement


INSERT INTO OFFERING VALUES
('11', '3', '01-DEC-2008', 'PRAGUE', '2')


and thats the error i get when i try to run the insert statement

TRIGGER1' is invalid and failed re-validation

Re: triggers help [message #316127 is a reply to message #316071] Wed, 23 April 2008 18:24 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
> Trigger created with compilation errors.
Fix the (*&&*^$%^$#^%() compilation errors (what ever they might be; since you decided to not share those details).
Re: triggers help [message #316128 is a reply to message #316071] Wed, 23 April 2008 18:27 Go to previous messageGo to next message
virdeeb
Messages: 9
Registered: April 2008
Junior Member
it didnt state the particular error that i got, it just stated that there was compilation errors so i couldnt show what these were. but i thought you guys might know what it was by looking at the code
Re: triggers help [message #316139 is a reply to message #316071] Wed, 23 April 2008 22:36 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> it didnt state the particular error

But it is easily achievable by issuing SQL*Plus SHOW ERRORS command right after trigger creation. Read the link to know about all its capabilities.
Alternatively you may query USER_ERRORS system view.

> but i thought you guys might know what it was by looking at the code

Read the previous posts. You already got hints
- to use SELECT INTO statement separately, not inside the IF condition (also notice the INTO clause)
- not to SELECT from OFFERING table, as all desired values from inserted/updated row are already accessible in :NEW variables.
But both approaches are still present in your code.

For correct syntax, consult the documentation, found eg. online on http://tahiti.oracle.com/.
Re: triggers help [message #316146 is a reply to message #316106] Wed, 23 April 2008 23:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Explain what is the purpose of these 2?
select OFFERINGID from OFFERING where to_char(STARTDATE, 'Mon') = 'Dec'
SELECT COURSEID FROM COURSE where DURATION = 5

Regards
Michel
Re: triggers help [message #316155 is a reply to message #316146] Thu, 24 April 2008 00:27 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
@virdeeb,

as far as I can tell, your problem is not the fact that trigger wouldn't compile. Your problem is much wider: you have no idea how to use PL/SQL.

Do yourself (in the first place) and us a favour, take some PL/SQL book (Online PL/SQL Oracle documentation will be just fine for the beginning) and READ IT. It is useless to fix your code statement by statement as you aren't familiar with basic PL/SQL principles.

It is obvious that you are like a 10-months old child: you want it, but can't reach it.
Re: triggers help [message #316222 is a reply to message #316071] Thu, 24 April 2008 05:39 Go to previous messageGo to next message
virdeeb
Messages: 9
Registered: April 2008
Junior Member
right i have taken all of your advice into account and i have come up with this trigger
create or replace trigger COURSE_DURATION
BEFORE insert on offering
for each row
begin
  select *
  from   course c
  where  c.courseid = :new.courseid
  and    to_char(startdate, 'Mon') = 'Dec'
  and    c.duration = 5;
RAISE_APPLICATION_ERROR
(-20001,'Cannot run 5 day course in December')
end;


this is the error i now receive
Quote:

9/1 PLS-00103: Encountered the symbol "END" when expecting one of the following: := . ( % ; The symbol ";" was substituted for "EN D" to continue.

[Updated on: Thu, 24 April 2008 05:53] by Moderator

Report message to a moderator

Re: triggers help [message #316241 is a reply to message #316071] Thu, 24 April 2008 06:30 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
For correct syntax, consult the documentation, found eg. online on http://tahiti.oracle.com/

For the third time in this thread: SELECT statement MUST contain INTO clause in PL/SQL, as stated also in the link Littlefood provided you.
Additionally, each statement must end with semicolon (;). Your last one does not.
But here I must agree with Littlefood. Although you may be syntactically correct, you shall understand, what each statement does. I am afraid, you are still very far.
Re: triggers help [message #316346 is a reply to message #316071] Thu, 24 April 2008 12:57 Go to previous messageGo to next message
virdeeb
Messages: 9
Registered: April 2008
Junior Member
ok i have spent most of the day reading up on pl/sql and here is what i have created for the first trigger
create or replace trigger COURSE_DURATION
before insert on offering
for each row When (to_char(new.startdate, 'Mon') = 'Dec')
declare
  december exception; 
pragma exception_init(december, -20001);
dummy number;
begin 
select 1 into dummy from course c where c.courseid = :new.courseid 
and duration = 5;
  raise december;
exception
  when no_data_found then
    null;
end;


now this trigger creates succesfully but when i try to insert into the offering table i get this error

('20', '2', '02-DEC-2008', 'OSLO', '2')
    *

ERROR at line 2: 
ORA-20001: 
ORA-06512: at "OPS$0466065.COURSE_DURATION", line 5 
ORA-04088: error during execution of trigger 'OPS$0466065.COURSE_DURATION' 


This is my insert statement
INSERT INTO OFFERING VALUES
('20', '2', '02-DEC-2008', 'OSLO', '2')


now for the second trigger this is what i have
create trigger STUDENT_EVAL
  before insert on STUDENToffering
  for each row when (new.evaluation = 0)
begin
  insert into STUDENTAUDIT(sname, courseid, dateofattendance, instructorid, evaluation)
  select s.sNAME, c.COURSEID, O.StartDATE, O.INSTRUCTORid, :new.EVALuation
  from   OFFERING O, student s, course c 
  where  :new.OFFeringID = O.OFFeringID and :new.studid = s.studid and o.courseid = c.courseid;
end;


now this builds fine aswell but when i try an insert it doesnt send anything to the studentaudit table

this is my insert for this trigger
insert into studentoffering values
('05', '10', 0, 'A')
Re: triggers help [message #316361 is a reply to message #316346] Thu, 24 April 2008 15:42 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Could you explain what is the purpose of this statement (in the first trigger)?
raise december;


As of the second trigger: what is the result of the SELECT statement when you execute it in SQL*Plus, using parameters you used in the INSERT INTO statement?
Re: triggers help [message #316369 is a reply to message #316361] Thu, 24 April 2008 16:38 Go to previous messageGo to next message
virdeeb
Messages: 9
Registered: April 2008
Junior Member
Littlefoot wrote on Thu, 24 April 2008 15:42
Could you explain what is the purpose of this statement (in the first trigger)?
raise december;



As of the second trigger: what is the result of the SELECT statement when you execute it in SQL*Plus, using parameters you used in the INSERT INTO statement?


the raise december is to raise an exception if the above statements are true

the result of it is that is inserts fine with the parameters stated, so im not quite sure what the problem with this trigger is
Re: triggers help [message #316381 is a reply to message #316071] Thu, 24 April 2008 20:36 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
First trigger:
> this is the error i now receive
> the raise december is to raise an exception if the above statements are true

So the conditions were fulfiled and the DECEMBER error was raised. This is the standard way how SQL*Plus handles it. You may add some meaningful message - see RAISE_APPLICATION_ERROR.
What behaviour do you expect? As you use trigger for avoiding INSERT, you have to handle with the error after you call the INSERT, not inside DB.

Second trigger:
> but when i try an insert it doesnt send anything to the studentaudit table
> the result of it is that is inserts fine with the parameters stated

Even if the table content stayed unchanged, I would believe Oracle and guess, you do not pass the same parameters. As INSERT does not contain column list (add it), it is very easy to swap column values. You may (for testing purpose) write the values passed to the trigger to check it. This is all I can say without a testcase (copy/pasted SQL*Plus session completely demonstrating this behaviour).
Previous Topic: UDF question
Next Topic: I need help in my project.. please
Goto Forum:
  


Current Time: Sun Dec 04 18:54:45 CST 2016

Total time taken to generate the page: 0.15912 seconds